Working with the JSON functions
FileMaker Pro provides several text functions that enable your solution to parse and modify
JSON data from other sources, such as web services that transfer data in JSON format via a REST API.
For more information about the JSON data format, see
json.org.
Retrieving JSON data from a web service
Use the
Insert From URL script step to access a web service, specify parameters for the information to retrieve, send and receive HTTP headers, and store the results in a variable or a field.
For example, a bakery makes its product list available to clients in JSON format via a REST API. The following returns the list of today’s specials as JSON data in the $$JSON variable:
Set Variable [ $url ; "https://<bakery_domain_name>/rest/api/products" ]
Insert from URL [ Verify SSL Certificates ; With dialog: Off; $$JSON ;
$url ; "--data list=specials" ]
For the data returned in $$JSON, see
Example JSON data.
FileMaker Pro also provides several utility functions that handle the character encoding and cryptographic signing required by some REST APIs:
Formatting JSON data
JSON data doesn’t require spaces or line endings between elements. However, to make the data easier to read while you debug problems, use the
JSONFormatElements function, which adds tabs and line ending characters, as shown in
Example JSON data.
Parsing JSON data
Use the following JSON functions to parse JSON data—that is, to get keys, values, or entire JSON objects or arrays that you can process further:
•JSONGetElement – Queries JSON data for an element (an object, array, or value)
•JSONListKeys – Lists the object names (keys) or array indexes in JSON data
The first parameter of these functions, json, specifies the text field, variable, or text expression that contains valid JSON data to operate on.
The second parameter, keyOrIndexOrPath, specifies the portion of the JSON data to operate on:
•key – the name of a key in a JSON object
•index – the index of an element in a JSON array (the first element has an index of 0)
•path – a hierarchical string of key names, array indexes, or both
The following syntax for the keyOrIndexOrPath parameter is supported.
keyOrIndexOrPath parameter | Means |
"." | (optional) The root level, if it’s the first character |
".[n]" | Elements at index n of an array at the root level |
".name" | The key of an object named name at the root level |
".nameA.nameB.nameC" | An object named nameC, which is a descendent of nameB and nameA |
".[3][2][1]nameA[0]" | The first element of the array in the nameA object, which is at the third level in a set of nested arrays |
The following example script determines the number of products in the
Example JSON data (stored in the $$JSON variable) using JSONListKeys, creates a record for each product, then sets fields in each record to the values obtained using JSONGetElement for each product.
Set Variable [ $ProductCount ; Value:
ValueCount (
JSONListKeys ( $$JSON ; "bakery.product" )
) ]
Set Variable [ $i; Value: 0 ]
If [ $ProductCount > 0 ]
Loop
New Record/Request
Set Field [ Products::ID ;
JSONGetElement ( $$JSON ; "bakery.product[" & $i & "]id" ) ]
Set Field [ Products::Price ;
JSONGetElement ( $$JSON ; "bakery.product[" & $i & "]price" ) ]
Set Field [ Products::Stock ;
JSONGetElement ( $$JSON ; "bakery.product[" & $i & "]stock" ) ]
Commit Records/Requests [ With dialog: Off ]
Set Variable [ $i ; Value: $i + 1 ]
Exit Loop If [ $i ≥ $ProductCount ]
End Loop
End If
Changing and adding JSON data elements
To change values and add elements in JSON data, use the
JSONSetElement function. The
json and
keyOrIndexOrPath parameters work in this function as described in
Parsing JSON data. If
keyOrIndexOrPath specifies an existing element, the value of that element is changed; if the element doesn’t exist, a new element is added.
JSONSetElement sets the specified element to the value parameter. You can specify any valid JSON value, from a simple string or number to a complex object or array.
The
type parameter specifies the type of data in
value so that the JSON parser will follow strict rules when handling each data type. For the supported data types, see
JSONSetElement function. To insert JSON data into
json, you can set
type to
JSONRaw to have the JSON parser determine the data type of
value.
The following example adds the key-value pairs for a new product to an empty JSON object. Then the new object is added at the end of the product array in the $$JSON variable (see
Example JSON data).
Set Variable [ $NewProduct ; Value:
JSONSetElement ( "{}" ;
[ "id" ; "FB4" ; JSONString ] ;
[ "name" ; "Vanilla Cake" ; JSONString ] ;
[ "price" ; 17.5 ; JSONNumber ] ;
[ "stock" ; 12 ; JSONNumber ] ;
[ "category" ; "Cakes" ; JSONString ] ;
[ "special" ; true ; JSONBoolean ]
) ]
Set Variable [ $NextIndex ; Value:
ValueCount (
JSONListKeys ( $$JSON ; "bakery.product" )
) ]
Set Variable [ $$JSON ; Value:
JSONSetElement (
$$JSON ; "bakery.product[" & $NextIndex & "]" ; $NewProduct ;
JSONObject
) ]
Deleting JSON data elements
To delete an element, use the
JSONDeleteElement function. The
json and
keyOrIndexOrPath parameters work in this function as described in
Parsing JSON data. The
keyOrIndexOrPath parameter must specify an existing element in
json.
The following example deletes the element in the product array whose "id" key has the value "FB3" in the $$JSON variable (see
Example JSON data).
Set Variable [ $ProductCount ; Value:
ValueCount (
JSONListKeys ( $$JSON ; "bakery.product" )
) ]
Set Variable [ $i ; Value: 0 ]
If [ $ProductCount > 0 ]
Loop
Set Variable [ $ID ; Value:
JSONGetElement ( $$JSON ; "bakery.product[" & $i & "]id" ) ]
If [ $ID = "FB3" ]
Set Variable [ $$JSON ; Value:
JSONDeleteElement ( $$JSON ; "bakery.product[" & $i & "]" ) ]
Exit Script [ Text Result: 0 ]
End If
Set Variable [ $i ; Value: $i + 1 ]
Exit Loop If [ $i ≥ $ProductCount ]
End Loop
End If
Handling errors in JSON data
If an error occurs while parsing the
json parameter, the JSON functions return "?" followed by an error message from the JSON parser. For example, when the ":" after the "bakery" key is missing in
Example JSON data, this calculation
JSONGetElement ( $$JSON ; "bakery.product[0]id" )
returns this error message:
? * Line 3, Column 2
Missing ':' after object member name
* Line 13, Column 5
Extra non-whitespace after JSON value.
To determine whether JSON data is valid before using it, use the
JSONFormatElements function and test whether the first character is "?". For example:
Set Variable [ $result ; Value: JSONFormatElement ( $$JSON ) ]
If [ Left ( $result ; 1 ) = "?" ]
# $$JSON contains invalid JSON data.
End If
Example JSON data
The following example JSON data contains a "bakery" object that has an array of three "product" objects, each with several key-value pairs.
{
"bakery" :
{
"product" :
[
{
"id" : "FB1",
"name" : "Donuts",
"price": 1.99,
"stock" : 43,
"category" : "Breads",
"special" : true
},
{
"id" : "FB2",
"price": 22.5,
"name" : "Chocolate Cake",
"stock" : 23,
"category" : "Cakes",
"special" : true
},
{
"id" : "FB3",
"price": 3.95,
"name" : "Baguette",
"stock" : 34,
"category" : "Breads",
"special" : true
}
]
}
}
Notes
•The JSON parser preserves the order of elements in an array, but not the order of elements in an object. Therefore, the JSON functions may return elements in an object in a different order from the order specified.
Related topics