JSON Path explained
In Elements Connect for Jira Cloud, two query and formatting tools are available:
JSONPath: to extract data from JSON responses.
FreeMarker: to transform, combine, and render query results.
This document explains how to use JSONPath in the context of Connected items and Custom fields configured via REST API datasources, in the “Data in API response” field:
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
}Data in API response | Transformed Json | Note |
|---|---|---|
$.store.book[*].author | The authors of all books
CODE
| To use this information as a custom field value we should use only |
$..author | All authors
CODE
| To use this information as a custom field value we should use only |
$.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
CODE
| To use this information as a custom field value we should use only |
$..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 | |
$.store.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 |
$..* | Give me every thing |
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"
]
}
]
}Data in API response | Transformed Json | Note |
|---|---|---|
$.screens[?(@.id =="en-RP-100")].cameraType[*] | Get all camera types for element en-RP-100 filtered by id
CODE
| Since jayway returns an array when using filter, we should extract the results with operator [*]. To use this information as a custom field value we should use only |
$.screens[0].cameraType | Get all camera types for element en-RP-100 directly accessed by index
CODE
| Using index returns an element, we don't need to extract it. To use this information as a custom field value we should use only |
$.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. To use this information as a custom field value we should use only |
$.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 | To use this information as a custom field value we should use only |
$..productionPlaces[*][*] | Get all production places > USA, Europe, Asia, Studio, Apartment, Office, Europe, Studio, Building | To use this information as a custom field value we should use only |
Additional ressources
https://www.rfc-editor.org/rfc/rfc9535.html