How to Parse JSON Objects With Snowflake

For when you need an easy solution to transform messy data.

Photo by Soraya Irving on Unsplash

Raw data is imperfect. You can’t expect it to be ingested in the same format you wish to use it. If it was that easy, we analytics engineers wouldn’t exist.

I was recently tasked with ingesting some experimentation data directly from our database in the form of CSV files. The data was being sent in the form of a column containing a JSON object. This JSON object held all the key information we needed to evaluate the experiment we were running.

Being able to parse JSON objects is a key advantage of using Snowflake as your chosen data warehouse. Snowflake makes it super easy to do using SQL, rather than having to use another language like Python to parse it. It’s truly a one-stop-shop for data transformation.

So, how does it work?

All you need to do is use a simple function, parse_json() where object is the JSON column.

parse_json(object) 

This then allows you to parse the object similar to how you would using Python.

Let’s say we have a JSON object in the column user_information that looks like the following:

{
   "user":
      {
         "name":"Madison",
         "phone"…