JSON Path explained
JSONPath expressions always refer to a JSON structure in the same way as XPath expression are used in combination with an XML document.
The root element in JSONPath is always referred to as $ regardless if it is an object or array.
Licence
Elements Connect relies on the excellent Jayway JsonPath JAVA library (sponsored by Jayway), many thanks to them for their amazing work!
JSONPath library documentation is available under Apache 2 license.
Operators
Operator | Description |
|---|---|
$ | The root element to query. This starts all path expressions. |
@ | The current node being processed by a filter predicate. |
* | Wildcard. Available anywhere a name or numeric are required. |
.. | Deep scan. Available anywhere a name is required. |
.<name> | Dot-notated child |
['<name>' (, '<name>')] | Bracket-notated child or children |
[<number> (, <number>)] | Array index or indexes |
[start:end] | Array slice operator |
[?(<expression>)] | Filter expression. Expression must evaluate to a boolean value. |
Functions
Functions can be invoked at the tail end of a path in a root element - the input to a function is the output of the path expression. The function output is dictated by the function itself.
Function | Description | Output |
|---|---|---|
min() | Provides the min value of an array of numbers | Double |
max() | Provides the max value of an array of numbers | Double |
avg() | Provides the average value of an array of numbers | Double |
stddev() | Provides the standard deviation value of an array of numbers | Double |
length() | Provides the length of an array | Integer |
Filter operators
Filters are logical expressions used to filter arrays. A typical filter would be [?(@.age > 18)] where @ represents the current item being processed.
More complex filters can be created with logical operators && and ||.
String literals must be enclosed by single or double quotes ([?(@.color == 'blue')] or [?(@.color == "blue")]).
Example 1
Given the 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
}
},
"expensive": 10
}Root element | Result | Note |
|---|---|---|
$.store.book[*].author | The authors of all books | To use this information as a custom field value we should use $ sign in a JSONPath column. |
$..author | All authors | To use this information as a custom field value we should use $ operator in a JSONPath column. |
$.store.* | All things | First returned result is a list and a second result is a object. You should be more precise. |
$.store..price | The price of everything | To use this information as a custom field value we should use $ operator in a JSONPath column. |
$..book[2] | The third book | |
$..book[0] | The first book | |
$..book[:2] | All books from index 0 (inclusive) until index 2 (exclusive) | |
$..book[1:2] | All books from index 1 (inclusive) until index 2 (exclusive) | |
$..book[-2:] | Last two books | |
$..book[2:] | Book number two from tail | |
$..book[?(@.isbn)] | All books with an ISBN number | |
$.store.book[?(@.price < 10)] | All books in store cheaper than 10 | |
$..book[?(@.price <= $['expensive'])] | All books in store that are not "expensive" | |
$..book[?(@.author =~ /.*REES/i)] | All books matching regex (ignore case) | |
$..book[?(@.author=="Herman Melville")] | All books which are written by Herman Melville | |
$..book[?(@.title contains 'Lord')] | A book whose title contains the word "Lord" | This example is case-sensitive |
$..book[?(@.title =~ /Lord.*/i)] | A book whose title contains the word "Lord" | This example is case-insensitive |
$..* | Give me every thing | |
$..book.length() | The number of books | To use this information as a custom field value we should use $ operator in a JSONPath column. |
Example 2: working with arrays
Given the json
{
"elapsedMilliseconds": 90,
"count": 125,
"screens": [
{
"id": "en-RP-100",
"title": "Portret van zanger en cabaretier Aristide Bruant",
"productionPlaces": [
[
"USA",
"Europe",
"Asia"
],
[
"Studio",
"Apartment",
"Office"
]
],
"showImage": true,
"cameraType": [
"Instant camera",
"Multiplane camera",
"Press camera"
]
},
{
"id": "en-RP-200",
"title": "Henri de Toulouse-Lautrec",
"productionPlaces": [
[
"Europe"
],
[
"Studio",
"Building"
]
],
"showImage": true,
"cameraType": [
"Multiplane camera"
]
}
]
}Root element | JSONPath in column | Result | Note |
|---|---|---|---|
$.screens.[?(@.id =="en-RP-100")].cameraType[*] | $ | Get all camera types for element en-RP-100 filtered by id > Instant camera, Multiplane camera, Press camera | Since jayway returns an array when using filter, we should extract the results with operator [*]. |
$.screens.[0].cameraType | $ | Get all camera types for element en-RP-100 directly accessed by index > Instant camera, Multiplane camera, Press camera | Using index returns an element, we don't need to extract it. |
$.screens.[?(@.id =="en-RP-100")].productionPlaces[*][*] | $ | Get all production places for element en-RP-100 filtered by id > USA, Europe, Asia, Studio, Apartment, Office | Since jayway returns an array when using filter, and we have two arrays inside the production Places array, we should extract the results two times. |
$.screens.[?(@.id =="en-RP-100")].productionPlaces[1][*] | $ | Get only 2nd array of production places for element en-RP-100 filtered by id > Studio, Apartment, Office | |
$..productionPlaces[*][*] | $ | Get all production places > USA, Europe, Asia, Studio, Apartment, Office, Europe, Studio, Building |