This is a quick post to say that JSONQuery 2.0 is finally here with many new features, and some improvements to existing features as well.
What is JSONQuery?
A FileMaker custom function authored by Steve Senft-Herrera that enables you to query JSON (works with FM 18+).
Basic Things JSONQuery Does
1) Query – Allows for finding elements that match a supplied value, with a variety of options for operators.
2) Aggregate – Allows for returning an aggregate, e.g., SUM, AVG, LIST, etc., of values taken from either the matched elements, or all of the input.
3) Transform – Allows for customization of how the results are returned, with the option to pick-and-choose what attributes are included in the output, and how they are named.
Official Site
https://github.com/steve-ssh/FMP_JSONQuery
(Some of the) New Features in Version 2
- {OMIT}
- PATH
- Single quotes to simplify MAP
- DELETE option for MAP SOURCE
-
# of IN arguments no longer restricted to 20
-
IN operator can match against JSONNumber data type
-
=as a shortcut for EQUALS -
New match operator which allows for matching based on the existence of a node
JSONQuery ( JSON ; "key" ; "PATH_EXISTS" ; "country" ; "" ; "" )
Note that the 5th parameter (data type) can be supplied to further refine the results — it limits the results to a specified data type. -
New match operators which allow for matching based on the existence of a non-trivial value at a node
JSONQuery ( JSON ; "key" ; "IS_EMPTY" ; "" ; "" ; "" ) JSONQuery ( JSON ; "key" ; "IS_NON_EMPTY" ; "" ; "" ; "" )
Good not only for finding things like non-empty strings, and non-null values, but also for distinguishing between values which are empty arrays versus values which are arrays populated with at least one value. Similar for objects. -
New match operator to specifically match values which exist and are not null
JSONQuery ( JSON ; "key" ; "IS_NON_NULL" ; "" ; "" ; "" )
Note that we already had a way to match null values:JSONQuery ( JSON ; "key" ; "EXACT" ; "" ; JSONNull ; "" )
- New “~” path operator returns matched children in context
- When 3rd parameter (OPERATOR) is empty defaults to “MATCH_ALL”
Closing Thoughts
Congratulations to author Steve Senft-Herrera. This is a huge, no, make that HUGE, gift to the FileMaker Community. Thank you Steve!
WOW. Version 1 was amazing. The new features take it to another level. Thanks Kevin for championing this and to Steve for the awesome development of the custom function and comprehensive demo file.
I’m glad you called out Steve’s demo file — it is an encyclopedic and spectacularly well-crafted reference.
…with a bunch of new examples, and, in addition to the standard detail layout, a redesigned one for those with larger monitors.
I hadn’t spotted the new layout, very helpful.
I just formatted the calculation in the data viewer as I was curious. Mapping everything to 2 letter let variables in order to to stay under the character limit and still have room for documentation at the top is inspired, but how much harder that makes it to follow what’s going on further down the calculation. Huge respect to Steve; I can only imagine the time and focus that has gone into crafting the code for JSONQuery.
Paul, are you aware of the JSONQuery Source Code layout in the demo file? You can peruse 109 incarnations of the CF, including of course, the latest version, in unminified splendour.
I wasn’t aware, but will be taking a look. Thanks.
Hi Paul,
Thank you very much for these kind words. It would be honest to say that exchanges that you and I have had, albeit brief and infrequent, contributed to the desire to make JQ as good as possible.
If you ever have any questions about usage, etc., please don’t hesitate to reach out.
All the best & thank you. I hope it serves you very well.