This article is part of a series. See also…
• JSON Custom Functions for FM 19.5, part 2
• JSON Custom Functions for FM, part 3
Demo File
Introduction
Today we have some custom functions (CFs) that can help you accomplish various JSON-related tasks in FileMaker. Back in 2018 I had this to say about JSON custom functions…
My inclination is to really understand something before I use a custom function to simplify things, but that’s a matter of personal choice… and one which can vary depending on the situation.
And four years later I find myself using JSON custom functions on a daily basis, to save time and to boost productivity — for example, to merge two objects into a single object, or to deduplicate an array.
Note: where appropriate, some of today’s custom functions utilize bracket notation to avoid unexpected results when and if key names contain wonky characters such as dots, brackets or braces. You can read more about bracket notation in Thinking About JSON, part 4.
And with that said, let’s move on to…
The Custom Functions
Disclaimer: use at your own risk, these CFs may contain bugs, these CFs may not be performant when processing large amounts of data, always test carefully before deployment, etc., etc., etc.
Here is the complete list, 23 in all. We’re going to look at the first eight today, and the remainder in part 2.
JSON.MergeObjects ( object1 ; object2 )
Purpose: combine two JSON objects into a single object; if the same key exists in both objects, object2 will prevail.
JSON.MergeArrays ( array1 ; array2 ; insertionPointForArray2 )
Purpose: combine two arrays into a single array
[Superseded by the upgraded version in JSON Custom Functions for FM, part 3.]
JSON.ObjectFromFieldList ( fieldList ; optionalKeyList )
Purpose: creates a correctly-typed JSON object from a list of fully-qualified field names
Ex. 1 — without optional key list
Ex. 2 — with optional key list
Here is the definition of sales::as_json…
…and it returns an object like this for each record in the sales table:
JSON.ArrayFromList ( listOfArrayItems )
Purpose: takes a list as input and creates an array of correctly-typed items
Ex. 1 — array from simple list
Ex. 2 — cities in Rhode Island where we have customers
Ex. 3 — array of objects from current found set in sales
…where s_list_as_json is defined like so.
[At the risk of stating the obvious, make sure your found set is of a reasonable size before invoking this particular incantation.]
Ex. 4 — related sales for one customer
JSON.Format2D ( json )
Purpose: Format a 2-dimensional JSON array or object containing arrays so that all array rows maintain their horizontal aspect
[Superseded by the upgraded version in JSON Custom Functions for FM, part 3.]
JSON.ArrayFromSQL ( selectList ; from ; where )
Purpose: generate a 2-dimensional JSON array from the results of a SQL query
JSON.ArrayOfObjectsFromSQL ( selectList ; from ; where ; optionalKeyList )
Purpose: generate a 2-dimensional JSON array of objects from the results of a SQL query
Ex. 1 — without optional key list
Ex. 2 — with optional key list
JSON.GetValueType ( input )
This is the “engine” that drives a number of the other CFs. It was discussed in detail in JSON.GetValueType.
Closing Thoughts
So far we’ve looked at eight of the custom functions in the demo file. We’ll continue with the remaining ones next time.
Fantastic stuff! Thanks for sharing.
I appreciate you saying so Shawn.