Render Found Set as JSON Object

Demo file: found-set-to-json-object.zip (40MB; requires FM 18 or later)

[Author’s note: the demo file is a work in progress. Modify it as you see fit.]

Today we’re going to compare various methods one might consider employing to render an arbitrary found set as a JSON object. The demo consists of a table of US customers with 250K records, and the JSON object will be structured like so…

{ 
   State Name: 
      { County : [ [ customer array ] , [ customer array ] ... ] , 
        County : [ [ customer array ] , [ customer array ] ... ] , 
        ...
      } , 
   State Name:  
      { County : [ [ customer array ] , [ customer array ] ... ] , 
        County : [ [ customer array ] , [ customer array ] ... ] , 
        ...
      } , 
   ...
}

…i.e., grouped by state name and county, with customer data represented as a two-dimensional array, for example:

Here are the tables…

…here is the relationships graph…

…and note that state names live in their own table, and are related to customers via the standard two-character abbreviation. I mention this because the methods we use to generate the JSON will need to take this into account.

Another thing to be aware of is that some of the methods are significantly slower than others, so until you get a sense of which scripts run quickly, you’ll want to start from a small found set, rather than all 250K records, either by locating your own found set, or running one of these highlighted scripts.

After ensuring you are in an appropriately-sized found set, you can run scripts locally…

…or, if the demo is hosted and PSOS (Perform Script On Server) is checked…

…you can do the heavy lifting server-side…

…because each script in this group invokes a corresponding subscript, via PSOS when possible, otherwise via Perform Script.

Working with the Demo

Performance can vary widely depending on whether you are running the demo locally vs. hosted, and if hosted, whether you’re accessing across a LAN or WAN.

As mentioned, you’ll want to start out with an appropriately-sized found set, and conveniently, at startup, the demo puts you into a found set of 500 random records, which is small enough to ensure you won’t be kept waiting too long with any of the scripts. Scripts running client side can be cancelled by the user (except for scripts 1, 2, 21 & 22, which do their heavy lifting in a single step), but of course cancelling won’t work for subscripts running server side.

Okay, now that you’ve got your found set lined up, it’s time to run one of the scripts. When the script finishes, a global variable ($$json) will be populated, and a card window will appear displaying some statistics along with the results…

…or, more legibly, with the JSONFormatElements box checked.

And if you’d like to compare or review script runs, click the “Log” button.

A Note Regarding Caching

FileMaker is extremely efficient when it comes to caching sorts, so if you want to attempt to be scientific in your comparisons, you could invoke “find random records…” before each script trial (keyboard shortcut is Cmd-5 on the Mac and Ctrl-5 on Windows).

Okay, that pretty much covers the general aspects of the demo. Let’s look at some points of interest in the individual scripts. I’ll have various things to say about the first ten (client side) scripts, and then some additional observations to make re: the PSOS-compatible scripts.

1. while – standard array

In terms of sheer elegance it’s hard to beat an approach that does the heavy lifting in a single script step.

Regrettably, this “elegance” doesn’t translate into acceptable performance for any but the smallest found sets, but we can improve things if we’re willing to define a calculated helper field.

2. while – calc field array

This script is identical to its predecessor except we’re going to offload some of the work to a helper field, customers::array, defined like so:

Now we can simplify our While statement to reference this field instead of having to generate a 1D array on the fly for each customer in the found set.

What’s the benefit? Preliminary testing indicates that, regardless of found set size, script 2 will execute more than 2x as fast as script 1. Unfortunately this performance gain is merely relative, and both of these While scripts bog down quickly as found set sizes increase. But keep your eye on that array field. We’ll be seeing more of him.

Note: for more information on While (and SetRecursion) see:

• New in FM 18: SetRecursion and While
• New in FM 18: While, part 2

3. walk each record / standard array

I decided to include this approach because it represents an obvious path one might take to solve today’s JSON challenge. The basic idea is to loop through your found set from top to bottom, building the JSON one record at a time like so:

Like scripts 1 & 2, this approach not going to win any prizes for blazing performance (though it’s not too bad when found set sizes are small).

4. walk each record / calc field array

This script is identical to #3, except that, rather than generating the array at run time, it references the array helper field.

5. walk each record / calc field array / insert calc result / slow sort

So far each script we’ve seen has used JSONSetElement to populate the $$json variable. Here, instead of JSONSetElement, we employ Russell Watson’s amazing “Insert Calculated Result” trick to manually assemble our JSON, line by line, from the top down. Here’s what this “handcrafted” JSON looks like (to see it in the Results window, make sure to uncheck JSONFormatElements before running the script):

Unfortunately, script 5 is hamstrung by the need to sort the customers table by the related state name in the states table…

…and sorting on related fields can be a performance killer, especially across a WAN.

Why not simply sort by the 2-letter “st” abbreviation in the customer table? Because (assuming the JSONFormatElements box is not checked) that would result in our state names appearing out of sequence, e.g., since AK precedes AL, Alaska would precede Alabama.

Wouldn’t it be nice if there were some way to get the benefit of sorting by state name, without sorting on a related field (or, heaven forbid, denormalizing our solution by storing the state names in the customers table)? Well, it turns out there is, which brings us to…

6. walk each record / calc field array / insert calc result / fast sort

This script is identical to its predecessor except for the Sort Records step, which uses this trick to eat its cake and have it too, so to speak.

