Using JSONPath to Filter JSON data

In this article, we discuss using JSONPath expressions in the Data Selector to query and filter JSON data returned from the web service. For a complete overview of the JSONPath filter 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

• JSON

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

• JSON

[
    {
        "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 Filter Arrays

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

Example

• JSON

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

• JSON

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

When testing for equality in JSONPath filter, use == for equality and != for inequality.