Flattening JSON in Snowflake
Snowflake · Views, Pipelines & Automation
Theory
JSON loaded into Snowflake usually sits in a VARIANT column, where nested arrays are still packed inside a single value. FLATTEN is a table function that takes an array or object and returns one row per element, with columns such as INDEX (array position), KEY (object key), VALUE (the element) and PATH. It is used with LATERAL, so each source row is flattened on its own and keeps its other columns.
For example, an event row with items ["A1", "B2"] becomes two rows: one for A1 and one for B2, each carrying the event_id. Rows whose array is empty or missing are dropped unless you add OUTER => TRUE.
Why we use it
Arrays in JSON cannot be filtered, joined or aggregated as rows until they are expanded. FLATTEN turns nested data into normal relational rows inside Snowflake, so no outside JSON processing step is needed.
When to use it
Use FLATTEN whenever you need one row per array element, such as order items or tags. For a single field, plain path notation (col:field) is enough. Use OUTER => TRUE when parent rows without elements must be kept.
Storing Semi-Structured Data
Semi-structured JSON is stored in a single VARIANT column, letting a table hold irregular or nested data alongside regular relational columns.
What FLATTEN Does
The FLATTEN table function unpacks a nested array or object inside that VARIANT column into one relational row per element, so you can SELECT and filter it like ordinary columns.
Pairing With LATERAL
Typically combined with LATERAL so each source row's JSON is flattened independently, avoiding any separate JSON pre-processing step outside Snowflake.
How it works
- Row with a VARIANT column holding an array
- LATERAL FLATTEN(input => col:items) reads that row's array
- Does the array have elements? Yes: Emit one row per element with INDEX and VALUE. No: Drop the row unless OUTER => TRUE.
- Select parent columns plus f.value fields, cast with ::
- Filter, join or aggregate the flattened rows
Example
CREATE OR REPLACE TABLE raw_events (event_id INT AUTOINCREMENT, event_data VARIANT);
INSERT INTO raw_events (event_data)
SELECT PARSE_JSON(column1) FROM VALUES
('{"user_id": 101, "action": "purchase", "items": [{"sku":"A1","price":999}], "metadata": {"country":"India"}}');
SELECT event_data:user_id::INT AS user_id,
event_data:metadata.country::STRING AS country
FROM raw_events;
SELECT event_data:user_id::INT AS user_id,
f.value:sku::STRING AS sku, f.value:price::FLOAT AS price
FROM raw_events, LATERAL FLATTEN(input => event_data:items) f;Key points
- Works on arrays and objects inside VARIANT, OBJECT or ARRAY values
- LATERAL keeps each source row's columns alongside its elements
- Output columns include SEQ, KEY, PATH, INDEX, VALUE and THIS
- Empty or missing arrays drop the row unless OUTER => TRUE
- Cast values with ::STRING or ::NUMBER for clean output