Extracting JSON fields to columns in Snowflake

Use Case/Scenario:

Fivetran connectors deliver data to your 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 warehouse.

Environment:

You are using Snowflake as your cloud warehouse.

Fivetran Recommendation:

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

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:

1. Log in to your Fivetran account.
2. Click Transformations.
3. Click + Transformation.
4. Enter a Name for your transformation.
5. In the SQL Script text box, add the following lines:

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

6. Choose your Schedule Type: New Data or Time Scheduled.
7. Click Save and Run.

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