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

FM18: JSON.UpdateArrayElement

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.

Bug fixes: Demo file has been updated to v3.

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)

2019-08-13_16-02-58.png

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…
    2019-08-13_165158.png
    …or this…
    2019-08-13_160700.png
    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:

2019-08-13_165636
Array address [6] is empty
2019-08-13_164036.png
The null at array address [2] is effectively empty
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,.

2019-08-13_170834.png

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

2019-08-13_17-14-08.png

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.

2019-08-14_13-06-21.png

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(!).

2019-08-13_172530.png

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:

2019-08-14_060701.png
Garbage in, garbage out

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

2019-08-14_060332.png
Garbage in… uh… what the heck?

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.

2019-08-14_061418.png

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)…

2019-08-14_075138.png

…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…

2019-08-14_075718.png

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

2019-08-15_065625.png
Malformed JSON is correctly identified
2019-08-15_070004
Well-formed JSON is correctly identified

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

2019-08-14_16-02-30.png

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

2019-08-14_16-04-01

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.

3 thoughts on “FM18: JSON.UpdateArrayElement”

  1. Hi Kevin, Hi Paul, ….

    woo-hoo! Oh what fun we’re having!

    OK …

    Problem 1, where an existing value in an array won’t be overwritten…

    …is maybe a niggling behaviour issue, but let’s discuss that another time – because we have bigger fish to fry!

    Problem 2, where the JSON functions get confused…

    …is indeed really a FileMaker bug BUT EVEN MORE TRICKSIER than what you have documented here. (See FileMaker community issue page https://community.filemaker.com/en/s/question/0D70H000004fEhr/fmp18-bug-the-function-jsonsetelement-111-1-breaks-the-calculation-engine for the full discussion.)

    *First up*

    **The problem seems to be caused by an internal cacheing problem in the JSON functions.**

    This means that the ORDER in which the JSON functions are called is fundamental.

    This means that we should eliminate the List function from our testing, since it only confuses the situation because it evaluates its contents IN REVERSE ORDER, as illustrated by these examples:

    Normal left-to right order:

    Let( $i = 0 ; Quote(
    Let( $i=$i+1 ; $i ) & ¶ &
    Let( $i=$i+1 ; $i ) & ¶ &
    Let( $i=$i+1 ; $i )
    ))

    => “1¶2¶3”

    whereas:

    Let( $i = 0 ;Quote(List(
    Let( $i=$i+1 ; $i ) ;
    Let( $i=$i+1 ; $i ) ;
    Let( $i=$i+1 ; $i ) ;
    )))

    => “3¶2¶1”

    (Now, isn’t that interesting?!)

    *Secondly*

    – The problem seems to be (is!) triggered by the JSONSetElements function
    – when an empty path string is passed
    – when a JSONString value is passed
    – which the string value also happens to be a valid JSON array/object (e.g. “[]”, “{}”, “[111]”,”{\”a\”:1}”)

    Note: I already reported the JSONSetElement function with an empty path as problematic over a year ago on the community here: “JSONSetElement with empty path overwrites the whole JSON” https://community.filemaker.com/en/s/feed/0D50H00006ezLf1SAE) – since the function simply overwrites the entire target JSON with an exact copy of the value – not even the JSON representation of the value (e.g.”¶” is NOT converted to “\r”).

    I reported it then as a totally useless function, and now it turns out to also be a useless AND disruptive function, since it also screws the JSON cache.

    *Thirdly*

    – The problem then comes to the fore in all following JSON functions that act on *exactly* the same JSON string
    – As soon as a *different* json string is acted on the cache is overwritten and thus corrected.

    This can be seen clearly in the following example code

    Expression:

    Let(
    [
    json=”[111]” ; /* a string which happens to be a valid JSON array / object, e.g. “[]”, “{}”, “[111]”,”{\”a\”:1}” */
    jsonSame=”[111]” ; /* an identical string (inc. identical white-space!) */
    jsonDiff=”[222]” ; /* a different string */
    sep = “¶-¶”
    ];
    JSONGetElement ( json ; 0 ) & sep &
    JSONGetElement ( jsonSame ; 0 ) & sep &
    JSONGetElement ( jsonDiff ; 0 ) & sep &
    “====¶” &
    JSONSetElement ( “” ; “” ; json ; JSONString ) & sep & /* <<< Cache-problem occurs here */
    JSONGetElement ( json ; 0 ) & sep & /* <<< Cache-problem is to be seen where the json is identical to the above VALUE String */
    JSONGetElement ( json ; 0 ) & sep & /* <<< Cache-problem is to be seen where the json is identical to the above VALUE String */
    JSONGetElement ( jsonSame ; 0 ) & sep & /* <<< Cache-problem is to be seen where the json is identical to the above VALUE String */
    "====¶" &
    JSONGetElement ( jsonDiff ; 0 ) & sep & /* <<< Cache-problem is cleared, when a different json-string is targetted */
    JSONGetElement ( json ; 0 ) & sep &
    JSONGetElement ( jsonSame ; 0 ) & sep &
    JSONGetElement ( jsonDiff ; 0 ) & sep &
    ""
    )

    Result:

    111

    111

    222

    ====
    [111]

    ?

    ?

    ?

    ====
    222

    111

    111

    222

    (The first [111] CAUSES the problem, which then appears 3 times as ?, before being reset)

    *Fourthly*

    Now we understand the problem, please everybody join the cause – of lobbying FileMaker (ahem .. Claris) to quickly fix the issue.

    But how? What is the fix?

    ….OMG!

    …I have just discovered EXACTLY what is going wrong with the JSONSetElements function when it has an empty path – and changed my mind on how the function could be sensibly reworked.

    ….oh dear … I need to look at this closer …. it's pretty screwy.

Leave a Reply

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