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}.
JsonPath
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.
| JsonPath | Result |
|---|---|
['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
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 Filter | You 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
' '.
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.
| JsonPath | You 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.