Intro
OK, you know how to create your own JSON as well as modify existing JSON to fit your needs. How about we turn our attention to now going through that JSON and filter for what we want to get. And if you’ve been following us since the intermediate course, you already know what we’re talking about: JsonPath.
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.
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’ Crunchbase integration to practice some JsonPath.
Enter the following formula into cell A1 of a spreadsheet:
=SEARCH_COMPANIES_ADVANCED_CRUNCHBASE(">50","Germany","Internet")
Now if we open the Data Explorer, we’ll see that we get a whole list of organizations - 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 first three organizations?
📬 Answer:
=PARSE(A1,"['organizations'][:3]") :
Fundamental Practice 2
How would you get the last two organizations?
📬 Answer:
=PARSE(A1,"['organizations'][-2:]")
Fundamental Practice 3
How would you get the entries between the second (inclusive) and fifth (exclusive)?
📬 Answer:
=PARSE(A1,"['organizations'][1:5]")
Fundamental Challenge 1
How would you get all the names for entries between the second (inclusive) and fifth (inclusive)?
📬 Answer:
=PARSE(A1,"['organizations'][1:5].['name']")
Fundamental Challenge 2
How would you get all the names, domains, and fundingTotalUsds for all the organizations?
📬 Answer:
=PARSE(A1,"['organizations'][*].['name', 'domain', 'fundingTotalUsd']")
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'] > 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.
Practice
Try out the following examples:
Filtering and RegEx Practice 1
How would you get all the organizations based in Hamburg?
📬 Answer:
=PARSE($A$1,"['organizations'][*].[?(@.['city'] == 'Hamburg')]")
Filtering and RegEx Practice 2
How would yet get all organizations that have a name similar to either qype or Qype?
📬 Answer:
=PARSE($A$1,"['organizations'][*].[?(@.['name'] =~ /.*qype/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 1
How would you get the name, domain, and totalFundingUsd of all the organizations that have a totalFundingUsd greater than 775000?
📬 Answer:
=PARSE(A1,"['organizations'][*].[?(@.['fundingTotalUsd'] > 775000)].['name', 'domain', 'fundingTotalUsd'])")
Filtering and RegEx Challenge 2
How would you get the the domain of all the organizations based in Frankfurt where their lastFundingOn date was after 2017-01-01?
📬 Answer:
=PARSE($A$1,"['organizations'][*].[?(@.['city'] == 'Frankfurt' && @.['lastFundingOn'] > '2017-01-01')].['domain']")
Up next
Right! JsonPath - pretty cool right? The more you use it, the more you’ll get used to how to work with it. After a while, you’ll be wondering how much more difficult life would be without it!
To finish this course off, let’s have a look at how to work with multiple accounts for the same integration connected to your account - a surprisingly common situation for a lot of us.
So, let’s get on it with it 🚀!