FM18: JSON.UpdateArrayElement

18 Sep 2022 – these custom functions have been upgraded – see JSON Custom Functions for FM 19.5, part 2 for details.

Disclaimer: the technique shown in this article is an attempt to work around an annoyance that can crop up when using FileMaker’s native JSONSetElement function, and is provided on an as-is basis. Use with appropriate caution and at your own risk.

Acknowledgement: I owe a debt of gratitude to Paul Jansen for many illuminating JSON-related discussions over the last year or so. Thank you Paul.

Introduction

Today we’re going to look at a custom function, JSON.UpdateArrayElement, written to circumvent a problem you may encounter when using JSONSetElement. (Note: see Thinking About JSON, part 1 and part 2 if you need a basic FM/JSON overview or refresher.)

Demo file: JSON-UpdateArrayElement-v3.zip (requires FM 18 or later)

Why did I write a custom function to do what JSONSetElement apparently already does? Because under certain circumstances JSONSetElement does not work the way I believe it should. We’ll get to the custom function in a minute, but first there are a couple issues we need to examine.

Reminders

1. JSON addressing uses a zero-based index
2. When you specify an array-style address for an item to be added via JSONSetElement, if that location is empty, and when necessary, an array (or, if appropriate, multiple nested arrays) will be created with nulls to fill in preceding slots as needed, e.g., this…
   
   …or this…
   
   I refer to this as an autogenerated array, which has nothing to do with the specified JSON type (in this case JSONString), but is a logical consequence of targeting a non-existent array address… in which case, as we’ve seen, FileMaker magically generates the necessary array structure, and populates any intervening empty addresses with nulls.

A Couple Issues

1. Typically JSONSetElement will overwrite one element with another, regardless of element type, with one exception: JSONSetElement will not overwrite an existing value with an autogenerated array
2. In FM 18 (but not in 16 or 17) JSONFormatElements and other JSON functions can mask mangled JSON errors, making them harder to detect.

Issue #1

Note that I said “…will not overwrite an existing value…” — the preceding examples (where the source is empty so there is no existing value) work fine, as will either of these:

But change the first address segment to [0], [1] or [3], so you are targeting non-empty locations, and you’ve got a problem, e.g,.

The preceding is what you will see in FM 18 on Windows. On the Mac, the result is less verbose.

And finally here is a slight variation on the above, featuring a JSON object rather than an array at the outermost level. The two statements are nearly identical, except that in the first statement, the value corresponding to the “first” key is a text string, whereas in the second statement it is an array.

Conclusion: JSONSetElement resists overwriting non-array elements with autogenerated arrays.

Issue #2

As mentioned, in FM 18, JSONFormatElements masks JSON transformation errors, and it does so by displaying the unaltered source(!).

Furthermore, the problem is not restricted to autogenerated arrays, and it’s also not restricted to JSONFormatElements. In FM 16 and FM 17 here’s how a botched JSON transformation is evaluated by a various JSON functions:

Here’s how the same code is evaluated in FM 18:

The functions ignore the mangled transformation and operate on the original untransformed JSON, creating the illusion that all is well.

Note: this only applies to transformed JSON. Raw (untransformed) JSON appears to be immune to this problem.

Where things get really weird is you can force FM 18 to confront reality by attempting to transform the botched transformation. This code is identical to the preceding, except for the addition of the highlighted entry. Now, all results except for the JSONDeleteElement result are working properly.

Update 16 Aug 2019: See Russell Watson’s comment re: List evaluating from the bottom up rather than from the top down.

And here’s some more weirdness. Rather than returning an error, JSONGetElement returns nothing (which would normally be an indication of well-formed JSON)…

…however, if you, invoke the JSONGetElement call twice, the first occurrence correctly returns an error (since “botched” is not valid JSON), but the second occurrence acts as though all were well.

Thank you Paul Jansen 1. for discovering this…

…2. for suggesting that this odd behavior may be linked to new-in-18 JSON parameter caching, as per a recent FileMaker community posting by Alex Zueiv…

…and 3. for pointing out that appending a space provides an easy fix.

Conclusion: the simple expedient of appending a space allows you to easily test transformed (or untransformed) JSON for validity in FM 18 like so:

IsEmpty ( JSONGetElement ( json & " " ; 9999999 ) )

(The result will be 1 for valid JSON and 0 for invalid JSON.)

The Demo File

The demo file contains 22 example records, and the first ten display the autogenerated array issue when JSONSetElement is selected (assuming the Format box is unchecked).

Here’s the result for the same record with JSON.UpdateArrayElement selected.

At the risk of stating the obvious, I encourage you to play around with the demo, add new examples, and modify existing ones. Also you can see what happens when you check the Format box (bearing in mind issue #2 if JSONSetElement is selected).

As implied by its name, JSON.UpdateArrayElement is designed to work with arrays; you may discover some limited support for object transformation in there as well, but that’s not its primary aim.

Here’s a brief overview of what JSON.UpdateArrayElement does under the hood:

1. Determine the entry point in an existing array where, if necessary, the new array item will be created
2. If the entry point is unoccupied (or contains a null), there won’t be any problem so simply transform the existing JSON using JSONSetElement
3. Otherwise the entry point is occupied so first null it out via JSONSetElement, and then perform step 2

Long-winded code explanations can be tedious, so I won’t say anything more at this time. If you have specific questions, feel free to post them in the comments section.

Next time, we’ll look at a companion custom function, JSON.InsertArrayElement, which enables you to insert an element into an array, with any existing array elements sliding one position rightward to accommodate.