Home / Blog / FileMaker / Parsing JSON in FileMaker 16
09May 2017

Parsing JSON in FileMaker 16

About the Author

Anders Monsen Anders Monsen

Anders is a Senior Developer for Soliant Consulting. He has developed in FileMaker since 1994, spoken at DevCon from 2010-2013, and written articles on FileMaker and PHP. He is also a runner, cyclist, martial artist, and working on writing crime fiction.

Comments (10)

Jason Wood - May 9, 2017

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

    Anders Monsen
    Anders Monsen - May 9, 2017

    Thanks for catching that. It’s been fixed.

TJ - May 20, 2017

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.

TJ - May 20, 2017
    Anders Monsen
    Anders Monsen - May 22, 2017

    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.

nomfjmt - May 29, 2017

Good intro to JSON format.
Please allow me to translated this article into Japanese, which you can read at .

David - June 9, 2017

As always, exellent information from Soliant.

allanmmcdonald - February 8, 2018

A useful read – thanks for taking the time Anders.

Kevin Frank - February 11, 2018

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.


    Anders Monsen
    Anders Monsen - February 16, 2018

    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 to review the structure of any new JSON.


Leave a Reply