JSON in Filemaker
Recently, a client I work with was struggling to decipher some FileMaker scripting because I had used JSON as part of the code. One of my favourite things to do is to not only code unique solutions to perplexing problems but to teach as well. This is a great benefit to my clients - and also lets them in on some of the fun! But writing something that is inaccessible is at best a humble brag and at worst a demoralizing flex. I want to use this post to break down JSON and its uses so that others can make use of this powerful tool.
What the Heck is JSON
JSON stands for JavaScript Object Notation - helpful right?!? At its heart, JSON is a way to systematically and predictably store and retrieve information. It is structured in a key value pair, the key being the name assigned and value being the information referenced by that name. JSON unformatted looks like this {“type”:”fruit”,”name”:”apple”,”quantity”:2} but more commonly it will present like:
{
“type”:”fruit”,
”name”:”apple”,
”quantity”:2
}
JSON has data types implied by its formatting. In the above object the key:value pair of “type”:”fruit” is an example of a STRING where ”quantity”:2 is an example of a NUMBER data type (notice the absense of "" around the 2). Both string and number (as well as the less common Boolean) refer to the data type of VALUE in the key:value pairs. When you group a set of key:value pairs together this is called a JSON Object. The above example would therefore be an object. Finally, when you assign a number of values to a key, that is an array. {"fruit":["apple","pineapple"]} is an array of key "fruit". It is implied by the [] around the values.
Information can be nested within a JSON object. Nesting is very powerful. For example:
{
"groceries" :
{
"dairy" :
[
{
"name" : "milk",
"quantity" : 12
},
{
"name" : "yogurt",
"quantity" : 25
}
],
"fruit" :
[
{
"name" : "apple",
"quantity" : 2
},
{
"name" : "pineapple",
"quantity" : 5
}
]
}
}
The above is an example of a nested blob of groceries. Hopefully you can see it is made up of an ARRAY of fruit and an ARRAY of dairy. Within each array we have an object with the keys of name and quantity where name is a STRING data type and quantity is a NUMBER data type. Here is the powerful part. We reach onto the blob and grab the fruit object and get:
{
"name":"apple",
"quantity";2
},
{
"name":"pineapple",
"quantity";5
}
Or we can retrieve the first record in the dairy array and get:
{
"name":"milk",
"quantity";12
}
Or we can ask for the value of name in the first record of the dairy array to get "milk" returned. Nesting information allows us to meaningfully cluster information together and retrieve it very easily.
Advantages of JSON
This formatted state of JSON is attractive as it is easy to read (at least it is relative to other code - try deciphering XML!?!). Another advantage is that it is text based and therefore extremely light weight. In other words its doesn't slow your computer down noticeably when using it.
There are other text methods of gathering, storing and retrieving data, including SQL and XML. JSON biggest advantage of XML is its easy of use and readability. JSON advantage over SQL is its ease of injecting or updating information once it is set. In the above JSON object we can easy update the fruit key to orange but to do so in a SQL result requires some fancy parsing and looping. Therefore, logging, updating and retrieving information stored in a JSON formate is a lot easier.
JSON is also ubiquitous outside of the FileMaker world. In FileMaker it is like we speak Dutch. Great and powerful language but really really limiting - unless you speak English as well. Likewise, JSON has become the way information is shared "under the hood" in the web-er-nets. If we can use it within our system then our solutions can work outside the FIleMaker world fluently - a massive advantage
Downsides of JSON
JSON is not always the right solution. JSON is not inherently relational. Now JSONista's before you jump all over me - hear me out. When there is a giant blob of nested JSON information, one cannot easily determine to following query: "Get me the count of all types that equal fruit?". Or even more difficult, "Get the count and name where type is 'fruit'". Again, we can with fancy whizzy looping - or we can use SQL. (Performing SQL on a JSON blob in FileMaker is possible - but tricky - as it involves the virtual tables technique. Best stick with vanilla SQL if at all possible)
It is also difficult to perform aggregate functions on a JSPN blob. "Get me the average quantity of all records where type is fruit". Summarizing can be difficult. The while function makes it a bit easier - but that function is not easy to set up and decode when it breaks. Again, if you need functions like MAX or SUM best to try to use SQL rather than JSON.
Uses in FileMaker
Give the above advantages, it is not surprising that JSON use has exploded within FileMaker developers. Developers are using it to store, pass and recall variables within and amongst FileMaker scripts. The FileMaker Data API and Execute Data API return data in JSON. Calls to google maps or mail chip or AWS all return JSON objects. Recently, I have started using it to set logs within larger scripts as setting a new key does not delete the information all ready set. Therefore it is super easy to add information to a blob as you go.
JSONget
JSONGetElement is the function used to pull out data. Consider the grocery BLOB example above where we pulled out the fruit array. To do this we need to tell the function which variable or field the JSON blob is in. For this example, lets say it is in the variable $_params. To extract fruit we would call JSONGetElement($_params;"fruit"). The result would be the fruit object.
Now lets say we want the first record of the fruit object. In JSON Arrays are zero based meaning the first record is counted 0 not 1. Therefore to get the first record of object fruit we would write JSONGetElement($_params;fruit[0]). Recall that array are ear marked using [] so we use them here to tell the system we want the whole array. This would return:
{
"name":"apple",
"quantity";2
}.
Now we want to retrieve the name of the fruit in the first record of the fruit array. We would write JSONGetElement(&$_params;"fruit[0]name") to get "apple" as the reply.
JSON can nest objects within objects if you recall. Therefore, we can have an instance where the fruit and dairy array are nested within a "grocery" object, which would look like this:
{
"groceries";
{
"fruit":
[
{
"name":"apple",
"quantity";2
},
{
"name":"pineapple",
"quantity";5
}
]
"dairy":
[
{
"name":"milk",
"quantity";12
},
{
"name":"yogurt",
"quantity";25
}
]
}
}.
Using this example, if we want to retrieve the name of the fruit in the first record of the fruit array within the object grocery. We would write JSONGetElement(&$_params;"grocery.fruit[0]name") to get "apple" as the reply. In this case we use "." in the search string to denote that we want the system to search within the grocery object and look for the fruit key. The [0] tells the system to look within the first record of the fruit array and the "name" denotes the key to present as the result.
JSONset
Here is where I got stuck initially. For me, and I suspect for many, setting JSON within FileMaker is challenging. The trick for me was to not try to use the JSONsetElement function multiple times (or not to nest the function within the function) as this begets really confusing code. Rather use the get style notation in the set and set each and every line in one go. Here is an example of what JSONsetElement would look like if we were to set the grocery blob:
JSONSetElement ( $_params
;["groceries.fruit[0]name";"apple";JSONString ]
;["groceries.fruit[0]quantity";2;JSONNumber ]
;["groceries.fruit[1]name";"pineapple";JSONString ]
;["groceries.fruit[1]quantity";5;JSONNumber ]
;["groceries.dairy[0]name";"milk";JSONString ]
;["groceries.dairy[0]quantity";12;JSONNumber ]
;["groceries.dairy[1]name";"yogurt";JSONString ]
;["groceries.dairy[1]quantity";25;JSONNumber ]
)
I would hope that really reviewing the above code to make sure you understand what it is doing would be helpful! Note that the data types being set are only STRING and NUMBER type. No where does it say to set an OBJECT or ARRAY (event thought the resulting blob does have objects and arrays). This is because the OBJECTs and ARRAYS are implied in the set notation. "groceries.fruit[0]name" implies a groceries OBJECT (by using the ".") and the FRUIT and DAIRY are arrays by using [0] or [1]. We do not need to make that explicite.
The above example is about as complicated as it gets. And fortunately when reviewing my code I have usually set a lot of that for you. I use JSON exclusively now to set up the4 parameters for a script. In all my larger scripts, I set up test parameters for when the passed parameters are empty. Often my scripts look like this:
set error capture ON
Allow user abort OFF
IF (IsEmpty(Get(ScriptParameter))
set variable &_params: JSONSetElement ( ""
//;["type";$_type;JSONString ]
;["by";Get ( AccountName );JSONString ]
;["id";People::_PlanningCenterID;JSONString ]
;["date";Get(CurrentDate);JSONString ]
//;["info";Get ( AccountName )&" sent an email";JSONString ]
)
Else
set variable &_params to Get(ScriptParameter)
END IF
The code after the ELSE clause is what will run when the script is called by a button and the parameters are passed to it by that button. The line after the IF gives the parameters to use. This is the JSONSetElement block. The lines that are preceded with "//" are optional. To include them remove the "//". To set a button to run this script, create a button element on the layout, and set it to preform script. Point it to the script and set the parameters by copying and pasting the values in the $_params variable after the IF line. Change the VALUE items as needed (but not the keys). The script will then accept this JSON blob as its parameters and use it to generate the variables needed to complete the script. In this case, you could adjust the date parameter to be whatever date you want, or use the predefined Get(CurrentDate). Likewise the "by" key defaults to Get(accountname) but can be set to whatever value needed. Type is optionally set and what field that value sets or how the variable is used needs to be determined by exploring the code of the script. Finally, "info" is optional and deciding to include this parameter needs to be determined by reading the script. If you determine you do want to include it remove the "//" and set the VALUE to whatever would be best.
JSON Gotcha’s
There are a few things to keep in mind as you work through and use JSON. First, JSON is caseSensitive. In the above example, JSONGetElement($_params ; "ID") will not pull out the "id" value. I use camelCase in all my JSON code so that the keys structure are predicable. Second, the resulting JSON within FileMaker when JSONSetElement is used (or when getting a blob back from the Data API) are all unformatted and very hard to read. Make sure you wrap the resulting variable in JSONFormatElements to make it much more readable. NOTE though that if you attempt to format a result that is a pure data type (like you would if you call JSONGetElement($_parameter;"id") it will throw an error as the resulting data is not JSON.
JSON and You
My hope is that this article has helped you develop a base understanding of what JSON is, how to read it and began down the road towards using it. I have attached an example file which will take you through the sections of this article and test your understanding as well as allow you to safely play with JSON. I hope with these two tools, you are welcomed into the useful and valuable world of JSON within FileMaker.
Work With Me
If you’d like to work with me, I provide business consulting and coaching, custom FileMaker solutions, app development, api integration, customer management solutions for businesses of any size. I’d be happy to discuss with you your current challenges and how business automation can help your business to run more efficiently. Contact me to get started.
