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

• An understanding of JSON, JSONPath, and JSONPath expressions.

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.

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: 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:

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.

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.

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.

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

See Also

• JSONPath online evaluator
• JSONPath specification
• JSONPath