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:

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

16 thoughts on “Parsing JSON in FileMaker 16”

  1. Great overview, thanks! One formatting error… The last JSONGetElement example displays html entities instead of simply the “&” character.

  2. Very helpful overview. Wondering if you could explain how you can loop through the JSON when it has 830 keys and put the data in the value into a new record? Here is a sample JSON file with 830 records.

    1. In your JSON you have an array with 830 items of data. You’ll need a combination of JSONGetElement and JSONListKeys to traverse your JSON to extract each piece and insert this into a new record. To begin, you can get the number of records you’ll need to traverse using JSONListKeys ( $JSON ; “cases” ). In this case it lists all 830, and if you prefix this with ValueCount you get just the number “830” which is how many times to loop through the JSON.
      In your script, write a loop that starts with the counter at zero and exits once it exceeds your count of 830. Inside the loop, you’re going to extract the various elements. In this case I’ll just explicitly name one. Since they’re in an array, you need to use the array index:
      JSONGetElement( $JSON ; “cases[1].chapter” ) — this returns the second record’s “chapter” value.
      JSONGetElement( $JSON ; “cases[” & $counter & “].chapter” ) — use your variable in the script to increment

      You can set variables with the above examples to check your progress, and inside the loop create the new records and set your fields once you have the data.

      Where it gets interesting is that in this array you have a second array – “parties.” You’ll need to extract the items in the array the same way, making sure to reset your second counter for each of the 830 records.
      JSONListKeys ( $JSON ; “cases[0].parties” ) — lists your keys for the first record.
      Add ValueCount in front of this and you get three child records that need to be created. Further, some of these parties have nested objects, and you need the right path to extract those – something like:
      JSONGetElement( $JSON ; “cases[1].parties[0].address.lastName” )
      JSONGetElement( $JSON ; “cases[“& $record_array_counter &”].parties[“& $parties_array_counter &”].address.lastName” )
      Here I’m using $example_names in the array counter variables to illustrate the corresponding array.

      Also remember that the keys are case sensitive so “lastname” would not work.

      I hope this helps.

  3. Hi Anders,

    a ) Your 2017 DevCon session on JSON was remarkable; I keep referring back to the session materials, so thank you.

    b ) I copied and pasted the sample JSON from this article into a text field in a test DB so I could experiment via the data viewer. JSONFormatElements didn’t like it, but there wasn’t anything obviously wrong with your code… I ended up replacing all multiple spaces with a single space (and retyping all the spaces to play it safe)… and also I had to remove and retype the “:” after the second “type” key… sounds like voodoo, but at any rate, that’s what I had to do before JSONFormatElements would recognize the JSON… and of course having valid JSON was a prerequisite for JSONGetElement to work properly. I mention this in case anyone else runs into the same problem.

    Regards,
    Kevin

    1. Hi Kevin,
      a) Thanks for your kind comments about the session. I read your articles on a regular basis and am always impressed by your knowledge and approaches to interesting FileMaker solutions.
      b) A great comment. There’s likely some formatting on the web site to make the JSON show up pretty on the page. When I copied the text from the web site into a text editor I saw invisible characters. Once I removed those characters then JSONFormatElements recognized the JSON without errors. When you get JSON from web services this formatting issue shouldn’t be a problem, but always something to keep in mind when copying over formatted text.

      While I’m thinking about it, one thing I left out of the article is that when I come across new JSON for the first time I will paste the JSON into an online editor. This lets me trace the path to each element better than JSONFormatElements, particularly on large blocks of JSON with many nested objects and arrays. I know I demonstrated this in the DevCon session, but this gives me a chance to once again recommend using an online JSON editor such as https://jsoneditoronline.org/ to review the structure of any new JSON.

      Cheers,
      Anders

  4. If the data returned is an array, such as:

    [
    {…firstObject},
    {…secondObject}
    ]

    How can you test for this, and also get the array count.

  5. David,
    Not sure I understand the question. If you have an array with objects inside the array, are you trying to find way to extra items from inside the object? Usually I’d take a look at the JSON structure before trying to parse anything. Once I know the structure, and this is a consistent result, then I’d use JSONGetElement based on the path to the element that I need. Is that what you’re asking, or something else?
    Anders

    1. Hello,
      I’m not sure if you’re talking about the Data API or the Admin API, but yes, you can send JSON arrays in your requests. For example, in the find request (POST) your query would contain an array of the fields you’re searching on plus the actual request. If you look at the FileMaker help files online there’s extensive documentation in how to set up queries and the sort of JSON (object or array) each query takes.
      I hope that helps.


      Anders

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top