4 Replies Latest reply on May 23, 2017 8:22 AM by PeterDoern

    Building an array with the new JSON functions

    alecgregory

      The below is a technique for building a JSON array when using a popular FileMaker Loop pattern. I came up with the technique while converting the output of FileMaker loops to JSON format. I hope it'll be useful to some of you.

       

      Many FileMaker functions, such as List, and popular custom functions, such as #, allow us to build array-like structures with concatenation. For example, a common use of the List function is to build a list of keys:

       

      Set Variable [ $i = 0 ]

      Loop

           Set Variable [ $i = $i + 1 ]

           Set Variable [ $thisKey = GetValue ( $listOfKeys; $i )

           ...

           Set Variable [ $keys = List ( $keys; $thisKey ) ]

           ...

      End Loop

       

      JSON arrays don't support this technique. In some cases, if we know that every iteration of the loop will lead to a value being added to the array, and that the $json variable is empty at the start of the loop, we can use our loop counter ($i) to dictate the array index:

       

      Set Variable [ $i = 0 ]

      Loop

           Set Variable [ $i = $i + 1 ]

           Set Variable [ $thisKey = GetValue ( $listOfKeys; $i )

           ...

           Set Variable [

                $json =

                     JSONSetElement (

                          If ( IsEmpty ( $json ); "[]"; $json );

                          $i - 1; // JSON arrays are zero-based so we often need to subtract 1 as FileMaker is mainly one-based

                          $thisKey;

                          JSONString

                     )

           ]

           ...

      End Loop

       

      But if we are not sure whether the $json variable will be empty at the start of the loop or we may not be adding a value to the array in every iteration of the loop, we can run into problems. Specifically, we may end up accidentally overwriting data in the $json array or we may end up with sparse arrays that look like this:

       

      ["value",null,null,"value",null]

       

      Overwriting data is obviously bad. Sparse arrays can break scripts that don't check for null or treat a null value as the end of the array. Additionally sparse arrays are less efficient as they lead to needless iterations of loops when they are passed to looping scripts.

       

      To avoid overwriting and sparse JSON arrays we can use the JSONListKeys function to determine the correct index to use. For an array the JSONListKeys function returns a return separated list of indexes (numbers). For example 0¶1¶2. Providing we know that our array is not sparse, we can count the number of values in the result of JSONListKeys and use that as the next index:

       

      Set Variable [ $i = 0 ]

      Loop

           Set Variable [ $i = $i + 1 ]

           Set Variable [ $thisKey = GetValue ( $listOfKeys; $i )

           ...

           Set Variable [

                $json =

                     JSONSetElement (

                          If ( IsEmpty ( $json ); "[]"; $json );

                          If ( IsEmpty ( $json ); 0; ValueCount ( JSONListKeys ( $json; "" ) ) );

                          $thisKey;

                          JSONString

                     )

           ]

            ...

      End Loop

        • 1. Re: Building an array with the new JSON functions
          jbante

          You can deal with the sparse array problem by starting the array index based on the last value from JSONListKeys, rather than the number of values. Doing this once at the start of the script may also improve the execution speed, since you only have to call JSONListKeys once, rather than repeating it for each value:

           

          Set Variable [ $keyList ; Value: JSONListKeys ( $json ; "" )) ]

          Set Variable [ $json.i ; Value: If ( IsEmpty ( $keyList ) or Left ( $keyList ; 1 ) = "?" ; -1 ; /* Else */ RightValues ( $keyList ; 1 ) ) ]

          Set Variable [ $list.i ; Value: 1 ]

          Loop

               Set Variable [ $value ; Value: /* whatever */ ]

               Set Variable [ $json.i ; Value: $json.i + 1 ]

               Set Variable [ $json ; Value: JSONSetElement ( $json ; $json.i ; $value ; JSONString ) ]

               Set Variable [ $list.i ; Value: $list.i + 1 ]

               Exit Loop If [ $list.i > $end ]

          End Loop

          2 of 2 people found this helpful
          • 2. Re: Building an array with the new JSON functions
            PeterDoern

            Unless I'm missing something you need to define $end with the count of keys. Also -- and this just might be a style preference -- I would place the Exit Loop If step immediately after the opening Loop step so that it can exit gracefully in case of a zero count.

            • 3. Re: Building an array with the new JSON functions
              jbante

              The calculation of $end is based on the source data, a detail I'm happy to elide over, since the source data are not the focus of interest here. I agree that different placement of the Exit Loop If step is appropriate for different structures and expectations of the data being looped over. Placing Exit Loop If before most of the loop body does make more sense if we anticipate that empty source data might be possible. I wrote this example with the assumption that we know for a fact there will always be at least once value of source data, in which case placing Exit Loop If at the end avoids a superfluous test on the first value. In general, I strongly encourage developers to adapt their techniques to each situation as granularly as they can over rigid habits, though the latter is often more important for FileMaker developers where "fast" code means "fast to write" as often as not.

              • 4. Re: Building an array with the new JSON functions
                PeterDoern

                Understood and agreed.