Parsing data manually

Learn how to parse and manipulate JSON data manually.

Intro

So in our Beginner course, we spoke about getting data using our Recommended Rows and Tables. But, sometimes you want to get some more bespoke data - and that, my friends, is what we’ll be covering in this article.

In this lesson, we’ll get you accustomed with:

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

Ready? Let's go 🚀!

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-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.

kb-data-explorer-advanced

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!

Check it out!

kb-parsing-one-value-newUI

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.

JSON

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:

kb-int-json-explanation-newUI

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

kb-int-json-data-types-newUI

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.

kb-int-json-objects-newUI

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.

kb-int-json-arrays-newUI

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.

kb-int-json-arrays-and-objects-newUI

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.

We're almost done with the theory - we just have to learn one more thing: how to count again 🤯.

Counting things in JSON

The first item of any object or array is actually zero. Why? Well, this comes from programming, and we just have to accept it.

kb-int-json-counting-newUI

As you can see, as soon as we get into another object or array, the count starts again from zero.

Now, why did you need to know all of this? Well, you're going to be working with JSON a lot - either by creating it yourself or when you want to extract values from data you get back from an integration function.

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:

kb-jsonpath-example

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

kb-jsonpath-example-annotated (1)

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:

kb-jsonpath-example-annotated (1)

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:

kb-parsing-asterisk-newUI

The answer to everything

The asterisk, *, is an operator that means “everything”. So in this case, what we’re saying is: Go into every item in organizations and get me the value of name.

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 🤩.

kb-int-app-multiple-values

Check-in

OK, this was a lot, we know. But don’t worry, as long as you know these general ideas, you’re golden:

What is a key and a value?

The key is the name that has some information stored in it. The information stored in it is called the value.

Where do we begin counting in JSON: 0 or 1?

You always start from 0.

What is JsonPath?

It’s a language that we use to navigate and retrieve data from JSON.

Up next

If you answered those questions easily, you probably want to know now why you needed to know all of this. And that’s why next up, we’ll talk about inserting data automatically from JSON responses by yourself!