FileMaker 16: New JSON Functions

One of the highest community voted features making its way into the release of FileMaker 16 is a new set of functions dedicated to the parsing of JSON (JavaScript Object Notation) text strings. These come in conjunction with the introduction of REST API into the FileMaker Server software, and serve as a new valuable tool for developers to communicate with third party data sources. JSON holds a demanding position as an industry standard in web services. Now, where FileMaker developers used to have to turn to custom functions and/or third party plugins to interact with these services, they will have use of standard built-in functions that will ship with FileMaker 16. The NEW Functions

• JSONDeleteElement( json ; keyOrIndexOrPath ) – Deletes a JSON data element specified by an object name, an array index, or a path.
• JSONFormatElements( json ) – Formats elements in JSON data to make them easier to read.
• JSONGetElement( json ; keyOrIndexOrPath ) – Queries JSON data for an element specified by an object name, an array index, or a path.
• JSONListKeys( json ; keyOrIndexOrPath ) – Lists the object names (keys) or array indexes in JSON data for an element specified by an object name, an array index, or a path.
• JSONListValues( json ; keyOrIndexOrPath ) – Lists the values in JSON data for an element specified by an object name, an array index, or a path.
• JSONSetElement( json ; keyOrIndexOrPath ; value {; valuetype } ) – Adds or modifies an element in JSON data specified by an object name, an array index, or a path.
• SortValues( values { ; datatype } ) – Sorts a list of values based on the specified data type and locale.
• UniqueValues( values { ; datatype } ) – Returns unique values that are in a list based on the specified data type and locale.

…other NEW utility functions

• CryptDigest( data, algorithm ) – Returns a binary hash value generated by the specified cryptographic hash algorithm.
• CryptAuthCode( data, algorithm, key ) – Returns a binary HMAC (keyed-hash message authentication code) using the specified cryptographic hash algorithm.
• Base64EncodeRFC( RFC number, data ) – Returns data as text in the specified Base64 format.
• HexEncode( data ) – Returns data as text in hexadecimal format.
• HexDecode( data {; fileNameWithExtension } ) – Returns either container or text content from text encoded in hexadecimal format.
• TextEncode( text ; encoding ; lineEndings ) – Returns a text file as container data from text using the specified character encoding and line endings.
• TextDecode( container ; encoding ) – Returns text decoded from container data using the specified character encoding.

The new utility functions have been included to maximize FileMaker’s ability to talk to REST APIs by providing tools for signing and encoding JSON data based on the requirements of specific APIs. They each have situations where they would be very useful, however specific examples of their uses will not be covered within this article. Parsing data out of JSON These first functions are designed for pulling desired data out of a JSON text string. The first parameter is always the string itself while the second parameter designates what specific data is desired. The result of these functions is text, but is NOT itself a valid JSON string. For the purposes of all the following examples any reference to the $$JSON variable can be assumed to be populated with the sample block of JSON data provided by the FileMaker documentation here. JSONGetElement() Example 1

• JSONGetElement (“{ “a” : 10, “b” : 11, “c” : 12 }”; “b” )
• Returns 11

Example 2

• JSONGetElement (“[“lion”, “tiger”, “bear”]”; 1 )
• Returns tiger

Example 3

• JSONGetElement ($$JSON; “bakery.product[1]id” )
• Returns FB1

(Notice how this example demonstrates how to format a request for a value within a nested object) JSONListKeys() Example 1

• JSONListKeys (“{ “a” : 10, “b” : 11, “c” : 12 }” )
• Returns a¶b¶c

Example 2

• JSONListKeys ( $$JSON ; “bakery.product” )
• Returns 0¶1¶2

JSONListValues() Example 1

• JSONListValues (“{ “a” : [“First”, “Middle”, “Last”] }”; “a” )
• Returns First¶Middle¶Last

Modifying/Producing JSON These next functions are available for creating and/or modifying JSON. The first parameter is an existing JSON string (or simply “{}” in the case of JSONSetElement(), allowing for the creation of a new string). The result of these functions is a valid JSON text value. JSONDeleteElement() Example 1

• JSONDeleteElement (“{ “a” : 10, “b” : 11, “c” : 12 }”; “b” )
• Returns { “a” : 10, “c” : 12 }

Example 2

• JSONDeleteElement (“[“lion”, “tiger”, “bear”]“; 2 )
• Returns [“lion”, “tiger”]

JSONFormatElements() Example

• JSONFormatElements (“{ “a” : { “id” : 42, “Description” : 34 } }”)
• Returns

{   “a” :{    “id” : 42,    “Description” : 34   } }

JSONSetElement() Example 1

• JSONSetElement (“{ “a” : 10, “c” : 12 }”; “b”; 44; JSONNumber )
• Returns { “a” : 10, “b” : 44, “c” : 12 }

Example 2

• JSONSetElement (“[“lion”, “tiger”, “bear”]”; 1; “sheep”; JSONString)
• Returns [lion, sheep, bear]

(Notice that the count of values within an array starts with “0” designating the first value) JSON has six types of values. These types are designated within a fourth parameter of the JSONSetElement function. 1. JSONString 2. JSONNumber 3. JSONObject 4. JSONArray 5. JSONBoolean 6. JSONNull Example 3

• JSONSetElement (“{}”; “a”; “[1,2]”)
• Returns {“a”:[1,2]}

Example 4

• JSONSetElement (“{}”; “a”; “[1,2]”; JSONString )
• Returns {“a”:”[1,2]”}

Example 5

• JSONSetElement (“{}”; “a”; “[1,2]”; JSONNumber )
• Returns {“a”:12}

Example 6

• JSONSetElement (“{}”; “a”; “[1,2]”; JSONObject)
• Returns {“a”:{}}

Example 7

• JSONSetElement (“{}”; “a”; “[1,2]”; JSONArray)
• Returns {“a”:[1,2]}

Example 8

• JSONSetElement (“{}”; “a”; “[1,2]”; JSONBoolean)
• Returns {“a”:true}

Example 9

• JSONSetElement (“{}”; “a”; “[1,2]”; JSONNull)
• Returns {“a”:null}

Value Lists Lastly, two new functions allow for the manipulation of value lists. These can also reference the data types listed above. SortValues() Example

• SortValues ( 5¶15¶10¶20; -2 )
• Returns 20¶15¶10¶5

UniqueValues() Example

• UniqueValues ( Flowers::Colors )
• Returns red¶blue¶yellow (when the Flowers::Colors field contains red¶blue¶yellow¶blue )

For additional information about JSON in general see http://www.json.org. Some samples listed here are based on those provided by FileMaker within the FileMaker 16 documentation Jeremy Upton is a FileMaker 15 Certified Developer at Skeleton Key in St. Louis, Mo. About Skeleton Key Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business. In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.