JsonPath Fundamentals

Overview

JsonPath is a convenient standard that allows you to navigate through a JSON structure to retrieve the data that you need. You might not know it, but you’ve been using JsonPath every single time you’ve opened the data explorer, copied a JSON property you wanted, and pasted it into a cell. The PARSE() formula you paste into a cell contains JsonPath (have a look below if you don’t believe us).

Parsing data with JsonPath in dashdash

You can use JsonPath for a variety of reasons, but one of the most popular is with our INSERT_DATA, OVERWRITE_DATA, and UPDATE_DATA functions. After you go through this article, you’ll have a much better understanding of how to get the data you want from a JSON response to use in these functions.

In this article, we’ll cover:

  • JsonPath fundamentals
  • JsonPath filters and regular expressions

JsonPath fundamentals

To navigate a JSON object with JsonPath, you need to know a little about how JSON is structured. Let’s have a look at this very simple example:

And now, say that we want to get the value dashdash from our first item, then we just need to use:

json-path-example)

And that’s pretty much it! The JsonPath expression will return exactly what we want: dashdash.

There’s plenty of combinations and tricks with JsonPath to get to data that you want. Just have a look at a few below

JsonPath You get…
['item'][*] All item’s.
['item'][0] The first ‘item’
['item'][2] The third ‘item’
['item'][1,4] The second and fifth 'item’s
['item'][-2] The second last ‘item’
['item'][:-2] Everything but the last two 'item’s
['item'][:2] The first two 'item’s. 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 'item’s.
['item'][-2:] The last two 'item’s (inclusive)
['item'][2:] From the third ‘item’ to the end (inclusive).
['item'][*].['child_item'] Get child_item for all the entries
['item'][*].['child_item', 'another_child_item'] Get child_item and another_child_item for all the entries

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

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

Practice

Let’s use dashdash’s 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!

Using the information from the table in JsonPath Fundamentals, go through the practice questions below and see if you can retrieve the right information using JsonPath:

Fundamental Practice 1

How would you retrieve the first three organizations?
:mailbox_with_mail: Answer:

=PARSE(A1,"['organizations'][:3]")

Fundamental Practice 2

How would you get the last two organizations?
:mailbox_with_mail: Answer:

=PARSE(A1,"['organizations'][-2:]")

Fundamental Practice 3

How would you get the entries between the second (inclusive) and fifth (exclusive)?
:mailbox_with_mail: 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)?
:mailbox_with_mail: Answer:

=PARSE(A1,"['organizations'][1:5].['name']")

Fundamental Challenge 2

How would you get all the names, domains, and fundingTotalUsds for all the organizations?
:mailbox_with_mail: Answer:

=PARSE(A1,"['organizations'][*].['name', 'domain', 'fundingTotalUsd']")

JsonPath: filtering and regular expressions

You can further define the data by using JsonPath’s inbuilt filtering functionality as well as its support for regular expressions.

Filtering

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

jsonpath-filtering-example

JsonPath filter You get…
['item'][?(@.['child_item'])] All 'item’s with the specified child_item.
['item'][?(@.['child_item'] == 'a_string')] All 'item’s with the specified child_item is equal to a_string.
:memo: Note: To use dates or strings, they need to be enclosed in single quotes ' '.
['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.
:memo: Note: To use dates or strings, they need to be enclosed in single quotes ' '.
['item'].[?(@.['child_item'] > 10)].['another_child_item'] The another_child_item where an item's 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'.

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 filter with regular expression You get…
['item'][?(@.['child_item'] =~ /.*nike/i)] All items where child_item has nike in its name.
:memo: Note: 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. Or, if you’d like an article more aimed at your use cases, leave a comment on this article with some suggestions where you’d think regex would be useful in your dashdash apps :slight_smile:.

Practice

Let’s continue with dashdash’s Crunchbase integration to practice filtering and regular expressions with JsonPath. In case you’ve deleted the formula we used before, here it is again (paste it into cell A1 of a spreadsheet):

=SEARCH_COMPANIES_ADVANCED_CRUNCHBASE(">50","Germany","Internet")

Filtering and RegEx Practice 1

How would you get all the organizations based in Hamburg?
:mailbox_with_mail: 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?
:mailbox_with_mail: 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?
:mailbox_with_mail: 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?
:mailbox_with_mail: 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?
:mailbox_with_mail: Answer:

=PARSE($A$1,"['organizations'][*].[?(@.['city'] == 'Frankfurt' && @.['lastFundingOn'] > '2017-01-01')].['domain']")

Challenge

Right, now that you’ve learned how to use JsonPath with PARSE(), it’s time to add a little challenge into the mix. Using the same Crunchbase formula, create a few customized tables using dashdash’s UPDATE_DATA() function! When you’re done, post a screenshot of your fancy table, along with a short description, in the comments.

Summary

There you go! If you managed to get through all those practice exercises, you’ve already gained a very useful skill which you can easily apply in dashdash :wink: Now it’s just time get building :rocket:!

3 Likes