alecgregory

Building an array with the new JSON functions

Discussion created by alecgregory on May 22, 2017
Latest reply on May 23, 2017 by PeterDoern

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

Outcomes