How to Parse JSON Objects

Goal

Working with JSON objects can be a tedious, overwhelming and time consuming task. Datameer can help you to easily parse JSON objects with powerful dedicated functions specific for this task. Our ingest options such as, “collect additional fields” ensures that your data can flexibly expand and contract with little to no maintenances of your models.

When new elements or key/value pairs are added, they can be parsed out on demand without reforming your work.

 

Learn

Let's use Citibike_NYC to give an example into exploring a JSON object. Citibike is a bike share system in New York City that shares their data with the public. We will use this data to demonstrate the JSON functions in Datameer. If you’d like to download the data, follow the steps below.

Create a connection

Open Datameer and start creating a new connection.

For the connection type, choose Custom Protocol (including http/https).

Click Next.

On the Connection Details page, type http://feeds.citibikenyc.com/stations for the Base URL.

You can leave the User and Password fields blank. Click Next.

Click Save.

Save the connection with the name CitiBike_NYC.

Create an import job

Select Data from the drop-down menu and select Import Job.

Click Select Connection button and choose CitiBike_NYC that you just created.

Select JSON as the File Type. Then click Next.

In the File field, type /stations.json

Then click Next.

Your Define Fields page will look like this.

Click Next.

Click Next on the Schedule page.

Click Save.

Save your new import job as CitiBike_Stations.

 

Put your JSON data into a workbook

Select Analytics from the drop down menu and select Workbook.

 

Choose the import job you just created CitiBike_Stations and then click Add Data.

 

You might be surprised that the cell appears blank where your CitiBike data should appear. This is due to the JSON string being so large that it can not fit in the cell. If you hover your mouse cursor over the field, it becomes clear that there is a massive amount of data there.

Let's try and make it a little easier to read and work with.

 

Create a new sheet by clicking the + symbol at the bottom of the page. The Formula Builder opens.

Select the function JSON_ELEMENTS

Use the column labeled stationBeanList from your data source sheet as the JSON Array.

Then click OK.

As viewed in Fx bar JSON_ELEMENTS(#CitiBike_Stations!stationBeanList)

Your JSON is now parsed by its elements. This makes it much easier to read by human eyes.

 

Now we can pull out details from the JSON.

Click another column in the sheet to open the Formula Builder.

Select the function JSON_VALUE

Use the parsed JSON column (stationBeanList) you just created on this sheet as your JSON Object.

Type in stationName into the JSON Key field. Then click OK.

As viewed in Fx bar JSON_VALUE(#stationBeanList;"stationName")

The name of the stations is extracted from the JSON and displayed in your new column.

Let's also check for how many bicycle docks are located at each of these stations.

Click a new column to open the Formula Builder and choose JSON_VALUE.

Use the stationBeanList column again as your JSON Object. 

Type in totalDocks into the JSON Key field. Then click OK.

As viewed in Fx bar JSON_VALUE(#stationBeanList;"totalDocks")

Now you will have pulled the total number of bicycle docks that each station contains.

 

  

We hope this guide to working with the JSON file type has been helpful. Learn more about importing and using JSONs from our video tutorials.  Video 1: Importing JSON Data Video 2: JSON Array Analytics

 

“Got a question? Have an answer? Join the Datameer Community!”