| Components | All | New | MacOS | Windows | Linux | iOS | ||||
| Examples | Mac & Win | Server | Client | Guides | Statistic | FMM | Blog | Deprecated | Old | |
JSON.Query
Performs a JSON Path query.
| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
| JSON | 13.5 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
Parameters
| Parameter | Description | Example |
|---|---|---|
| json | A JSON text or reference. | "{\"people\":[{\"first\":\"Christian\",\"last\":\"Schmitz\",\"city\":\"Nickenich\"}]}" |
| query | The query string. | |
| flags | The flags. |
Result
Returns JSON or error.
Description
Performs a JSON Path query.Evaluates the root value against the JSONPath expression and returns an array of values or normalized path expressions.
Returns a JSON with an array containing either values or normalized path expressions matching the JSONPath expression, or an empty array if there is no match.
To learn more about JSONPath, please check this website:
https://goessner.net/articles/JsonPath/
Learn more about the JSONPath implementation here:
https://danielaparker.github.io/JsonCons.Net/articles/JsonPath/JsonConsJsonPath.html
See also JSON.Replace to replace found values with new values.
Examples
Query input:
let([
json = "{\"test\":123}";
result = MBS("JSON.Query"; json; "$"; 0)
]; result)
Example result:
[
{
"test": 123
}
]
Query with regular expression:
let([
json = "{
\"store\": {
\"book\": [
{
\"category\": \"reference\",
\"author\": \"Nigel Rees\",
\"title\": \"Sayings of the Century\",
\"price\": 8.95
},
{
\"category\": \"fiction\",
\"author\": \"Evelyn Waugh\",
\"title\": \"Sword of Honour\",
\"price\": 12.99
},
{
\"category\": \"fiction\",
\"author\": \"Herman Melville\",
\"title\": \"Moby Dick\",
\"isbn\": \"0-553-21311-3\",
\"price\": 8.99
},
{
\"category\": \"fiction\",
\"author\": \"J. R. R. Tolkien\",
\"title\": \"The Lord of the Rings\",
\"isbn\": \"0-395-19395-8\",
\"price\": 22.99
}
],
\"bicycle\": {
\"color\": \"red\",
\"price\": 19.95
}
}
}";
// All books whose author's name starts with Evelyn
result = MBS("JSON.Query"; json; "$.store.book[?(@.author =~ /Evelyn.*?/)]"; 0)
]; result)
Example result:
[
{
"category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 12.99
}
]
Query all keys:
let([
json = "{
\"store\": {
\"book\": [
{
\"category\": \"reference\",
\"author\": \"Nigel Rees\",
\"title\": \"Sayings of the Century\",
\"price\": 8.95
},
{
\"category\": \"fiction\",
\"author\": \"Evelyn Waugh\",
\"title\": \"Sword of Honour\",
\"price\": 12.99
},
{
\"category\": \"fiction\",
\"author\": \"Herman Melville\",
\"title\": \"Moby Dick\",
\"isbn\": \"0-553-21311-3\",
\"price\": 8.99
},
{
\"category\": \"fiction\",
\"author\": \"J. R. R. Tolkien\",
\"title\": \"The Lord of the Rings\",
\"isbn\": \"0-395-19395-8\",
\"price\": 22.99
}
],
\"bicycle\": {
\"color\": \"red\",
\"price\": 19.95
}
}
}";
// All keys in the second book
result = MBS("JSON.Query"; json; "keys($.store.book[1])[*]"; 0)
]; result)
Example result:
[
"category",
"author",
"title",
"price"
]
Find all books matching a criteria and return list of matching title values:
let([
json = "{
\"store\": {
\"book\": [
{
\"category\": \"reference\",
\"author\": \"Nigel Rees\",
\"title\": \"Sayings of the Century\",
\"price\": 8.95
},
{
\"category\": \"fiction\",
\"author\": \"Evelyn Waugh\",
\"title\": \"Sword of Honour\",
\"price\": 12.99
},
{
\"category\": \"fiction\",
\"author\": \"Herman Melville\",
\"title\": \"Moby Dick\",
\"isbn\": \"0-553-21311-3\",
\"price\": 8.99
},
{
\"category\": \"fiction\",
\"author\": \"J. R. R. Tolkien\",
\"title\": \"The Lord of the Rings\",
\"isbn\": \"0-395-19395-8\",
\"price\": 22.99
}
],
\"bicycle\": {
\"color\": \"red\",
\"price\": 19.95
}
}
}";
// Find all book titles with price > 10
result = MBS("JSON.Query"; json; "$.store[@.book[?(@.price > 10)].title]"; 0)
]; result)
Example result:
[
"Sword of Honour",
"The Lord of the Rings"
]
Find matching entry in JSON Array of Objects:
MBS( "JSON.Query";
"[ {\"test\":\"abc\", \"ID\": 1}, {\"test\":\"Hello\", \"ID\": 2} ]";
"$[?(@.test == 'Hello')]" )
Find matching entry in JSON Array of Objects with two criterias:
MBS( "JSON.Query";
"[ {\"test\":\"abc\", \"ID\": 1}, {\"test\":\"Hello\", \"ID\": 2} ]";
"$[?(@.test == 'Hello' || @.test == 'abc')]" )
Example result:
[
{
"test": "abc",
"ID": 1
},
{
"test": "Hello",
"ID": 2
}
]
See also
Release notes
- Version 13.5
- Added JSON.Query function for JSON Path queries.
Example Databases
Blog Entries
- Functions in JSON Query
- New in MBS FileMaker Plugin 13.5
- Neues MBS Plugin 13.5 für Claris FileMaker
- MBS Plugin 13.5 for Claris FileMaker
- Using JSONPath in FileMaker
- JSON Query in FileMaker
- MBS FileMaker Plugin, version 13.5pr1
Created 17th September 2023, last changed 10th January 2024
