JavaScript Object Notation or JSON for short is a text-based data format following JavaScript object syntax. This is commonly used for transmitting data in web applications.
During training, in one of our projects, we had to extract data about LAPD using API in Alteryx and then put it in snowflake to extract the data from the JSON file and create a table that you can then use in Tableau.
As we had some years' worth of data to work with, the result of the API gave us nearly 3000 rows of data in JSON form, but this is how a single entry of JSON file looks like:
This is how it would look like when you click on one of the entries:
To get this, we would have to parse the JSON file and create a table of the JSON entries. The SQL code would look like this:
And then we would like to store these in a table for further use, so the code would look like this:
This would give the JSON entries that we saw above.
The next step would be to create a table that would be useful to use in Tableau. For this, we have to write a code that trims that JSON entry into the pieces that you would like to use for analysis and also in what type of data, this piece of trimmed information, would be. This is how the code looks like:
This process is called Lateral Flattening. As the array is flattened, each of the objects from the name and the value pair will be separated accordingly to the code you decide to give for that specific value.
This, in result, will create a table of all of your separated values. Then, your table is now ready to be used in Tableau for further analysis.
One of the observations that we have learned in that project is that extracting data from huge data sets in Snowflake is useful as it can do the job a lot faster than Alteryx. So sometimes it is much easier to put it the JSON file in Snowflake. However, the downside is that you have to write all of the columns and their data types yourself the way you want to receive the data which might be a bit tedious. But in this case, you can only select certain information and not extract it all.
Hope this helps!