FileMaker 19 + JSONata

Introduction

Recently I had the pleasure of speaking with Jeremy Brown on The Context Podcast about an open-source project called JSONata that enables you to query and transform JSON.

The JSONata site is packed with useful information, including this short video…

…as well as…

• Extensive documentation
• A link to the Stack Overflow forum, where you can ask questions (which may or may not get answered), and
• The JSONata Exerciser, where you can experiment with pre-existing examples, or with your own pasted in JSON

Okay, a JSON query and transformation language sounds intriguing, at least in theory, but wouldn’t it be great if we could harness that power from within FileMaker? Well, now we can, utilizing techniques in this demo, built by Jeremy and shared here with his permission.

Demo file: JSONata.zip (requires FM 19+ on Mac, 19.3+ on Windows)

As you’ve probably surmised, the demo makes use of the Perform JavaScript in Web Viewer script step. If you’d like to learn more re: what’s going on under the hood, Jeremy goes into specifics here: Towards a Better JS Development Environment.

Note: the demo requires an online connection to access the JSONata library.

Some Examples

Instructions: choose an entry on the left, then click “Get result into the field” to see results.

In the demo you’ll find examples that convey a tiny bit of what’s possible with JSONata. For instance, in the above example, the source JSON comes from restcountries.eu. Since each country entry includes a “timezones” array, I decided to query those arrays for countries with more than two time zones.

Here’s another one — for a given set of orders, find the customers whose total sales are > 3x above average.

Do you need JSONata to answer a question like this? Of course not, but consider the time and effort you would invest using the standard FileMaker tool set. (Though, to be fair, it took me a while to get the syntax correct, and I wouldn’t have attempted it had I not seen this example in the JSONata documentation.)

Here’s a simpler example with one of my favorite features: the ability to extract values corresponding to a key regardless of that key’s location within the JSON.

You can also pass the result back to the calling script like so:

If you want to experiment with JSONata, you will find it helpful to play in the above-mentioned Exerciser. Here I worked out the syntax for another example which you can find in the demo file — in this case generating statistics for sample orders.

One neat feature of the Exerciser is the ability to save and share examples, by clicking this icon…

…which sends you through some CAPTCHA contortions, then generates a URL like this: https://try.jsonata.org/wmeljWPN5. If you decide to post a question in the Stack Overflow forum you are more likely to get a response if you include an Exerciser link to your source JSON and the code that isn’t working.

Note that JSONata maintains the original order of keys in an object…

…but FileMaker will alpha sort by key if you wrap the result in JSONFormatElements.

Continuing with the same sample data, we have 3,282 orders dated from 1 Jan 2020 through 31 May 2021, and note that the dates are in ISO 8601, a.k.a. “SQL date”, format. This comes in handy for our next trick, which is to aggregate sales by year and month, producing a JSON object with one key per YYYY-MM like so:

(I got this technique from this presentation given by Andrew Coleman at the London Node User Group.)

At this point we’ve produced two different “reports” based on the same data using JSONata. Can we combine them? Yes we can.

Or perhaps we should aggregate sales by customer? That is easily accomplished.

By now you get the general idea. There are a number of other interesting examples in the demo file so I encourage you to dive in and explore.

Closing Thoughts

I’m still in the early stages of my learning curve with JSONata. Clearly it is a flexible and extremely powerful tool. Using it to solve challenges like the ones in the demo, I’ve sometimes found the syntax to be intuitive, and other times, quite the contrary. Common sense suggests it will become less opaque if I continue working with it.

One drawback, compared with FileMaker, is that JSONata doesn’t have a large community of helpful people answering questions. So far, my track record getting questions answered on Stack Overflow is mixed, which has somewhat dampened my enthusiasm in the weeks since the podcast was recorded. For me, at any rate, it remains to be seen whether this will become a tool I use regularly.

Finally, thank you Jeremy Brown for building the demo, as well as the opportunity to discuss and collaborate.