Optimising Parsing JSON Arrays using the JSONGetElement Function

Using the JSONGetElement function you can query JSON data for a specific element by an object name, an array index, or a path. For example you might be querying an API like Xero or Shopify for a set of recent Invoices or Orders which you would like to download into your FileMaker solution. Most APIs use pagination to limit the number of records that are returned in response to your query. The number of records returned might be set by the API vendor (e.g. you will always get 50 records) or you might be able to specify the number of records to return by using a query parameter.

The response from the API will typically be a JSON Array – Claris use the following example JSON data in their documentation:

{
    "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
            }
        ]
    }
}

This sample JSON contains a bakery object with an array of three product objects. To get the value of a particular object you can use JSONGetElement function and specify the array index and object name. For example if you wanted to get the value of the name object of the second product object in the array you would use:

JSONGetElement ( $$JSON ; "bakery.product[1]name" )

which would return ‘Chocolate Cake’. You would typically have a counter that you would set in your FileMaker script to increment the array index – remember to start your counter at 0 as JSON Array indexes are zero based. Whilst this approach works reasonably well for small JSON Arrays once your JSON Array contains larger results (50, 100, 150 records etc) then you will get better performance by breaking out each individual record into it’s own FileMaker variable and then referencing that with your JSONGetElement functions. The benefits are even more noticeable when your JSON data contains multiple nested arrays.

FileMaker’s JSON parser is much slower when it has to continually parse the JSON array to target the 1st record, then the 2nd, 3rd and so on. If you have over a hundred records in your JSON array (as well as each record having their own arrays, such as an Order with line items, tax items, refund items etc) you will get much better performance if you break out each record into it’s own FileMaker variable and target that. You want to avoid parsing any large JSON arrays as much as possible.

In our FileMaker integration solutions we have changed from using this type of structure in a loop:

JSONGetElement ( $response ; "orders[" & $counter & "].id" )

to extracting each record from the JSON array in a loop into a $record variable:

JSONFormatElements ( JSONGetElement ( $response ; "orders[" & $counter & "]" ) )

then we can simply target each object like this:

JSONGetElement ( $record ; "id" )

We’ve seen performance improvements of over 80% when converting scripts to this new format when testing our fmEcommerce Link (Shopify Edition) solution by downloading all Orders and all Products from Shopify. If you’re parsing large JSON arrays we highly recommend moving away from continually having to parse the JSON array to extracting each record from the array in your loop and perform your JSONGetElement queries on that instead.

Here’s a short video that demonstrates the technique (you can also watch this on YouTube here):

You can also download the file we used in this demonstration here.