How Can I Extract JSON Fields to Columns?

Use Case

Fivetran connectors deliver data to your Snowflake data warehouse in a JSON format. However, the connectors only allow you to unpack one layer of columns.

You want to flatten the JSON fields below the first layer into their specific columns in your Snowflake data warehouse.

Environment

Snowflake

Recommendation

In this example, Fivetran's Webhooks connector delivers JSON data to a column named DATA in your warehouse:

Raw JSON

The JSON data is structured as:

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

To bring the user’s name and email fields into their own columns, set up a transformation to flatten the JSON into a view using Snowflake tasks:

    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)
    );

Once the transformation executes, it results in a newly created view in Snowflake: a row per user with the NAME and EMAIL columns.

Extracted data