How to Work With JSON Data in Snowflake?

Use case

You have JSON data in Snowflake that you need to parse for analytics. This could be:

• Nested JSON objects that need to be flattened into separate columns
• JSON arrays that need to be unpacked into individual rows
• Data from Fivetran connections delivered in JSON format

Environment

Destination: Snowflake

Recommendation

Fivetran does not automatically unpack nested JSON objects into separate columns or tables in Snowflake. Instead, we recommend using Snowflake native functions, such as FLATTEN, to parse and transform JSON data based on your analytics needs. For more information, see our documentation.

Example 1: Flattening JSON arrays

In this example, the Fivetran Webhooks connector syncs JSON data into a column named DATA.

The JSON structure looks like this:

{
  "id": 67575,
  "users": [
    {
      "email": "john.smith@fivetran.com",
      "name": "John Smith"
    },
    {
      "email": "jane.doe@fivetran.com",
      "name": "Jane Doe"
    }
  ]
}

To extract each user into its own row with separate NAME and EMAIL columns, create a view using Snowflake tasks that flattens the users array. For example:

USE SCHEMA webhooks;
CREATE OR REPLACE VIEW users_unpacked AS (
  SELECT
    _ID,
    _INDEX,
    _CREATED,
    EVENT,
    value:name::STRING as name,
    value:email::STRING as email,
    _FIVETRAN_SYNCED
  FROM
    "ED"."WEBHOOKS"."USERS",
    LATERAL FLATTEN(input => data:users)
);

After the transformation runs, the view contains one row per user with extracted name and email values.

Example 2: Flattening nested JSON objects

Before applying transformations to production data, you can experiment with sample JSON to understand the structure.

1. Run the following script in Snowflake to create sample JSON data:

   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. Run the following command and analyze the output:

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

3. Run the following command to flatten the JSON:

   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;
   

Discover JSON structure

JSON data often does not follow a fixed schema, which can make it difficult to know which keys are present.

To understand the structure of your JSON data, use the 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(FT, recursive=>true) f
GROUP BY 1, 2
ORDER BY 1, 2;

This query returns:

• All JSON paths found in the data
• The data type for each path
• The number of records in which each path appears

Use this output to understand your JSON structure before writing transformation queries.