FileMaker 21.0: New JSON Functions and Operators

FileMaker 21.0 introduces some enhancements for how we construct and interact with JSON data. 

FileMaker scripts commonly manipulate a collection of data. Historically such data has been typically constructed as a list of values separated by carriage returns. Since the introduction of the JSON parsing functions, working with JSON data has become the norm. Working with lists is still common, but JSON is needed for more sophisticated data structures. 

New Function: JSONMakeArray 

A lot of the native ways of collecting data still results in lists though. For example, I can use a summary field to collect values from the found set, but the result will be a list of values. Similarly, I can query record data using SQL, but the most common way of collecting that SQL result is as a list of values. 

JSONMakeArray is a new, native function that converts a collection of values to a JSON array. You can specify the delimiter (separator) and the data type used by the values.

Functions signature:

JSONMakeArray ( listOfValues ; separator ; type ) 

Let’s learn more about this function by looking at some examples: 

JSONMakeArray ( "A¶B¶C" ; ¶ ;JSONString ) 
=> ["A","B","C"] 

If you leave the separator blank, it will default to ¶:

JSONMakeArray ( "A¶B¶C" ; "" ;JSONString ) 
=> ["A","B","C"]

if you leave the type blank, FileMaker will infer it from the value: 

JSONMakeArray ( "A¶B¶C" ; "" ; "" ) 
=> ["A","B","C"] 

The separator can consist of multiple characters: 

JSONMakeArray ( "AhelloBhelloC" ; "hello" ; "" ) 
=> ["A","B","C"] 

It can even span multiple lines:

JSONMakeArray ( "Ahello¶worldBhello¶worldC" ; "hello¶world" ; "" )
=> ["A","B","C"] 

The values can be a mix of types, including JSON objects and arrays:

JSONMakeArray ( "A¶123¶{\"B\":\"C\"}¶[4,5,6]" ; "" ; "" ) 
=> ["A",123,{"B":"C"},[4,5,6]] 

But the inferred type might not always be what you expect:

JSONMakeArray ( "A¶¶C" ; "" ; "" ) 
=> ["A","","C"] // why is the second array element a string instead of null? 

If the first character is a digit, the value is inferred to be a number: 

JSONMakeArray ( List (  
	"5/16/2024" ;  
	GetAsDate ( "5/16/2024" ) ;  
	GetAsTime ( "1:57:34 PM" ) ;  
	GetAsTimestamp ( "4/1/2024 1:57:34 PM" ) ;  
	"9days" ;  
) ; "" ; "" ) 
=> [5,5,1,4,9]

Same if the first character is a hyphen: 

JSONMakeArray ( List ( "-" ; "-hyphen" ) ; "" ; "" ) 
=> [0,0]

New Array Operators 

Along with the new JSONMakeArray function, FileMaker 21.0 introduces two new operators to make it easier to push an element onto an array and to work with the last element in an array. 

The [+] operator references a new, to-be-created element at the end of the array, and the [:] operator references the last, already-existing element in the array. 

JSONSetElement( ""  
	; [ "[+].a" ; 5 ; JSONNumber ] // add {"a":5} as a new array element 
	; [ "[:].b" ; 6 ; JSONNumber ] // add new key "b" to the last array element 
) 
=> [{"a":5,"b":6}] 

We can now easily do push and pop array operations: 

JSONSetElement( "[0,1]" ; "[+]"; 2 ; JSONNumber ) 
=> [0,1,2] // push 2 onto the array 

JSONDeleteElement ( "[0,1,2]" ; "[:]" ) => [0,1] 
// pop 2 off of the array 

The [:] operator can be used with read, modify, and delete operations too: 

JSONGetElement ( "[0,1,2]" ; "[:]" ) 
=> 2

JSONSetElement( "[0,1,2]" ; "[:]"; 3 ; JSONNumber ) 
=> [0,1,3] 

JSONDeleteElement ( "[0,1,2]" ; "[:]" ) 
=> [0,1]

Summary 

The new function and operators are a welcome developer experience (DevEx) improvement for working with JSON arrays. 

To be safe, use explicit typing (JSONString, JSONNumber, etc) with the JSONMakeArray function, because relying on inferred types can give results you might not expect.