A couple of weeks ago I was assigned the role of project manager for one of our client projects. Part of the project requirements meant that we had to retrieve lot’s of data stored in API’s. Often when you retrieve data from an API it comes out in a JSON file format. This is a common file type for data shared online given its compactness, but this structure is very difficult for us (and Tableau) to read therefore we need to find a way to restructure it. This is where the lateral flatten comes in.
What is a lateral flatten?
A lateral flatten is a technique for flattening a nested data structure (like a JSON file) into a single, flat list. To give you an example, imagine we have the following list with multiple items, but some of these items have multiple items within them. This is a nested list:
Performing a lateral flatten on this list would bring every item onto the same level and produce the following:
Performing a lateral flatten in SQL
In this example I’m using TFL’s bike point data from an API. Currently, the data looks like this:
To bring everything in this JSON file onto the same level we’re going to use the LATERAL FLATTEN table function like. This will transform our data structure into something like this:
In this example, we want to flatten our object keys into separate columns so that we have a table structure we can work with. To do this we’re going to create a new table with each key (i.e. the column name) in a separate column and each value is cast to a data type that is appropriate for the value. For example, for distance we would want to use a numerical data type therefore to make this into a column I would write “VALUE”:”distance”::double AS DISTANCE.
You may have noticed that ADDITIONAL_PROPERITES data type was set to an array, this is because we have data nested within data. To produce a separate table for these key value pairs I’ll perform a similar query to last time:
Finally, I want to pivot my additional properties table so that string in my KEY column has its own column, using VALUE to provide the values for these columns like so:
With only a few lines of SQL queries, we have transformed our API from a messy nested structure in our JSON format, to 2 structured tables that can be downloaded and run in Tableau for us to perform analysis on.
