JsonPath

JsonPath is an expression language used to select nodes from a JSON document tree contained in a native JSON message. Its basic ideas follow XPath used to select nodes from a DOM tree contained in a structured Orchestra message.

Introduction

The following examples work on the following JSON document:

{
"store": {
 "name": "Schneider books & magazines",
 "address": {
  "street": "Downtown",
  "city": "Paddington"
 }
},
"book": [
 {
  "category": "reference",
  "author": "Nigel Rees",
  "title": "Sayings of the Century",
  "price": 8.95
 },
 {
  "category": "fiction",
  "author": "Herman Melville",
  "title": "Moby Dick",
  "isbn": "0-553-21311-3",
  "price": 8.99,
  "first published": ["Harper & Brothers", "New York", "1851"]
 },
 {
  "category": "fiction",
  "author": "J.R.R. Tolkien",
  "title": "The Lord of the Rings",
  "isbn": "0-395-19395-8",
  "price": 22.99,
  "first published": ["George Allen & Unwin", "London", "1954"]
 }
],
"periodical": [
 {
  "category": "newspaper",
  "title": "The Times",
  "price": 2.70,
  "currency": "GBP"
 },
 {    
  "category": "magazine",
  "title": "Der Spiegel",
  "price": 4.60,
  "currency": "EUR"
 }
 ]
}

We assume that it is stored in the message variable MSG.

Similar to Orchestras XPath implementation a JsonPath at the global level always starts with a reference to a message variable. Every variable reference starts with a dollar sign immediately followed by the name of the variable. After that initial variable reference a sequence of path steps follows.

Selecting Object properties

The most simple step is a reference to a property. So if you want to extract the name of the bookstore you write

$MSG.store.name

In the Orchestra process model you can assign this expression to a variable of type string.

Selecting Array items

To access the items of an array you use the familiar bracket notion. So to access the first book in the array named “book” you write

$MSG.book[0]

Actually this returns a JSON object. If you want to assign such a complex value to a variable in the Orchestra process model the variable must have the type message. You may retrieve the name of the first book using the expression

$MSG.book[0].name

Selecting all Array items

In most cases, if you have a collection of objects you may want to iterate over all the items. All items are reference by the so called wildcard step denoted by [*]. So in a procedural JSON mapping you can write:

for (JsonNode book: $SRC.book[*]) { ... }

Counting the number of Array items

Even if you access the message from a process model such an expression can be sensible, e.g. if you want to retrieve the number of books. In this case you might use the expression

count($MSG.book[*])

This type of function working on the result of a JsonPath invocation is called reduction function because it reduces the set of values returned by a JsonPath to a single value, in your example to an integer value.

Filtering Array items

Instead of accessing a predefined item from an array, you rather want to retrieve one or more items having certain properties. Here is an example:

$MSG.book[.isbn=="0-553-21311-3"]

With this query you retrieve the book where the property isbn has the value “0-553-21311-3”. Actually the expression .isbn is a so called local path. It is structured like any other path but it doesnt start with a variable reference, like a global path because it starts at the current node. In our example the current node is any item of the array referenced by $MSG.book. A path step containing a conditional expression surrounded by brackets is called a filter step. A filter step tests any item of an array if it fulfills the filter condition. The filter step results in the set of items meeting that condition.

Selecting the first item of a result set

In the example above the result of the query will probably return only one book because we can assume that a book is uniquely identified by its ISBN. Generally the application of a JSONPath (a query) can return more than one value. In this case it might be necessary to utilize another reduction function named “first”. E.g. you might apply

first($MSG.book[.author == "Herman Melville"])

Definite and indefinite query results

Assume that the bookstore contains more than one book of “Herman Melville” then the assignment of simply

$MSG.book[.author == "Herman Melville"]

to a variable would result in an error because the result set is not unqiue, we say the result is indefinite. The example above is actually equal to the expression

value($MSG.book[.author == "Herman Melville"])

You might want to explicitely use that reduction function to express that you expect the query to result in a definite result.

Testing for existence

Beside the count, the first and the value functions there is another reduction function named exists. You can use it to check for the existence of a value, e.g.

exists($MSG.book[.category!="fiction"])

This type of expression might be helpful as test expression in a gateway element in the process model. The example also shows the usage of the not equal operator. Apart from == and != there are other operators which we well encounter later in this text.

Of course you can use the exists function also within local paths. E.g. to select allo books having a property isbn you may write

$MSG.book[exists(.isbn)]

You may abbreviate this type of filter condition by simply omitting the function invocation:

$MSG.book[.isbn]

Testing for null values

Sometimes properties exists but explicitly have the value null.

To test for null you must use the literal null in JsonPath.
E.g. you can find all books where the author is set to null

$MSG.book[.author==null]

General index notation

