JSON Optimization in FM 21

Introduction

If you work with large JSON structures in FileMaker, you may be aware that the current shipping version of the product (FileMaker Pro 21, a.k.a. FileMaker Pro 2024) has a single-element JSON cache, which is reset whenever JSONSetElement or JSONGetElement is called, and that you can boost performance by taking this into account.

Six months ago Josh Halpern was kind enough draw my attention to this in a comment on one of my articles, and added…

“If you’re interested, I wrote a couple more points about how the JSON cache behaves here: https://the.fmsoup.org/t/performance-core-principles-10-scripting/1867/25?u=jwilling. But the gist is… only touch one json at a time.”

[The above is required reading as far as I’m concerned, and has changed the way I work with JSON.]

More recently I was discussing JSON caching with Steve Senft-Herrera, and he shared a couple additional optimization ideas that had not occurred to me, and that I am definitely adding to my tool kit. (Thank you Steve.)

At any rate, today we’re going to take a look at applying all three of these ideas to speed up processing of large JSON structures within a While statement, and you can follow along in today’s demo file if you are so inclined.

Demo file: fm-21-json-optimization.zip  (requires FM 21 or later)

General Observations

Today’s file is similar to other sandbox files featured here recently. Input is an array of objects which, when you click “refresh”, will be parsed into the Output field via a While statement in the Query field, with Note providing a bit of context for each example.

There are three pairs of examples, with odd numbered ones designated “slower” and even ones designated “faster” — the former being a bit more readable; the latter more compact and efficient.

In all the examples the input is the same — an array of 750 objects, which is large enough for FileMaker’s JSON cache to make a difference, but small enough that we don’t have to wait too long for the slowest examples to execute.

Also, at the risk of stating the obvious, an array of 750 objects is not a terribly large amount of JSON, assuming each object is reasonably sized. The disparity between the “slower” and “faster” approaches would be more dramatic with larger input payloads, but what we have here is sufficient to make a good argument for taking the “faster” route when possible.

Also, keep your eye on that bit of red text in the Query field. We’re going to do something interesting there in examples 5 and 6.

Note 1: observations, inferences and conclusions in this article specifically refer to the currently shipping version (FileMaker Pro 21).

Note 2: the demo is completely open, and all fields are editable so you can conduct your own experiments.

Note 3: the source for the input is a subset of JSON-LD.

Examples 1 & 2

In these first two examples we iterate through the array of 750 objects, calculating the sum of the lengths of all the “@id” values.

Example 1

In this “slower” example, we call JSONGetElement (JGE) twice in succession, repeatedly clobbering and generating new caches each time JGE is invoked to populate the “object” (1) and “value” (2) variables. On my Windows 10 laptop this output takes about 8.2 seconds to generate.

Example 2

Example 2 is similarly constructed, except a single JGE call (1) maintains the cache, rather than “busting” it, and this version runs about three seconds faster than example 1.

Examples 3 & 4

In this pair of examples the goal is to generate an array by parsing the “@id” value from each object. Note that we offset items in the output array by one so address [0] is available to display stats.

Example 3

Normally one might be inclined to produce the output array via JSONSetElement, but that would be a cache-buster. Instead, we designate a “delimiter” (1), iteratively build up a list of values (2), and only when we’re done iterating do we (3) transform the list into an array via JSONMakeArray.

It’s a great idea, and faster than using JSONSetElement to iteratively create the array, but we are still hampered by a pair of cache-busting calls at (4) and (5), and example 3 takes about 8 seconds.

Example 4

Here we are not busting any caches. The single JGE (1) maintains the cache, and example 4 is nearly three seconds faster than its predecessor.

Examples 5 & 6

We wind up today with a trick that comes from way out in left field: pre-parsing the input by applying JSONGetElement ( input ; "" ). Apparently this causes the input to be cached in a way the calculation engine finds easier to work with. And, yes, I invite comments from readers to correct, expand on, or elucidate this assumption. I asked Steve if he knew who came up with the idea, and he replied…

I feel that credit for the actual discovery should go to a person named Brendan Pierce, who originally shared an observation which I consider to be equivalent.

Example 5

Example 5 is identical to example 3, except for the pre-parsing at (1). Cache-busting still occurs at (2) and (3) but the effect is less egregious and this example runs more than twice as fast as example 3.

Example 6

And, finally, example 6 implements all three of the ideas we’ve seen today. We pre-parse at (1), maintain the cache at (2), and generate the output array without touching the JSON cache at (3).

The results speak for themselves… this runs 5x faster than example 5 and 10x faster than example 3.

Conclusion

Thank you Josh, Steve and Brendan. These are game-changing ideas, each of which can be applied independently of the others. The pre-parsing trick is especially brilliant, and should be easy to retro-fit into existing code with a minimum of fuss… though as always with information you find for free on the Web, use these ideas at your own risk, and with a healthy dose of skepticism and common sense.

P.S. if you’re curious about the use of NumToJText in these examples, see the “bonus tip” at the end of this article.