This is a quick “odds and ends” post to share the current iteration of my JSONQuery sandbox file. Some of the examples are new; some are from presentations I gave earlier this year. Most of them illustrate one or more things you can do with this amazing custom function (a few of the examples don’t deal with JQ directly, but play a supporting role). The aim is to convey a tiny measure of the depth and breadth of what you can accomplish with JSONQuery.
Resources:
- JSONQuery v2 — https://github.com/steve-ssh/FMP_JSONQuery
- All FileMaker Hacks JSONQuery-related articles — https://filemakerhacks.com/tag/jsonquery
- Sandbox file — jq-sandbox-december-2024.zip
The Sandbox file requires FM 19.6 or later (but a few of the examples use features introduced in FM 21).
A bit of info in case this is the first time you’ve heard of JSONQuery. It is a custom function which operates on JSON, created and maintained by Steve Senft-Herrera.
Typically you’re going to point it at an array of objects…
…or an array of arrays…
…and it will return child elements matching your query parameters.
The file contains 58 examples of the former, and 30 examples of the latter, for a total of 88, each of which should be fairly self-explanatory. Aside from basic retrieval of query results, there are also examples showing aggregation (sum, average, etc.) and transformation (restructuring of JSON).
When I first interviewed Steve about JQ v1 in the Fall of 2021, he observed that…
…anything you can do with this function, you could instead do by writing your own While loop to cruise through all of the entries in your JSON array and find the correct ones.
Part of what I think this custom function offers as an advantage over writing your own While loop is A. it saves you the time of writing and debugging that routine on your own, and B. it has sort of a little bit of special sauce that that will operate faster than just iterating through every single element until you find all of your matches. It will perform more efficiently than that approach.
I’ve certainly found this to be the case, and often will embed JQ statements within While statements, as per this rather extreme real-world example.
Here I’m calling While three times, but would have had to call While an additional four more times without JQ in my tool kit. [Note: the examples in the Sandbox file are considerably less convoluted than this.]
Given that each example in the Sandbox file includes an explanation, there really isn’t much else to say, except thank you to Steve Senft-Herrera for making this brilliant and useful tool available to the FileMaker community.
If you have any questions, or if I haven’t made something clear in one of the examples, feel free to post a comment here.