Share

Writing JSONPath Expressions

JSONPath is a query language for JSON, similar to XPath for XML. It allows you to select and extract data from a JSON document. You use a JSONPath expression to traverse the path to an element in the JSON structure. You start at the root node or element, represented by $, and reach the required element in the JSON structure to extract data from it. You can use either the dot-notation or the bracket-notation to form the expressions.


Prerequisites


Forming JSONPath Expressions

A JSONPath expression begins with the dollar sign ($), which represents the root element. An expression is formed by following this up with a sequence of child elements, separated by a period (.), called the dot notation or square brackets (['...']) called the bracket notation.

Suppose, you want to extract the agent name from the following JSON document:

{
  "event": {
    "agency": "MI6",
    "data": {
      "name": "James Bond",
      "id": "007"
    }
  }
}

In this example, the root node is event, and the child nodes are agency and data. The agent name is found in the child element, name, of the node, data.

Thus, the JSONPath expression to extract the agent name is:

  • In dot-notation:

    $.event.data.name
    
  • In bracket-notation:

    $['event']['data']['name']
    

The response returns a string that contains the agent name.

Fetch Agent Name


JSONPath Expressions

Hevo supports the following JSONPath expressions to retrieve data from a JSON document:

Note: Hevo only supports JSONPath expressions that return an array or an object.

Expression Description
$ The root node or element.
* Wildcard. Selects all elements in an object or array.
For example, address.* returns all properties of the address object, and book[*] returns all items of the book array.
..<name> A recursive scan. Selects and returns a list of all the properties of the element that matches <name>. The element referenced must be a JSON object.
For example, consider the following JSON structure:

Example -  Recursive scan

The expression $..data returns a list of all the properties of the object, data.
.<name> The dot-notated child element of the current node.
For example, $.event
['<name>', '<name>',...] The bracket-notated child or children of the current node.
For example, the expression $['event']['type', 'data'] returns all the properties of the child elements, type and data, of the node, event.

Note: This notation is useful if the property name contains special characters, such as spaces, or begins with a character other than A..Za..z_. For example, $['personal info']
[n] The n-th element of an array. Indices start from 0.
[<index1>, <index2>,...] The array elements specified by the given indices. The result is a list of elements.
[start:end]
[start:]
The array elements from the start index up to, but not including, the end index. If end is omitted, selects all elements from start until the end of the array. The result is a list of elements.
[:n] The first n elements of the array. The result is a list of elements.
[-n:] The last n elements of the array. The result is a list of elements.
[?(<expression>)] A filter expression. The expression must return a boolean value. The result is a list of all the elements in an object or array that match the specified filter.
@ The current node or element being processed in the filter expression.

Filters

Filters are logical expressions that you can use to sieve information from arrays. A filter expression is enclosed in square brackets and returns a boolean value.

The following is an example of a JSONPath expression with a filter:

$.person.[?(@.age > 18)]

where @ represents the current array item or object being processed. The expression returns a list of people who are above 18 years of age.

You must enclose string values within single or double quotes. For example, [?(@.color == 'blue')] or [?(@.color == "blue")].

You can create more complex filters with the logical operators && and ||.

Hevo supports the following operators in filters:

Operator Description
== Equal to.
For example, [?(@.color == ‘blue')] matches elements whose color is blue.

Note: In the number to string comparison, 1 is not equal to ‘1’.
!= Not equal to.
For example, [?(@.color != 'red')] matches elements whose color is not red.
< Less than.
For example, [?(@.days < 30)] matches elements in which the number of days is less than 30.
<= Less than or equal to.
For example, [?(@.days <= 30)] matches elements in which the number of days is less than or equal to 30.
> Greater than.
For example, [?(@.days > 30)] matches elements in which the number of days is greater than 30.
>= Greater than.
For example, [?(@.days >= 30)] matches elements in which the number of days is greater than or equal to 30.
=~ Matches a regular expression.
For example, [?(@.firstName =~ /vi.*?/i)] matches elements whose firstName starts with vi (case-insensitive).
! Negates a filter.
For example, [?(!@.isbn)] matches elements that do not have the isbn property.
&& Logical AND. Used to combine multiple filter expressions where all the conditions must be met.
For example, [?(@.isbn && @.price < 10)] matches elements that have an ISBN, and whose price is less than 10.
|| Logical OR. Used to combine multiple filter expressions where any of the conditions may be met.
For example, [?(@.category == 'fiction' || @.price < 10)] matches elements whose category is fiction or whose price is less than 10.
in Checks if the value on the left exists in the set on the right.
For example, [?(@.size in ['S', ‘M'])] matches elements whose size is either ‘S’ or ‘M’.

Note: String comparison is case-sensitive.
nin Checks if the value on the left is not in the set on the right.
For example, [?(@.size nin ['S', ‘M'])] matches elements whose size is neither ‘S’ nor ‘M’.

Note: String comparison is case-sensitive.
size Checks if the size of the array or the string matches the specified value.
For example, [?(@.name size 12)] matches all elements whose name length is 12, including spaces.
empty true Matches an empty array or string.
For example, [?(@.isbn empty true)] matches all elements whose isbn property has an empty value.
empty false Matches a non-empty array or string.
For example, [?(@.isbn empty false)] matches all elements whose isbn property has a non-empty value.

JSONPath Expression Examples

Note: To evaluate the JSONPath expression give it as the Data Root to the GET method when configuring REST API as a Source during Pipeline creation. You can retrieve and view the API response by clicking GET RESPONSE in the Test the REST API panel. Read Troubleshooting the REST API for more information.

Let us use the following JSON document to form sample JSONPath expressions:

{
  "event": {
    "name": "Bond Movies",
    "movies": [
      {
        "name": "Licence to Kill",
        "star": "Timothy Dalton",
        "rating": 6.6
      },
      {
        "name": "GoldenEye",
        "star": "Pierce Brosnan",
        "rating": 7.2
      },
      {
        "name": "Tomorrow Never Dies",
        "star": "Pierce Brosnan",
        "rating": 6.5
      },
      {
        "name": "Skyfall",
        "star": "Daniel Craig",
        "rating": 7.8
      }
    ]
  }
}

You can query for the details of each movie in the document using the following JSONPath expression:

$.event.movies

The response is a list of all the movies from the document.

Movie list

Similarly, you can filter the movies with a rating greater than 7 using the following expression:

$.event.movies[?(@.rating > 7)]

The response returns only those movies whose rating is more than 7.

Movie rating more than 7

You can form more queries, based on the level of granularity you need, using the expressions in the following table:

Note: This is not a comprehensive list. You can form expressions to suit your requirement using the supported JSONPath syntax and filters.

JSONPath Expression Meaning
$ Entire object.
$.event Event object.
$.event.movies[0] First movie.
$['event']['movies'][0] First movie.
$.event.movies[0,2] First three movies.
$.event.movies[:2] First two movies.
$.event.movies[-2:] Last two movies.
$.event.movies[?(@.rating > 7)] All movies with a rating greater than 7.
$.event.movies[?(@.star == "Pierce Brosnan")] All movies whose star is Pierce Brosnan.

Hevo uses Jayway JSONPath to evaluate JSONPath expressions. Read JSONPath for the complete reference.


See Also

Last updated on Jul 16, 2024

Tell us what went wrong

Skip to the section