Sometimes it would be useful to have a flexible way to access the elements within a JSON object. Something like XPATH for XML objects.
FileMaker itself offers only very limited JSON functions. With JSONGetElement it is possible to query a known path within the JSON object, but no wildcards or even search filters are possible.
This is exactly what the library “JSONPath” offers. It enables an XPATH-like query syntax for JSON objects. A detailed description with examples can be found on Stefan Gössner’s website:
https://goessner.net/articles/JsonPath/
This can be used to realise the following queries, for example:
- $.store.book[*].author -> the authors of all books in the store
- $..book[-1:] -> the last book in order
- $..book[?(@.isbn)] -> filter all books with isbn number
- $..book[?(@.price<10)] -> filter all books cheaper than 10
See more examples and full sample JSON on the project website.
There is a JavaScript implementation of this library which can be downloaded from the Google Code Archive:
https://code.google.com/archive/p/jsonpath/downloads
With the help of the JavaScript functions of the MBS plugin, the JSON Path library can be easily integrated into FileMaker and then called via script command:
https://www.mbsplugins.eu/component_JavaScript.shtml
Note the difference in the functions “JS.CallFunction” and “JS.CallFunctionValues”. While the first function expects the input of parameters in JSON format, the second function works with parameters in text form. If this difference is not observed, the values arrive incorrectly in the JavaScript function and cannot be processed. Instead, one receives an error of the type “invalid json (at offset 1)”.
Once these hurdles have been overcome, the content of a JSON object from FileMaker can be queried with a simple script via MBS using a JSON path expression. You get the result in text form or you can simply convert it back into JSON.
Attached is a FileMaker file with the examples from the JSON Path website.
The JavaScript library is already included in the FileMaker demo file.
The MBS Plugin version 11.3 or newer is required.
Download: FM JSON Path.fmp12 (319 KB)
Update 2023-10-02:
JSON Path has been implemented in the latest version (13.5) of MBS plugin. Please refer to MBS documentation and corresponding blog post:
MBS JSON.Query
JSON Query in FileMaker
