Filtering JSON responses with JSONPath

How to use advanced JSONPath to get and filter for specific data points.

If you're interacting with a lof of {data}, you'll need to work with JSON a lot.

Here, we’ll take a more in-depth look at JsonPath, specifically how to use filtering and regular expressions to find just the right information inside {data}.


As we stated in Parsing data manually, JsonPath is a very useful tool for extracting specific information from JSON responses. We’re going to make a little practical lesson here - so have a look at the table below to see some example combinations of JsonPath.

['item'][*]All items.
['item'][0]The first item.
['item'][2]The third item.
['item'][1,4]The second and fifth items.
['item'][-2]The second last item.
['item'][:-2]Everything but the last two items.
['item'][:2]The first two items. The way that this works is that by writing [:2] is the same as writing from 0 (including that item) to 2 (excluding that item)
['item'][1:4]The second to fourth items.
['item'][-2:]The last two item (inclusive).
['item'][2:]From the third item to the end (inclusive)
['item'][*].['child_item']Get child_item (for example, a value) for all the entries.
['item'][*].['child_item', 'another_child_item']Get child_item and another_child_item for all the entries.

JsonPath and PARSE()

At Rows, we always use JsonPath in combination with our PARSE() function:

=PARSE("JSON data", "['item'][*].['child_item']")


Filtering allows you to quickly parse through the JSON and only return the data that matches certain criteria. It looks a little like this:


JsonPath FilterYou get...
['item'][?(@.['child_item'])]All items with the specified child_item.
['item'][?(@.['child_item'] == 'a_string')]All items with the specified child_item is equal to a_string.
['item'][?(@.['child_item'] > 10)]All items where child_item is greater than 10.
['item'].[?(@.['child_item_date'] > '2018-01-01')]All items where child_item_date is greater than 2018-01-01.
['item'].[?(@.['child_item'] > 10)].['another_child_item']The another_child_item where an items child_item is greater than 10.
['item'].[?(@.['child_item'])].['another_child_item']All another_child_items that have an item that the specified child_item.

Single quotation marks

To use dates or strings, they need to be enclosed in single quotes ' '.

You can also combine filtering expressions using && (AND) and || (OR) logical expressions:

['item'].[?(@.['child_item'] > 10 && @.['another_child_item] == 'a_string')]

The example above will return all items where the child_item is greater than 10 and another_child_item is equal to a_string.

Regular expressions

Regular expressions (RegEx) are quite complex, however, a quite “normal” use case is when you want to get companies that might have a different spelling now and then.

JsonPathYou get...
['item'][?(@.['child_item'] =~ /.*nike/i)]All items where child_item has nike in its name. The /i indicates that the case is not important. As such, you will get any variation of nike: Nike, nike, NiKe, NIke, nikE, and so on.

Regular expressions can get quite advanced, for example:

['item'][?(@.['child_item'] =~ /.*.asTerC..d/i)]

This would match any item that has a child_item that kind of looks like Mastercard, but it could also match blastercurd, plastercard, 5asterC33d, and so on. If you’d like to know more about regular expressions, check out RegExr.