Snowflake - How to flatten JSON file

by Liu Zhang
This is HIVERY trademark tagline. Data Has A Better Idea. Visit www.hivery.com to learn more about who we are.
Photo by Franki Chamaki / Unsplash

JSON file type is a common type choice for data shared online, given it's compactness, it is preferred format of access API data. The data are saved in a particular structure as a string.

Rules:

  • <NAME>:<VALUE> Data is in name/value pairs. A name/value pair consists of a field name (in double quotes), followed by a colon, followed by a value: "firstName":"John"
  • Data is separated by commas
  • Objects are enclosed in curly braces ({})Objects can contain multiple name/values pairs: {"firstName":"John", "lastName":"Doe"}
  • Arrays are enclosed in square brackets ([])

An array can contain multiple objects:

"employees":[
   {"firstName":"John", "lastName":"Doe"},
   {"firstName":"Anna", "lastName":"Smith"},
   {"firstName":"Peter", "lastName":"Jones"}
]


During the Data School Snowflake project, we are asked to download London TFL bike rental data through an API from Alteryx connection, then load directly to a Snowflake server and transform within Snowflake environment. The aim is to demonstrate the transition from Extract, Transform, Load (ETL) to ELT mode.

To start we have a single file to work with

Single JSON entry
Detailed view (after parsing)

To understand the structure better, let's view the code within a code editor.

Alternative view

The first thing we need to notice is the external '[]' on the whole entry which represents an array that is needed to be flattened, then there is another '[]' for additionalProperties, i.e. a nested array.

Note: children and childrenUrls are empty, so we ignore them in the analysis

To flatten the JSON in Snowflake, there is a function - LATERAL FLATTEN (office page). Though the office page doesn't give the best example, let's demonstrate in this post with particular examples.

Sample code

As shown in the first picture, we have a table with a single column BikePoint_JSON from Project_BikePoint_Data table. The function lateral flatten is called on the column that contains the JSON file (need a common in between). Once the array is flattened, then each objects within are name and value pair (as shown in the sample code at the start of this blog) will be displaced as separate rows. Most of the time, we are only interested in the value of the output, so we can call .value:, then the required columns.

This example has 782 pairs of objects

We notice the first column still contain another array which we need to flatten as well, this is a nested structure, but the lateral flatten function is easy to use where we can just repeatedly applying it.

Inner array with 9 objects

We can setup a similar structure as the above code, with additional name for results from inner array where we called t1 here. To get the results from the inner array can be called with .value on t1 instead, while results from the outer array is called with .value on t0.

Sample code
Now we have 7038 rows in the results

Since each objects (782) from the outer array has 9 objects each, the final results has 7038 rows, so flatten effectively created an appended table.

Now you can just add addtional calls to get any of the required columns from the JSON file in Snowflake in a query, rather than some complicated RegEx code.


Looking for more guides, tips and tricks in Tableau or Alteryx? Go check out the other blog posts from the Data School.

Tableau Public

LinkedIn

Twitter