BlogFileMaker

How to Handle a JSON Collection in Your FileMaker Solution

By April 30, 2020 No Comments

GeoJSON data contains many properties which can result in large object size. When dealing with large collections of GeoJSON in your FileMaker solution, your JSON file size can balloon, causing the JSON parsing functions to choke and crash FileMaker when you try to work with GeoJSON (or any large collections of JSON).

There are two techniques you can use to optimize manipulation of large arrays of JSON.

We’ll use two different file sizes as example data — one representing all zip codes in Rhode Island (1.7 MB) and another for those Texas (80.1 MB). Rhode Island is a better choice when you’re experimenting, and Texas is a good one to use for load testing and benchmarking.

Get the Demo

Download the demo to follow along.

Preparing The Data With Primitive Text Manipulation Functions

The first step for both options is to use FileMaker’s primitive text manipulation functions and split the collection up into a value list, with each value representing one object in the group. This basic optimization makes it possible to manage such large files, but it does have some drawbacks to be aware of.

The main drawback of this technique is that it relies on the specific white space format and potentially the particular property order of the input object. These are both supposed to be arbitrary in valid JSON. You don’t want to have to rely on them, but often the source of the collection is from a known automated source. If this is the case, we can reasonably risk relying on arbitrary aspects of the input file, which a JSON validator doesn’t enforce.

Defining a primitive text parsing function to split up an array is fast and generally not very complex. Once done, it allows much more efficient parsing of the input value-by-value. Even if each value is a reasonably large and complex object, most parsers, including FileMaker, are better able to process them one-by-one sequentially rather than trying to load them all into memory at the same time.

Loading The Collection as a Records

Let’s walk through a situation in which we want to load the source files into FileMaker as a table of records and look at a subset of the properties. We also want to be able to manipulate the data (add new metadata property) and then save it back out in the original format.

In a GeoJSON collection, a single instance object is called a Feature, and it contains many properties. We’re interested in a handful of identifying properties.

Once we finish our data manipulation, we want to save out the data in the original format, with our one new metadata property added to each one.

Here’s a screenshot of our Manipulator table. Note that we have a field called GeoJsonFeatureObjectInput.

GeoJSONFeatureObjectOutputField

The GeoJsonFeatureObjectInput is a stored text field. In this case, we have a handful of stored calculations which reference it using the native JSONGetElement to extract our identifying properties into our table.

Then we have our NewProperty field, into which we can add our custom metadata object.

Finally, we have our GeoJsonFeatureObjectOutput field, also a stored calculation. We do not use the native JSONSetElement function to manipulate our input object — remember, we rely on the specific white space and order of the input. If we used native JSON functions to rewrite the object, we’d scramble these. We should, therefore, use a simple concatenation technique in the Output field, which preserves the formatting.

Extracting the JSON Collection from the Source File

Lastly, we need to get the JSON collection out of the source file and into FileMaker. In the demo files, we use the native file manipulation features, but you can achieve the same thing with a file plugin.

The file manipulation has been encapsulated into a sub-script called _Ingest File. We’ve annotated the script if you’re interested in the implementation. This process converts the input JSON collection into a Value List, with each value comprised of a single GeoJSON Feature Object.

Splitting Processed Feature Objects Into Records

We can now split up the processed Feature objects into records. Here we can compare two alternatives techniques.

Technique 1: Looping Over the Value List

At first, you may want to loop over the value list. Loops are often fast, and this seems like a direct way to handle it. While this did get the job done, we found it was still pretty time-consuming. On a decent computer using local files, Texas took eight minutes to process. FileMaker was no longer freezing up, but we wanted to process all fifty states’ zip codes on a fairly regular basis. This was less than ideal.

Looping over the value list

Technique 2: Importing Prepared Values

We then realized we could probably import the prepared values from a file as if it was a one-column CSV without much extra effort. We should only need to export the value list as a file and treat it as a one-column import. We just had to add a header row and alter the default line break that FileMaker uses. If you’re following along with the demo file, you’ll see these prep steps in the _Ingest File script. The surprising finding here was that our processing time was ten times faster.

Next Steps for Your FileMaker Solution

We hope you have a better understanding of how to handle a JSON collection in your FileMaker solution quickly and efficiently.

If you have any questions about this process or have questions about another challenge in FileMaker, please feel free to contact our team.

Jeremiah Small

Jeremiah Small

Jeremiah, Soliant Consulting's Director of Engineering, has been with Soliant since 2006. In his spare time, he is a volunteer member of Altadena Mountain Rescue Team. Jeremiah got undergrad and masters degrees from NYU, TSOA, specializing in set design for theater and film. The film/theater and ride/show fabrication industries led him to a career in software consulting where his creative training and general problem-solving talents have served him well.

Leave a Reply

Need to adjust your business processes quickly? We're helping clients use technology to keep their teams productive and running smoothly in these times of uncertainty. Our team can guide yours if you need help in these areas.

Talk to a Consultant