(For more information see Fast Summaries Re-revisited.) Note: using this little trick across a WAN can result in a 10x or better performance boost.

Scripts 7, 8, 9, and 10 (fast summary / etc)

I’ve written a lot about so-called Fast Summaries over the years, e.g.,

• Fast Summaries
• Fast Summaries Revisited
• Fast Summaries Re-revisited

…and have made copious use of the technique in many articles on Virtual List and elsewhere. But is the technique a good fit here? Indeed it is. Reminder: the basic idea behind Fast Summaries is that you first sort your records, and then skip lightly across the found set, stopping once per group to aggregate data for that group.

Aside from the various tricks we’ve already seen, the new idea in this group of scripts is to slurp up a 2D array (corresponding to all customers for a given county within the found set). In script 7 this is done via the While function, and in 8, 9 and 10 via a summary list field, which is pointing at our old friend, the calculated array field.

Bottom line: script #10 is one speedy performer… though script #6 will give it some serious competition when found set sizes aren’t too large.

The PSOS-compatible Scripts

[Note: I am intentionally avoiding making any performance claims for these scripts vs. scripts 1 – 10… there are so many factors that can affect server-side vs. client-side performance, including LAN vs. WAN, network speed and latency, how busy the server is when you initiate PSOS, and the relative speed of your client computer vs. the host computer. I encourage you to experiment and draw your own conclusions.]

Scripts 21 – 30 are PSOS-compatible versions of scripts 1 – 10, and the higher-numbered scripts are variations on script 30 with work arounds for PSOS parameter- and result-length limitations.

In scripts 21 – 40 we transfer the found set from the client to the server by passing a return-delimited list of ID numbers as a parameter. The subscript will then assemble the found set via the simple expedient of a GTRR (Go to related records)…

The one exception to the preceding is that when the initial found set consists of all records, then there’s no need to transfer the found set to the server, and we can instead simply do a “show all records” server side.

Note: Greg Lane at Skeleton Key and Mislav Kos at Soliant have both produced thoughtful, detailed articles on this topic.
• Restoring a FileMaker Client’s Found Set Within A Server-Side Script
• Restoring a FileMaker Found Set

PSOS Script Result Character Limit

When the subscript has done its work, the JSON generated server side needs to be passed back to the calling script on the client, and the obvious way to accomplish this is via a script result. And for small found sets that’s fine, but if you see this error dialog…

…it means your result has exceeded the 1 million character limit.

Script 40t works around this limitation by passing the data from the server back to the client via a text field in a transfer table. The subscript also passes the ID of the record in the transfer table as a result, which enables the parent script to locate the proper transfer record.

Script 40c is a variation on the preceding and utilizes an approach which would never have occurred to me — it also uses the transfer table, but the subscript passes the data back as a text file in a container field. This extremely clever, outside-the-box technique was demonstrated by Fabrice Nordmann and Andries Heylen at dotFMP 2018, and you can read about it in detail here: dotFMP Developer Challenge. In script 40c and its corresponding subscript, the technique has been updated to use the TextDecode function and to take advantage of the new-in-18 data file script steps.

For more information on the data file steps, see this helpful Claris article: Using data files in FileMaker Pro Advanced.

PSOS Script Parameter Character Limit

Bear in mind that the PSOS 1M limit applies to script parameters as well as results. To see the parameter limit in action…

1. Go to the customers layout
2. Show all records and unsort
3. Go to the first record
4. Omit 107,142 records
5. Confirm there are 142,858 records in your found set
6. Now run script 91.

At this point you should see this:

Next, omit a single record (so your found set now consists of 142,857 records) and run script 91 again.

So does that mean PSOS isn’t viable for larger found set sizes? Not at all.

As we’ve seen, scripts 40t and 40c bypass the result limit… and scripts 50tt and 50tc do the same for the parameter limit by using the transfer table to move a multiline key of ID numbers representing the found set from the client to the server via a text field, and then on the server side recreating the found set by GTRR-ing…

Note: since the whole point of 50tt and 50tc is to use the transfer table for outgoing transfers, it will do so even when found count = total record count.

What about using a container field + text file for the outgoing transfer? I tested and it’s clearly slower so I didn’t include that approach in the PSOS-compatible group. However, you can benchmark it against the outgoing text transfer method, by comparing script 92 vs. 93.

Closing Thoughts

Today we’ve explored a number of ways to render a found set as JSON. Needless to say there are other ways to skin this particular cat, and to see a number of other  approaches, check out this demo from Paul Jansen: CreateJSONmethodsV11.fmp12.zip

Paul’s demo notes:

This demo was inspired by a thread on the FileMaker community as a way totes and compare the various approaches. Obviously it is a couple of years old now, and we all have a little more experience working with JSON.

The demo includes a bunch of problematic characters in the first record. These expose issues with some of the methods.

Also, some of the faster methods have additional fields added, which I didn’t bother doing for the slow methods. So the comparisons are not truly accurate.

Obviously the JSON calculated fields with ListOf are the fastest, but I really like the merge file export as the fields included in the JSON are controlled by an Export Records script step which add a considerable degree of flexibility. However it is still a bit of a hack.

I added a method with the script step Execute Data API, which interestingly is almost exactly the same speed as ExecuteSQL. I guess they are both using the same FileMaker “find” internally.

And I think that’s enough for today.