Working with the JSON functions

FileMaker Pro provides several text functions that enable your custom app to parse and modify JSON data from other FileMaker functions or 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.

The examples shown in this topic use JSON data from a bakery that 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:

Copy 
Set Variable [ $url ; "https://bakery.example.com/rest/api/products" ]
Copy 
Insert from URL [ With dialog: Off; Target: $$JSON ; $url ; Verify SSL Certificates ; cURL options: "--data list=specials" ]

For the data returned in $$JSON and used in these examples, see Example JSON data.

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

  • JSONListValues – Lists the values 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 two types of syntax for the keyOrIndexOrPath parameter are supported: dot notation and bracket notation.

Syntax for keyOrIndexOrPath parameter

Means
Dot notation Bracket notation

"."

""

The root level, if it's the first character (optional in dot notation)

".[n]"

"[n]"

Elements at index n of an array at the root level

".name"

"['name']"

The key of an object named name at the root level

".nameA.nameB.nameC"

"['nameA']['nameB']['nameC']"

An object named nameC, which is a descendent of nameB and nameA

".[3][2][1]nameA[0]"

"[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 last element of an array

"[+]"

"[+]"

The position after the last element of an array. Use in the JSONSetElement function to add an element to the end of an array.

The difference between dot and bracket notation is that, instead of using periods (.) to separate key names, bracket notation surrounds key names with single quotes (') and brackets ([]). You can use either notation in keyOrIndexOrPath. However, you must use bracket notation if key names include periods, so that the JSON parser can correctly identify the entire key name. For example, if a key at the root of a JSON object is "layout.response", then keyOrIndexOrPath would be "['layout.response']".

The following example script creates a record for each product in a JSON array. Assuming the Example JSON data is stored in the $$JSON variable, the script uses JSONListValues to get the contents of the product array as a list of values and uses ValueCount to determine the number of products in the list. Creating a record for a product each time through the loop, the script uses GetValue to get the JSON object for a product from the list and sets the fields to the values obtained using JSONGetElement. Because the JSON functions parse the entire JSON object passed into them, it can be more efficient to use the JSON functions on smaller JSON objects inside a loop that is repeated many times.

Copy 
Set Variable [ $ProductList ; Value: JSONListValues ( $$JSON ; "bakery.product" ) ]
Set Variable [ $ProductCount ; Value: ValueCount ( $ProductList ) ]
Set Variable [ $i; Value: 1 ]
If [ $ProductCount > 0 ]
   Loop [ Flush: Always ]
      New Record/Request
      Set Variable [ $Product ; Value: GetValue ( $ProductList ; $i ) ]
      Set Field [ Products::ID ; JSONGetElement ( $Product ; "id" ) ]
      Set Field [ Products::Price ; JSONGetElement ( $Product ; "price" ) ]
      Set Field [ Products::Stock ; JSONGetElement ( $Product ; "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 data into json that is already formatted as a valid JSON element, set type to JSONRaw.

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).

Copy 
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 
   ) ]

Another JSON function that creates a JSON element is the JSONMakeArray function. It converts a list of values into a JSON array. To accept data formatted in different ways, this function lets you specify the character that separates each value and the JSON data type to use.

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).

Copy 
Set Variable [ $ProductCount ; Value: 
   ValueCount ( 
      JSONListKeys ( $$JSON ; "bakery.product" ) 
   ) ] 
Set Variable [ $i ; Value: 0 ]
If [ $ProductCount > 0 ]
   Loop [ Flush: Always ]
      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

Optimizing JSON performance

Whenever a JSON function parses text input, the JSON parser creates a binary (not text) representation of it to make later processing of the JSON faster. There's an automatic mechanism to parse and cache JSON and an explicit one: the JSONParse function.

Taking advantage of automatic JSON caching

Parsing takes time, so the JSON functions cache the binary representation of the last parsed JSON in memory. This reduces the need to parse the same JSON again later. Only one automatic cache is maintained. When a different JSON value is parsed, the previously cached JSON value is discarded. The parsed JSON exists only in memory—in variables, script parameters, and calculations. When used to set a field's value, it is stored in the field as text.

