This document is to share a FileMaker custom function called: JSONQuery.
Originally this function was named "JSONGetElementWhere", but PeterDoern suggested using JSONQuery as the name, and I really liked that idea. (Thanks Peter!)
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 JSONQuery
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 JSONQuery sprouted, as I realized that not all JSON child matching use cases require iterating through every single child object in order to find all the matches. JSONQuery 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 are 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( result.path )" to obtain a count of matched values:
Using "SUM( result.path )" to return a sum of matched numeric values:
Using "LIST( result.path )" to return a return-delimited list of values:
Using "LIST_DISTINCT( result.path )" to return a return-delimited list of unique values:
Using "LIST_DISTINCT( result.path ; CodePoint )" to return a custom-delimited list of values:
|Date||Archive Identifier||Notes||CF Code Modified ?|
|29 Dec 2018||CF_JSONQuery_20181229_1245_R||Initial upload||---|
|30 Dec 2018||CF_JSONQuery_20181230_1005_R|
Corrected a bug in the test-running code of the sample file:
A calculation in TestCases table incorrectly flagged certain tests of LIST aggregate as passing.
Thanks go to jrenfrew for identifying and reporting this issue.
|30 Dec 2018||CF_JSONQuery_20181230_1151_R|
Slight improvement to TestCases UI:
Appropriately hides test result status when test has not yet been run.
|02 Jan 2019||CF_JSONQuery_20190102_1105_R|
Changes to tidy up sample file:
• Corrected documentation on initial screen.**
• Corrected awkward wording in notes for example #36.
• Removed unused Value Lists.
• Removed unused field and unused auto-enter calculation.
**Documentation had incorrectly indicated that IN clause values could be delimited by LF chars.
Current Archive: CF_JSONQuery_20190102_1105_R.fmp12.zip