JSON, Level: Advanced, Version: FM 19.5 or later

JSON Custom Functions for FM 19.5, part 1

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


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.

We’ll get to the CFs in a moment, but first I want to digress briefly to talk about…

Problematical Characters In Key Names

In Thinking About JSON, part 4 I touched on several JSON-related developments that have appeared on the FileMaker scene over the last few years, but neglected to mention an important improvement introduced in FM 19.3 — the ability to handle problematical characters such as dots in key names.

As you may know, the issue with dots is they typically serve as path delimiters in JSON structures, but sometimes we encounter (or need to produce) JSON with dots within key names. For example, this:

   "name.first" : "Albert",
   "name.last" : "Schweitzer"

As opposed to this:

   "name" : 
         "first" : "Elvis",
         "last" : "Presley"

You can find the official word from Claris on the subject here — https://help.claris.com/en/pro-help/content/json-functions.html.

In a nutshell, if you want to reference JSON objects with dots or other problematical characters in key names, rather than using standard notation, you need to escape the key names via “bracket notation” like so —


— in addition to the normal double quote wrapper that is always required for object names.

This holds true for both JSONSetElement and JSONGetElement, so let’s combine both the above examples into a single object, and push that object into a variable called $$jsonObject:

Now we can extract both first names by using JSONGetElement, first with standard notation, and then with bracket notation.

If bracket notation seems a bit complicated, don’t worry… once you’ve used it a few times it will make perfect sense. And if you happen to use it when you don’t need it, no harm done (assuming, of course, that you are bracketing a name and not a path)… for example, here with a $keyName that, hypothetically speaking, might or might not contain a dot, both of these statements produce identical JSON when $keyName does not contain a dot.

This next example is identical to the preceding, except now $keyName contains dots. Here we see the benefit of playing it safe and applying bracket notation to unknown or dynamically-generated key names.

And of course dots aren’t the only problematical characters. I don’t have a complete list, but common sense would suggest that brackets and/or braces are going to be a problem in an object name, since they normally play a “meta” role. Here, for reasons unknown, we have been tasked with creating a JSON object with key names that look like array addresses.

And sure enough, brackets in key names cause confusion if we go the standard notation route…

But with bracket notation we can achieve not only the impossible…

…but the ridiculous as well.

At any rate, where appropriate, some of today’s custom functions utilize bracket notation to avoid unexpected results when and if key names contain wonky characters. 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 Thinking About JSON, part 4.

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.


2 thoughts on “JSON Custom Functions for FM 19.5, part 1”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.