By line number, this example illustrates when the JSON text initially stored in $JSON1 and $JSON2 is parsed and when it's not.

Copy 
Set Variable [ $id ; JSONGetElement ( $JSON1 ; "id" ) ]
Set Variable [ $name ; JSONGetElement ( $JSON1 ; "name" ) ]
Set Variable [ $price ; JSONGetElement ( $JSON2 ; "price" ) ]
Set Variable [ $category ; JSONGetElement ( $JSON1 ; "category" ) ]

  1. $JSON1 is parsed and the binary representation is saved in the cache.

  2. $JSON1 doesn't need to be parsed again.

  3. $JSON2 is parsed and cached, replacing the previously cached value.

  4. $JSON1 is parsed again because it was no longer cached.

So to take advantage of automatic caching, it's best to work with one JSON value at a time (in the example above, swap lines 3 and 4 to avoid parsing $JSON1 again).

Using JSONParse

To parse and explicitly store the binary representation of a JSON value in a variable as well as in the automatic cache, use the JSONParse function. The function's json parameter is any text expression that evaluates to JSON data, whether it's text formatted as JSON in a field or a string, or already parsed JSON in a variable.

The following example parses JSON text:

Copy 
Set Variable [ $JSON1 ; "{ \"product\": {\"id\": \"FB1\"} }" ]
Set Variable [ $JSON1 ; JSONParse ( $JSON1 ) ]

  1. $JSON1 is only the text representation, which looks like this:

    { "product": {"id": "FB1"} }

  2. After using JSONParse:

    • The automatic cache contains the binary representation.

    • The $JSON1 variable contains both the text and binary representations.

If JSONParse isn't used, then in this example:

Copy 
Set Variable [ $JSON1 ; "{ \"product\": {\"id\": \"FB1\"} }" ]
Set Variable [ $JSON2 ; JSONGetElement ( $JSON1 ; "product") ]

  1. $JSON1 is only the text representation, as before.

  2. After using JSONGetElement:

    • The automatic cache contains the binary representation.

    • $JSON2 contains the binary representation of {"id": "FB1"}.

    • $JSON1 is still only the text representation. The text has been parsed, but the binary representation is only in the automatic cache.

Contrast that with what happens when you add JSONParse:

Copy 
Set Variable [ $JSON1 ; "{ \"product\": {\"id\": \"FB1\"} }" ]
Set Variable [ $JSON1 ; JSONParse ( $JSON1 ) ]
Set Variable [ $JSON2 ; JSONGetElement ( $JSON1 ; "product") ]

  1. $JSON1 is only the text representation, as before.

  2. After using JSONParse:

    • The automatic cache contains the binary representation.

    • $JSON1 contains both the text and binary representations.

  3. After using JSONGetElement:

    • $JSON2 contains the binary representation of {"id": "FB1"}.

      The text representation isn't stored in $JSON2 at this point but is generated later only when needed.

Notice that JSONGetElement didn't need to parse $JSON1 in line 3, because it could use the binary representation cached with $JSON1 in line 2.

Just as parsing text to get a binary representation takes time, so does converting binary JSON to text. That's why the result of JSON functions like JSONGetElement and JSONSetElement is only the binary representation. The text representation is created only when needed, such as when storing a variable (like $JSON2) in a field, or viewing a variable in the Data Viewer.

You can use the companion function JSONParsedState to discover whether a given JSON value has parsed JSON stored with it, whether the JSON is valid, and what the JSON type is.

Best practices

When processing large JSON structures repeatedly (especially in loops) or working with multiple elements in JSON data, the order of operations can make a significant difference in performance.

To take advantage of automatic JSON caching, it's best not to alternate between loading JSON data from one field to work on, switch to a different field to work on, then go back to the first field. Each time a JSON function processes different JSON text, the previously cached JSON is discarded, so the text must be parsed again. For example, this yields the slowest performance:

