Parsing JSON in FileMaker 16

JSON (JavaScript Object Notation) is an open standard format to send data that consists of an object with name-value pairs. JSON derives from JavaScript, but as many other programming languages have adopted it as an alternative to XML, as JSON is far less verbose than XML. Many web services return data in JSON or XML, and sometimes both. FileMaker’s Insert from URL script connects to web services and retrieves data such as XML and JSON. Until FileMaker 16, parsing JSON required functions that manipulated text to extract data.

FileMaker 16 introduces native functions to create and extract data from JSON, making interactions with web services easier and more streamlined. This article focuses on how to extract values from JSON.

JSON structure

JSON objects begin and end with braces – { } – containing sets of key-value pairs. Each key is followed by a colon and each key-value pair is separated by a comma.

{ key1 : value1 , key2 : value2 }

Arrays are collections of values. These begin and end with square brackets – [ ]. Values within arrays are separated by commas. The values inside arrays are indexed numerically, starting with zero (0). The array in the example below has an index consisting of 0, 1,  and 2.

[ value1 , value2 , value3 ]

Data types

Values can consist of strings, numbers, Boolean( true/false), or null, as well as objects and arrays. This means that a JSON object can contain data nested several layers deep within arrays and objects, creating a pathway to specific values by following a pathway of keys down to the value associated with the key in that path.

Whitespace

JSON ignores whitespace that appears between key-value pairs, which means tabs, spaces, and returns can be inserted to make the JSON more human readable.

A large block of JSON can be difficult for humans to read, especially when it comes to discerning arrays, objects, and paths. In my examples above I’ve rendered the JSON as “human-readable” with indents and spaced.

In order to extract data from JSON we need to know the path and key names, and being able to see the structure is crucial to seeing the pathway to the elements you want to extract. Although it’s possible to build scripts that loop through JSON, lists the keys, then extracts the values based on those keys without looking at the JSON, I firmly believe you can’t rely on trusting the data without eye-balling it to understand its structure. Web services construct JSON in interesting ways, from simple to complex.

To that end, the function called JSONFormatElements( JSON ) re-structures the JSON code to make it more readable. Note: This function also serves as a way to confirm that the JSON data you are working with is valid. Invalid JSON would return a “?” followed by some information about the error. For example, an object where the [ ] are not paired would return something like:

? * Line 1, Column 186
  Missing ',' or ']' in array declaration
If the { } braces are not paired, the error would look like this:
? * Line 1, Column 185
  Missing ',' or '}' in object declaration

Outside FileMaker, there are several websites that serve the same function. Some examples:

• http://www.jsoneditoronline.org where you can paste in a JSON object to see the structure
• http://jsonlint.com validates JSON

FileMaker 16 Function

The FileMaker 16 function used to extract data from JSON is called JSONGetElement.

JSONGetElement ( json ; keyOrIndexOrPath )

There are two required parameters. The first is the JSON itself, usually a variable or a field. The second is the more interesting one, as there are three possible options: Key, Index, or Path. The options will depend on the type of the element, such as string, array, number, object, etc.

Example

Let’s begin with some actual JSON code. Below is a JSON object that store contact information: name, address, and a pair of phone numbers. Important: JSONGetElement is case sensitive, so firstName is different from firstname or FirstName.

{
  "firstName": "John",
  "lastName" : "doe",
  "age"      : 26,
  "address"  : {
    "streetAddress": "123 Main street",
    "city"         : "Anytown",
    "postalCode"   : "12345"
  },
  "phoneNumbers": [
    {
      "type"  : "iPhone",
      "number": "123-456-8888"
    },
    {
      "type"  : "home",
      "number": "123-557-8910"
    }
  ]
}

This JSON object contains a mixture of objects, arrays, and key-value pairs. For example, the “firstName” is a key, with “John” being the matched value. The value for the “address” key is an object denoted by the { } braces, containing three key-value pairs: streetAddress, city, postalcode. The value for the “phoneNumbers” key is an array denoted by the [ ] brackets, containing two sets of key-value pairs, where type  and number are the keys.

In order to extract certain elements, the keyOrIndexOrPath parameter will look and behave differently based on where that element exists inside the main JSON object.

To extract the firstName value, the function is simple:

JSONGetElement( JSON ; "firstName" )

This returns “John” as the value.

However, using this exact same function with streetAddress as the parameter returns an empty value, as this key is nested inside the “address” object. This extends the path we need to traverse, so in order to extract any of the values inside the “address” object we need to add this to the path. Each branch in the path is separated by a period. There’s no need to declare the root, or top, path even though it’s possible by simply prefacing the key, such as “.firstName”

So, in order to get the “streetAddress,” we add “address” plus a period in front of this key.

JSONGetElement( JSON ; "address.streetAddress" )

If the address object had additional objects nested inside, the path would be constructed with the object names “separated.with.periods.etc” where the last portion of the string denotes the key for the element being extracted.

Arrays present different challenges. Arrays are denoted by [ ] brackets. There’s no key, and instead, we need to use the numerical index in our JSONGetElement somehow.

We’ll start by extracting out the array itself.

JSONGetElement( JSON ; “phoneNumbers” ) returns the entire array, and we see there JSON objects inside the array, unlike our simple example above with [ value1, value2, value3 ]:

[
    {
        "number" : "123-456-8888",
        "type" : "iPhone"
    },
    {
        "number" : "123-557-8910",
        "type" : "home"
    }
]

Since there are two groups of key-value pairs, to extract just the first one means we have to use the index of the array (remember the key, or index, or path from the parameter). JSON arrays are zero-based, so the first array has an index of zero (0).

JSONGetElement( JSON ; “phoneNumbers[0]” ) returns:

{"number":"123-456-8888","type":"iPhone"}

Now we have a set of name value pairs. To get the number we need to add its key to the path:

JSONGetElement( JSON ; "phoneNumbers[0].number" )

This would result in 123-456-8888. To get the second number you use [1] in the path instead of [0].

JSON objects for different people might contain a variable number of phone numbers. There’s no way to simply extract a list of the “numbers” values from the “phoneNumbers” array. Instead, you would need to loop through the data using a script or recursive custom function.

There are two functions that let you count the number of values in an array: JSONListKeys and JSONListValues.

JSONListKeys( JSON ; “phoneNumbers” ) returns the keys, starting with 0. In the case of the example above it would be a return-delimited list of “0¶1” – note that, unlike other FileMaker list functions, a trailing carriage return will not be appended to the result.

JSONListValues( JSON ; “phoneNumbers” ) returns the actual objects with the key-value pairs:

{"number":"123-456-8888","type":"iPhone"}
{"number":"123-557-8910","type":"home"}

Since strings can contain carriage returns, one way to how many elements there are inside an array is to use ValueCount( JSONListKeys( JSON; “keyOrIndexOrPath” ). This returns two(2) in our example with the phone numbers. In a scripted loop you set a variable to the iteration, i.e. $i, and you extract your data as follows:

JSONGetElement( JSON ; "phoneNumbers[" & $i & "].number" )

Again, since indexes are zero based, the counter for the loop would start at zero, not one.

Parse JSON in FileMaker 16, in Summary

Knowing how to parse JSON first begins with some JSON object. If you’re currently pulling down data from a web service in JSON into a FileMaker 16 file, you can now take advantage of native functions. The way to start is to take the JSON object, look for arrays, nested objects, and key-value pairs. Identify the element you want to extract, determine the path, then test the expression to make sure you get the value that matches what’s in the JSON. Along with this post I’ve added a simple sample file where you can paste in JSON objects and use JSONGetElement on those objects to extract all the data types available in JSON.

Get the Demo File