What is JSONPath and How to query JSON using JSONPath?

JSONPath and Query JSON using JSONPath

One of the most important advantage of JSON is that it is a light weight format that can be used to interchange data between computers and processes. JSON , like XML, is a format to provide structure to the data. If you are not familiar with JSON, please go through this tutorial first of Introduction to JSON.

In this tutorial, we will learn more about JSONPath and Query JSON using JSONPath. We will be covering the following topic:

What is JSONPath?
How to Query JSON with JSONPath?
Different strategies to query JSON object and JSON Array.

What is JSONPath?

Every JSON object is composed on an inherent hierarchy and structure. Every JSON ends up creating a tree of nodes, where each node is a JSON Element. Let us take an example here, below is a simple JSON expressing a collection of countries

{
  "Description": "Map containing Country, Capital, Currency, and some States of that Country",
  "Region": "Asia",
  "Countries": [
    {
      "Country": "India",
      "Data": {
        "Capital": "New Delhi",
        "mintemp": 6,
        "maxtemp": 45,
        "Currency": "Rupee"
      }
    },
    {
      "Country": "Nepal",
      "Data": {
        "Capital": "Katmandu",
        "mintemp": 9,
        "maxtemp": 23,
        "Currency": "Nepalese rupee"
      }
    }
  ]
}

At the top most level we have a Root node, which is basically the node containing all of the current JSON. Inside this root node, we have following nodes

Description
Region
Countries

Description and Region are simple leaf nodes in the tree. But Countries is a non-leaf node, which further contains more nodes. Here Countries node contains an array of two countries. If we were to simply define a hierarchical relation between the Root node and any node in the JSON we can do like shown below.

Note: Let us represent Root node by $ and a relationship from parent to child with >>

Description node will be represented by $ >> Description.

Region node will be represented by $ >> Region.

Similarly, we can also define a relationship between the Root node and the zero'th item in the Countries array. Relationship will be $ >> Countries[0] where [ ] is the index operator to represent an item at n index in an Array of Countries.

In a way this hierarchy in JSON allows us to create a standard mechanism to traverse through specific parts of the JSON. A standard way to do this is called JSONPath.

How to Query JSON using JSONPath?

JSONPath creates a uniform standard and syntax to define different parts of a JSON document. JSONPath defines expressions to traverse through a JSON document to reach to a subset of the JSON. This topic is best understood by seeing it in action. We have created a web page which can help you evaluate a JSONPath. Use this page to practice writing JSONPath. This is the link to JSONPath evaluator.

Get Root node operator in JsonPath

Root node operator in JSON is represented by a $ sign. $ will return all the nodes inside the JSON document. To try this out open the JSONPath evaluator page and type $ in the JSONPath field. As shown in the image below

JSONPath and Query JSON using JSONPath

Get Children operator in JSONPath

In order to get children of a given node, we can use the Dot (.) operator or the ['childname'] operator. In order to get all the Countries we can have JSONPath as

$.Countries
$['Countries']

Output will be

[
  [
    {
      "Country": "India",
      "Data": {
        "Capital": "New Delhi",
        "mintemp": 6,
        "maxtemp": 45,
        "Currency": "Rupee"
      }
    },
    {
      "Country": "Nepal",
      "Data": {
        "Capital": "Katmandu",
        "mintemp": 9,
        "maxtemp": 23,
        "Currency": "Nepalese rupee"
      }
    }
  ]
]

Wildcard operator in JSONPath

Wild card operator in JSONPath is (Star or Asterisk) symbol. This is literally means everything under that node. For example, if you want to display the Data nodes of all the countries you can simple write following JSONPat

$.Countries[].Data*
$['Countries'][].Data*

This will display the Data nodes of all the countries

[
  {
    "Capital": "New Delhi",
    "mintemp": 6,
    "maxtemp": 45,
    "Currency": "Rupee"
  },
  {
    "Capital": "Katmandu",
    "mintemp": 9,
    "maxtemp": 23,
    "Currency": "Nepalese rupee"
  }
]

Array Index operator in JSONPath

Sometimes it is required to access a particular entry at a given index in the JSON array. We can use the Array Index [i,j,k... ] to identify an entry at a particular index. In the above example, let us find out the last Country entry in the Countries array.

$.Countries[-1]
$['Countries'][-1]

Here -1 stands for the last item in the Array. You can also refer to the last item by giving a positive value to the index. For e.g.

$.Countries[1]
$['Countries'][1]

Output will look like this

[
  {
    "Country": "Nepal",
    "Data": {
      "Capital": "Katmandu",
      "mintemp": 9,
      "maxtemp": 23,
      "Currency": "Nepalese rupee"
    }
  }
]

Note: Array index starts from 0. Hence to refer to the second item in the array we have to use 1 as the index.

Array index is just not limited to displaying only 1 item. We can extract multiple items from the array at different indexes. The syntax to do so is [i,j,k..]. For e.g. to extract the first 2 array items we will write the JSONPath as

$.Countries[0,1]
$['Countries'][0,1]

Output will be

[
  {
    "Country": "India",
    "Data": {
      "Capital": "New Delhi",
      "mintemp": 6,
      "maxtemp": 45,
      "Currency": "Rupee"
    }
  },
  {
    "Country": "Nepal",
    "Data": {
      "Capital": "Katmandu",
      "mintemp": 9,
      "maxtemp": 23,
      "Currency": "Nepalese rupee"
    }
  }
]

For the JsonPath shown below, we will change our Json to have more nodes. Let us take a look at a Json representing collections of books. Here is the Json, from now on use this Json in the JsonPath Evaluator

{
  "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
}

Array slice operator in JsonPath

Array slice operator is wonderful operator to extract selected items from Json. Taking the example of books, what if we want to retrieve every alternative book in the Json. To do that we will need the Array, Slice operator. Syntax of Array Slice operator is [StartIndex : EndIndex : Steps]. Let find out what are books at odd number in the books collection. JsonPath will look like

$..book[1,4,2]
$..['book'][1,4,2]

the output of the above JsonPath will be

[
  {
    "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
  }
]