JSONQuery 2.0 is Here

Kevin Frank

1 year ago

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!