JSON Custom Functions for FM 19.5, part 1

9/13/2022: Demo file has been updated to fix a bug in JSON.MergeArrays.

Demo File

JSON Custom Functions for FM 19.5.zip

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.

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 —

['key.name']

— 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.