Extracting JSON fields to columns in Snowflake

Use Case/Scenario:

Several Fivetran connectors deliver data to your warehouse in JSON format, but only give the option to unpack one layer of columns.

You want to flatten JSON fields, below the first layer, into their own columns in Snowflake. 

Environment:

Your cloud warehouse is Snowflake.

Fivetran Recommendation:

In this example, the Fivetran webhooks connector delivers JSON data to a column named data in your warehouse:

The JSON data is:

{
  "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 you can set up a transformation to flatten the JSON into a view.

1. Open Fivetran
2. Click Transformations
3. Click + Transformation
4. Provide a Name
5. In the SQL Script 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. Select New Data or Time Scheduled
7. Click Save and Run

The result in the newly created view in Snowflake is a row per user with the email and name in their own columns: