Using JSONPath to Filter JSON data

In this article, we are going to discuss how to use JSONPath expressions in the Data Selector to query and filter JSON data returned from the web service. For a complete overview of the JSONPath syntax please refer to JSONPath - XPath for JSON.

Bind to Inner Objects

Very often the JSON data returned from a web service contains a single parent object that wraps child objects and/or arrays. If the parent object is used directly the report will not display any detail records. Therefore it is more convenient to return an array of the child objects or a particular nested array using a JSONPath expression.

Example

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

JSONPath

$.store.book

Result

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

Filter Arrays

In other scenarios, it might be useful to filter the JSON data to display only objects matching specific criteria.

Example

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

JSONPath

$.store.book[?(@.price<10)]

Result

[
    {
        "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95
    }
]

When testing for equality use == and for inequality !=.