New to Rows?
Rows is the spreadsheet with superpowers, where anyone can build automation and internal tools without code. It has 40+ built-in data integrations with the tools you use everyday, and a powerful sharing experience to instantly turn any spreadsheet into a web app, a form, or a dashboard.
✏️Sign up for free.
Intro
If you're interacting with a lof of {data}, you'll need to work with JSON a lot.
In this lesson, 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']")
Practice
Let’s use Rows’ on demand =SEARCH_COMPANIES function to practice some JsonPath.
Enter the following formula into cell A1 of a spreadsheet:
=SEARCH_COMPANIES(">50","Germany","Internet")
Now if we open the Data Explorer, we’ll see that we get a whole list of results - perfect. That’s just what we want!
See if you can retrieve the right information using JsonPath:
Fundamental Practice 1
How would you retrieve the seventh result from the list of results?
📬 Answer:
=PARSE(A1, "[6]")
Fundamental Practice 1
How would you retrieve the 'name' of the fourth result from the list of results?
📬 Answer:
=PARSE(A1,"[3].['name']")
Fundamental Practice 3
How would you get the entries between the second (inclusive) and fifth (exclusive)?
📬 Answer:
=PARSE(A1,"[1:5]")
Fundamental Challenge 1
How would you get all the names for entries between the second (inclusive) and fifth (inclusive)?
📬 Answer:
=PARSE(A1,"[1:5].['name']")
Fundamental Challenge 2
How would you get only the names, websites, and descriptions from all the results?
📬 Answer:
=PARSE(A1,"[*].['name', 'website', 'description']")
Not so bad, right? Well, let's complicate a few things by adding filtering to the mix.
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'] != 'a_string')] | All items with the specified child_item is not 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 item results where the child_item is greater than 10 and another_child_item is equal to a_string.
Practice
Filter Challenge 1
How would you get only the company's where the founding date is later than 2018?
📬 Answer:
=PARSE(A1,"[*].[?(@.['year'] > '2018-01-01')]")
Filter Challenge 1
How would you get only companies that have a following of 200 or more?
📬 Answer:
=PARSE(A1,"[*].[?(@.['followers'] > '200')]")
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.
Practice
Try out the following examples:
Filtering and RegEx Practice 1
How would you get all the companies based in Berlin?
📬 Answer:
=PARSE(A2,("[*].[?(@.['location'] == 'Berlin')]"))
Filtering and RegEx Practice 2
How would yet get all companies that have a name similar to gmbh?
📬 Answer:
=PARSE(A2,"[*].[?(@.['name'] =~ /.*gmbh/i)]")
Filtering and RegEx Practice 3
How would you get the name of all the organizations that have a totalFundingUsd greater than 775000?
📬 Answer:
=PARSE(A1,"['organizations'][*].[?(@.['fundingTotalUsd'] > 775000)].['name'])")
Filtering and RegEx Challenge
How would you get only the comapnies who have a .com domain name?
📬 Answer:
=PARSE(A2,"[*].[?(@.['website'] =~ /.*.com/i)]")