Until now we always worked with property steps of the form .name Sometimes a JSON document contains properties with unsual names containing special characters or spaces. E.g. the property “first published” in our example contains a space character. if you want to retrieve the value of this property for the first book you cannot use the dot nation. Instead you must use the index notation:

$MSG.book[0]["first published"]

As you can see it has the same form as the notation to access an item of a array. If you do not provide an integer value but a string you access a property of an object having the given name.

Using the question mark to clearly mark a filter condition

Sometimes it is difficult to recognize if a path step is a selector step or a filter step. E.g. the following two path expressions look very similar at first glance:

$MSG.book["first published"]
$MSG.book[["first published"]]

The first one selects the property first published of an JSON object book while the second one select all items of a JSON array book having a property first published. To clearly mark a filter step as such the user can add the question mark after the opening bracket, e.g.

$MSG.book[?["first published"]]

Of course you also can write the exists expression fully:

$MSG.book[?exists(["first published"])]

Note that the question mark is syntactical sugar only, you can safely omit it.

Using variable references as index expressions

If you use JsonPath in a procedural mapping you will probably need to configure your expressions using variables So instead of fixed index values as in the examples above you may use variable reference.

$MSG.book[$index]["first published"]
$MSG.book[$index][$propertyName]

As you can see, you can provide an integer variable containing the index to select an Array item as well as string variable containing the name of a property to select.

Using the wildcard selector to select all properties

Sometimes you don’t know the exact name of a property. E.g. in an OpenApi specification you can find something like

"paths": {
 "/pets": {
  "get": {
   "description": "Returns all pets",
   "responses": { "200": { "description": "A list of pets." } }
  },
 "/pets/{petId}": {
  "get": {
   "description": "Info for a specific pet",
   "responses": { "200": { "description": "Expected response to a valid request." } }
  }
 }
}

To select all paths provided, we can used the wildcard selector:

$OPENAPI.paths.*

This path selects the values of all paths objects.

Using regular expressions to denote variable named properties

If you want to select all paths whose names start with /pets we can use a regular expressions instead of a property name:

$OPENAPI.paths[=~"/pets/.*"]

Using regular expressions as filter conditions

Regular expressions are especially useful if we want to select items where we do not know the exact value of a property. E.g. if you want to select the titles of all books where the name of the author is Rees you can use the following expression:

$MSG.book[.author =~ ".*Rees"].title

Using complex local path expressions

Until now we always used simple local paths containing a single property step. Here is two more complex examples. First we select all books having the property "first published".

$MSG.book[exists(["first published"])]

The second example selects the title of the first book in our list where the publishers name is Harper & Brothers.

$MSG.book[["first published"][0]=="Harper & Brothers"].title

Using relative operator in a filter condition

Besides the operators == and != you may use the relative operators <, <=, > and >= to compare numerical values.

E.g. to select all books having a price lower then 10 you write:

$MSG.book[.price < 10]

In a procedural mapping you might provide the price limit in a variable, e.g.

$MSG.book[.price <= $highestPrice]

Combining conditions using logical operators

If you need to filter for several conditions, you can use the logical operators !, || and &&. As an example we select the titles of all book whose price is between 8.0 and 9.0:

$MSG.book[.price >= 8.0 && .price <= 9.0)].title

Or we can select all books having an isbn a title and a price:

$MSG.book[? exists(.isbn) && exists(.title) && exists(.price)]

In a process model we could use the following expression to check if the message contains any incomplete book record:

exists($MSG.book[? exists(.isbn) && .exists(.title) && .exists(.price)])

Using relative operators in global expressions

You can use relative operators in global expressions too. E.g. you can check if the number of books equals the number of magazines in our bookstore.

count($MSG.book[*]) > count($MSG.periodical[*])

Using deep property search to navigate through hierarchically structured data

Sometimes we have hierachical data structures and want to select all items meeting some conditions from the hierarchy. For this task you can search deep into the document. E.g. to select all titles from our book store we can use the operator ..:

$MSG...title

This simple expression selects all titles of all objects within our book store.

Applying filters recursively

The .. operator is probably more useful together with filters. E.g. to select all objects in the book store having a price < 10 you can use the following query:

$MSG..[.price < 10]

Checking for the existence of a value within an array

A special case is filtering the items of an array containing simple values. Actually this is only sensible if you want to check for the existence of some value. Assume that the items of the array first published can appear in any order. The to select all books first published in London you can use the following query:

$MSG.books[?exists(["first published"][?.=="London"])].title

As you can there is a special local path denoted by a single dot. This denotes the current node. In case of an array containing simple values the current node always references that value node.

To make that more clear int might be helpful to use the value function:

$MSG.books[?exists(["first published"][?value(.)=="London"])].title

This expression is completely equivalent to the first one.