Example of Transforming JSON Data in Snowflake

Use Case

You have nested JSON data Snowflake that you need to parse out so that the data may be used for analytics. You are looking for an example of how this works to help you get started before manipulating your production data.

Environment

Snowflake

Recommendation

Fivetran does not automatically unpack nested JSON objects to separate columns or tables in the destination. Fivetran recommends using Snowflake’s native FLATTEN function to parse out the values you are looking for into separate columns. You will need to follow this process to Flatten and Query JSON Data.

First try it out this example data to get familiar with how it works:

1. Create a sample of JSON data in Snowflake:

CREATE OR REPLACE SCHEMA TEST;
CREATE OR REPLACE TABLE TEST.JSON_TEST AS
SELECT 
parse_json(column1) AS FT
FROM VALUES
('{more:
{test:{
"ID": 2,
"name": "Fivetran",
"function": "Data Pipeline As a Service",
"code": {
"abbr": "5T",
"st": 
{
stattrib:"1"
}
}
}
}
}
')
as raw_json;

2. Study the raw output:
   

   SELECT FT:more::STRING as moreFROM TEST.JSON_TEST;
   

3. Manipulate the above select SQL code to flatten out the JSON completely:

SELECT 
    FT:more.test.ID::INTEGER as ID, 
    FT:more.test.name::STRING as Name, 
    FT:more.test.function::STRING as "Function",
    FT:more.test.code.abbr::STRING as codeAbbr, 
    FT:more.test.code.st.stattrib::STRING as codeStStattrib
FROM 
    JSON_TEST;

Considerations

JSON does not always come up in a fixed format and keys change all the time, which can make it hard to know what keys exist. To understand your JSON structure, you can use a flatten function:

select 
upper(regexp_replace(f.path, '\\[[0-9]+\\]', '[]')) as path 
, typeof(f.value) as type
, count(*) as record_count
from 
TEST.JSON_TEST
, lateral flatten(parse_json(FT), recursive=>true) f 
group by 1, 2 
order by 1, 2;