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

  1. Row with a VARIANT column holding an array
  2. LATERAL FLATTEN(input => col:items) reads that row's array
  3. Does the array have elements? Yes: Emit one row per element with INDEX and VALUE. No: Drop the row unless OUTER => TRUE.
  4. Select parent columns plus f.value fields, cast with ::
  5. 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

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic