Parsing JSON

In this article, we’ll cover:

• Using the Data Explorer
• JSON
• Parsing JSON with JsonPath

Using the Data Explorer

In our spreadsheet, click Find to make sure that we have a {companies} in cell E2. Then:

1. Click on the Data Explorer icon.
2. Select the All data tab.
3. Click on the triangle next to organizations, you’ll see a list of numbers pop up.
4. Click the triangle next to 0, you’ll get details about a company.

Pretty cool right? How about we try and get some of that data into our spreadsheet?

Manually getting data into a spreadsheet

To get a single value from a response in the Data Explorer, just hover over a value, click Copy, and paste it into a cell. Easy!

OK, but what was all that information in the All data tab? Why was it in a weird format?
Well, the data that you get back from integration functions is in a format called JSON. And if you really want to make the most out of Rows, you definitely need to know a bit about the JSON.

How JSON data is represented in Data Explorer

When we send information across the web, we send it as JSON - and all that is, is some text in key-value pairs. It's just like with a dictionary - the word you look up is the key, and the definition is the value. It looks like this:

And you can send pretty much any kind of data you can think of:

Objects

JSON objects are like chapters in a dictionary, while nested objects are each individual entry in the dictionary. If you have a look at the example below, you’ll see that we have some objects and that address has a nested object with more data.

Arrays

JSON arrays let you store more than one value in a key that you can access individually. If you look at the example below, you’ll see that we have two keys (offices and employees) that “technically” have more than one value stored.

However, for computers, offices only has one value - it’s just one string, "Berlin, Porto". If you wanted to get the first city (Berlin), you can’t - you’d always get the entire string.

Arrays let the computer know that there are separate values stored. In other words, a computer understands that "Jeremy" and "Patricia" are separate people in employees. And thanks to this, we can actually get each item (employee) in that array (employees).

Arrays of objects

Arrays can store the same data as a JSON object, which includes... other objects!

Why? Well, say you have a list of companies in an array (Apple and Rows), and for each of those companies, you want to store similar information (like the domain and address). In that case, it makes sense that you have an array, and for each company in that array, you’d have an object that has all that information. Have a look at the example below, and things will get a little clearer.

If we have another look at the data in E2 again, you'll see that companies is an array, and each individual company in that array are objects.

Parsing JSON

OK, let’s get back to some more practical things. If we look at the formula in the cell where we pasted in the value from the Data Explorer, you’ll see a formula like this:

Rows has a cool function called PARSE() that looks through returned JSON and extracts the value that you want. How it works is simple:

The first parameter is pretty easy to understand now, right? It’s just the cell or formula that’ll return JSON data. But, what the hell is JsonPath!? Don’t be scared - just read on and it’ll all make sense 😉.

JsonPath

JsonPath is a language that we use to navigate JSON and extract information. Let’s have a look at the example from before and analyze what’s going on:

Now, in the cell where you copied in the formula, try changing the 0 to 1. You should magically see that the value changes in that cell. Pretty cool right?

What about if instead of 1, you put in *? You get a {data} cell again, right? Let’s click in and see what happened:

And lastly, what about if we replace ‘name’ with ‘name’, ‘domain’? You’ll get an array of objects just with the name and domain! In the next lesson, you’ll see why all that comes in handy 🤩.