This document is to share a FileMaker custom function that I was working on last month called: JSONGetElementWhere.
The function is designed to quickly return matching top-level child elements from large JSON arrays/dictionaries without requiring any external dependencies such as plug-ins or services. To use this function, one specifies a TargetPath within the supplied JSON, a ComparisonValue, and an Operator parameter, which collectively specify the criteria that determines which child elements in the source JSON are considered "matches". An additional parameter, ResultPath, allows for tailoring which portion of a matched child object should be included in the result output.
Before going further, I'd like to acknowledge that this type of function is nothing new:
This is just my particular take on implementing this sort of JSON query/filter function -- something I did for enjoyment, which grew out of an interest in a Community post which I will mention below. There are already established and fine collections of JSON Custom Functions which, either individually or combined, offer the same kind of functionality described here.
Two such collections which I found and very much like include:
If this document is of interest to you, then I also suggest taking a look at the above collections, if you have not already done so.
Origin of JSONGetElementWhere
Earlier this year, JaredHague posted this discussion in the Community. I was really taken with Jared's innovative approach to reorganizing JSON into a structure that worked more efficiently for his use case. I think that what he did was inspired/brilliant. Eventually, when time permitted, I studied different ways to generalize Jared's technique to see if it could be used to streamline seek access in a more arbitrary JSON structure. It did prove to be possible to generalize the technique, but this endeavor led me to study a variety of alternatives for querying JSON, so that I could have a sense of what the special aspects were for each technique, and where each technique shined.
It was during this more general study that the seeds for JSONGetElementWhere sprouted, as I realized that not all JSON child matching use cases require iterating through each child object in order to perform a match. JSONGetElementWhere thus became a study concerning which sort of matching and filtering cases could be performed with fewer function iterations, and what sort of impact this would have on performance. The use case where this function really shines is that of looking for a "needle in a haystack", as the function's execution time is determined much less by size of the JSON input, and much more by the count of matches which is being returned.
The trade-off here is speed versus complexity. IMO, designs which iterate through each child object are far superior when it comes to code simplicity. There's no question about that in my mind. The task presently at hand (and possibly on hold) will be to see if I can provide a thorough enough test suite for this custom function to make it worth using.
I have just finished putting together a demo file. Once I have had a chance to define and process some test cases, I intend to post it here on the community.
In the meantime, I have recorded a rough video tour of the function:
Thanks, as always, for taking a look.
Screenshots from the demo file:
Using the "EQUALS" operator, to perform case-insensitive string matches:
Using the "LIKE" operator, to perform case-insensitive "Begins-With" style matches:
Using the "IN" operator, to perform matching against multiple possible comparison strings:
Using "COUNT( return.path )" to obtain a count of matched values:
Using "SUM( return.path )" to return a sum of matched numeric values: