The JSON-PATH directive uses a DSL for reading JSON records.
Syntax
Code Block |
---|
json-path :source :destination 'json-path' |
<source-column>
specifies the column in the record that should be considered as the "root member object" or "$"<destination-column>
is the name of the output column in the record where the results of the expression will be stored'json-path'
is a JSON path expression; see Usage Notes below for details
Usage Notes
An expression always refers to a JSON structure in the same way that an XPath expression is used in combination with an XML document. The "root member object" is always referred to as $
regardless if it is an object or an array.
Notation
Expressions can use either the "dot–notation":
Code Block |
---|
$.name.first |
or the "bracket–notation":
Code Block |
---|
$['name']['first'] |
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 |
| Dot-notated child |
| Bracket-notated child or children |
| Array index or indexes |
| Array slice operator |
| Filter expression; must evaluate to a boolean value |
Functions
Functions can be invoked at the tail end of a path: the input to a function is the output of the path expression. The function output is dictated by the function itself.
Function | Returns | Output |
---|---|---|
| The min value of an array of numbers | Double |
| The max value of an array of numbers | Double |
| The average value of an array of numbers | Double |
| The standard deviation value of an array of numbers | Double |
| The length of an array | Integer |
Filter Operators
Filters are logical expressions used to filter arrays. A typical filter would be:
Code Block |
---|
[?(@.age>18)] |
where @
represents the current item being processed.
More complex filters can be created with the logical operators
&&
and||
String literals must be enclosed by either single or double quotes, such as in
[?(@.color=='blue')]
or[?(@.color=="blue")]
Filter Operator | Description |
---|---|
| Left is equal in type and value to right (note |
| Left is not equal to right |
| Left is less than right |
| Left is less than or equal to right |
| Left is greater than right |
| Left is greater than or equal to right |
| Left matches regular expression |
| Left exists in right |
| Left does not exist in right |
| Size of left (array or string) matches right |
| Left (array or string) is empty |
Examples
Using this record as an example:
Code Block |
---|
{ "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 } |
JSON Path (click link to test) | Result |
---|---|
The authors of all books | |
All authors | |
All things, both books and bicycles | |
The price of everything | |
The third book | |
The first two books | |
All books from index 0 (inclusive) until index 2 (exclusive) | |
All books from index 1 (inclusive) until index 2 (exclusive) | |
Last two books | |
Book number two from tail | |
All books with an ISBN number | |
All books with an ISBN number | |
All books in store cheaper than 10 | |
All books in store that are not "expensive" | |
All books matching a regex (ignore case) | |
All books | |
The number of books |