docs > parsing json data

Using JSON Data

JSON files are used for a variety of applications and databases, and can be confusing when you see them in a spreadsheet since there will be more information in a cell than usual. Sourcetable gives you a formula (PARSE) to use the data received from JSON files in your spreadsheets to analyze more information.

What is JSON?

JSON stands for javascript object notation and it is a lightweight data structure and format used in databases like MongoDB or when data is received from an API call.

sales-customer table sales-customer table with JSONimg_title_goes_here

It consists of data objects in key value pairs, and arrays. In the example above the sales customer column data is a json object in the spreadsheet. The json object is found in curly braces {"gender":"M","satisfaction":3,"age":49,"email":"jegkeol@tuzib.tl"} with the keys on the left side of the ellipses and the value on the right side. The object has the following properties: gender, satisfaction, age and email. The object can be broken down further into key value pairs such as, "gender":"M", which is a key value pair causing them to be linked in the database. JSON objects can have many data types:

  1. Number - either an integer or floating point number

  2. String - a sequence of unicode characters delimited with double quotation marks

  3. Boolean - true or false values “isActive”:TRUE

  4. Null: an empty value, using the word null

  5. Array - using a square bracket notation[] with comma separated parts it is an ordered list with as little as zero parts:

    “customers”:[ {"gender":"M","satisfaction":3,"age":49,"email":"jegkeol@tuzib.tl"}, {"gender":"M","satisfaction":3,"age":28,"email":"dagu@es.fm"}, {"gender":"F","satisfaction":1,"age":50,"email":"da@sahwo.co"} ]

  6. Object - a set of key-value pairs with the keys being strings. Keys do not have to be unique and objects are delimited with curly brackets:

    { "gender":"M","satisfaction":3, "Age":49, "email":"jegkeol@tuzib.tl" }

Why do I need to parse json data?

Parsing JSON objects is necessary to analyze the data they contain. If you would like to analyze the parts of a JSON object then you will need to parse the data and separate it into its own columns rather than leave the data in the JSON format. For example if you are trying to count the number of customer email addresses that you have but your data is in the JSON format above then you will need to separate(parse) the data to create the sum calculation since the computer can only access that JSON as a single string.


How do I parse json data?

Parsing json data is easy in Sourcetable. All that you need is the PARSE function and the key you want to display. Below you can see the JSON object on the left under the Sales column and the data for email in being separated and displayed in the email column with the formula =PARSE($A2, “[‘email’]”) and structured referencing can be used in conjunction with the PARSE formula. The parse function is at its most basic, =PARSE(cell or structured reference of JSON object, “[‘key name’]”).

img_alt_goes_here

Parsing the JSON object using structured Referencing with a data table named sales in the column named customers you would have this formula, =PARSE(sales[customer],"['gender']").

Conclusion

JSON files look confusing at first but once you understand their structure you can use the Parse formula to analyze them in Sourcetable. Simply use cell references or structured referencing along with the key names that you want to separate out like in this example: =PARSE($A2, “[‘email’]”). Now you can analyze data contained in your JSON data.

Drop CSV