Copy 
# Example - Not efficient
Set Variable [ $namel ; Value: JSONGetElement ( Table::JSON1 ; "name" ) ]
Set Variable [ $name2 ; Value: JSONGetElement ( Table::JSON2 ; "name" ) ]
Set Variable [ $id1 ; Value: JSONGetElement ( Table::JSON1 ; "id" ) ]
Set Variable [ $id2 ; Value: JSONGetElement ( Table::JSON2 ; "id" ) ]

By only reordering the steps so that you do all the work on the same JSON data before working on different JSON data, the automatic cache works in your favor, eliminating the need to parse the data from the same field twice.

Copy 
# Example - Better
Set Variable [ $namel ; Value: JSONGetElement ( Table::JSON1 ; "name" ) ]
Set Variable [ $id1 ; Value: JSONGetElement ( Table::JSON1 ; "id" ) ]
Set Variable [ $name2 ; Value: JSONGetElement ( Table::JSON2 ; "name" ) ]
Set Variable [ $id2 ; Value: JSONGetElement ( Table::JSON2 ; "id" ) ]

The best, most flexible method is to use the JSONParse function to get the JSON text from each source, parse it, and store it in a variable. This way it's only ever parsed once. Then it doesn't matter in what order you do further operations (like getting or setting elements) since those JSON functions can use the variables, which contain the already parsed binary representations.

Copy 
# Example - Best
Set Variable [ $JSON1 ; Value: JSONParse ( Table::JSON1 ) ]
Set Variable [ $JSON2 ; Value: JSONParse ( Table::JSON2 ) ]

Set Variable [ $namel ; Value: JSONGetElement ( $JSON1 ; "name" ) ]
Set Variable [ $name2 ; Value: JSONGetElement ( $JSON2 ; "name" ) ] 
Set Variable [ $id1 ; Value: JSONGetElement ( $JSON1 ; "id" ) ]
Set Variable [ $id2 ; Value: JSONGetElement ( $JSON2 ; "id" ) ]

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

Copy 
JSONGetElement ( $$JSON ; "bakery.product[0]id" )

returns this error message:

Copy 
? * 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:

Copy 
Set Variable [ $result ; Value: JSONFormatElements ( $$JSON ) ]
If [ Left ( $result ; 1 ) = "?" ]
   # $$JSON contains invalid JSON data.
End If

Alternately, to determine whether JSON data is valid and a specific JSON type before using it, use the JSONGetElementType function. For example, to test whether $$JSON is a valid JSON object:

Copy 
Set Variable [ $result ; Value: JSONGetElementType ( $$JSON, "" ) ]
If [ $result ≠ JSONObject ]
    # $$JSON contains invalid JSON data.
End If

Another way to detect errors is with the JSONParsedState function. It can tell you whether the JSON data has been parsed and is valid, and if so, what type of JSON data it is.

Copy 
Set Variable [ $result ; Value: JSONParse ( $$JSON ) ]
Set Variable [ $ParsedState ; Value: JSONParsedState ( $result ) ]
If [ $ParsedState < 0 ]
    # $$JSON has been parsed but contains invalid JSON data.
Else If [ $ParsedState = 0 ]
    # $$JSON hasn't been parsed.
Else If [ $ParsedState > 0 ]
    # $$JSON has been parsed and is valid. $ParsedState indicates JSON type.
    Set Variable [ $type ; Value: 
        Case ( 
          $ParsedState = JSONString ; "JSON type is JSONString" ;
          $ParsedState = JSONNumber ; "JSON type is JSONNumber" ;
          $ParsedState = JSONObject ; "JSON type is JSONObject" ;
          $ParsedState = JSONArray ; "JSON type is JSONArray" ;
          $ParsedState = JSONBoolean ; "JSON type is JSONBoolean" ;
          $ParsedState = JSONNull ; "JSON type is JSONNull"
        )
    ]
    Show Custom Dialog [ $type ]
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.

Copy 
{
    "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.

  • In JSON data, fractional numeric values must use a period "." as the decimal separator regardless of the separator specified by your computer's system formats or the formats used when the FileMaker Pro file was created.

Related topics