In Snowflake, how do you query a JSON array inside a VARIANT column using LATERAL FLATTEN?

middlemust knowfreq 66%

answer

A colon walks into the document
An uncast value keeps its JSON quotes
A table function turns array elements into rows
Its output column holding the element is VALUE
One option makes it behave like a left join

basics

~10 s

Traverse a Snowflake VARIANT with colon paths and cast the result, then use LATERAL FLATTEN(input => payload:items) to turn each array element into its own row, reading the element from the flattened VALUE column.

solid answer

~40 s

A VARIANT column holds a parsed semi-structured document. You navigate it with the colon operator and dots or brackets — payload:customer.id, payload['customer']['id'], payload:items[0] — and you almost always cast the result, because an uncast path yields a VARIANT that renders a string with its JSON quotes. To expand an array into rows, join the table to the FLATTEN table function laterally: SELECT o.id, f.value:sku::string FROM orders o, LATERAL FLATTEN(input => o.payload:items) f. FLATTEN returns SEQ, KEY, PATH, INDEX, VALUE and THIS; VALUE is the element, INDEX its position in the array, and KEY the field name when you flatten an object. OUTER => TRUE keeps parent rows whose array is empty or missing, which is exactly the behaviour of a left join and the usual fix for silently disappearing orders.

A `VARIANT` column holds a parsed semi-structured document. You navigate it with the colon operator and dots or brackets — `payload:customer.id`, `payload['customer']['id']`, `payload:items[0]` — and you almost always cast the result, because an uncast path yields a `VARIANT` that renders a string with its JSON quotes. To expand an array into rows, join the table to the `FLATTEN` table function laterally: `SELECT o.id, f.value:sku::string FROM orders o, LATERAL FLATTEN(input => o.payload:items) f`. `FLATTEN` returns `SEQ`, `KEY`, `PATH`, `INDEX`, `VALUE` and `THIS`; `VALUE` is the element, `INDEX` its position in the array, and `KEY` the field name when you flatten an object. `OUTER => TRUE` keeps parent rows whose array is empty or missing, which is exactly the behaviour of a left join and the usual fix for silently disappearing orders.

code

sql · 7 lines

sql

-- payload: {"order_id":11,"items":[{"sku":"A1","qty":2},{"sku":"B7","qty":1}]}
SELECT o.payload:order_id::number AS order_id,
       f.index                    AS line_no,
       f.value:sku::string        AS sku,
       f.value:qty::number        AS qty
FROM orders_raw o,
     LATERAL FLATTEN(input => o.payload:items, OUTER => TRUE) f;

go deeper

for a junior

Be able to pull a scalar out with payload:field::string and expand an array with LATERAL FLATTEN, reading the element from the VALUE column.

for a middle

Explain the FLATTEN output columns, what OUTER, RECURSIVE and MODE change, and why every extracted path needs an explicit cast.

for a senior

Show the production shape: a raw VARIANT landing table plus typed projections, careful JSON-null handling, and STRIP_OUTER_ARRAY chosen deliberately at load time.

for a principal

Own the policy for semi-structured data — how long raw documents are retained for replay, where schema drift is absorbed, and what contract analysts are given on top of the raw payload.

VARIANT in one paragraph

VARIANT is Snowflake's universal semi-structured type; OBJECT and ARRAY are its constrained cousins. It stores a parsed document, not a text blob, and a single VARIANT value is limited to 16 MB compressed. You get one by loading JSON, Avro, ORC or Parquet with a matching file format, or by calling PARSE_JSON on a string. TO_JSON goes the other way.

sql

CREATE TABLE orders_raw (payload VARIANT);

COPY INTO orders_raw
  FROM @raw/orders/
  FILE_FORMAT = (TYPE = JSON STRIP_OUTER_ARRAY = TRUE);

STRIP_OUTER_ARRAY = TRUE matters when each file is one big JSON array: without it you get a single enormous row instead of one row per element (and can hit the 16 MB ceiling).

Navigating

sql

SELECT payload:order_id::number        AS order_id,
       payload:customer.email::string  AS email,
       payload['shipping']['country']::string AS country,
       payload:items[0]:sku::string    AS first_sku
FROM orders_raw;

The colon reaches into a VARIANT; dots and brackets continue the walk; brackets index arrays. Path segments are case-sensitive against the source document, which is a frequent source of unexplained NULLs. A missing path returns SQL NULL rather than erroring, so typos fail silently — a good reason to eyeball results early.

Always cast. payload:customer.email is a VARIANT; displayed or concatenated it carries surrounding double quotes, comparisons against a VARCHAR behave surprisingly, and downstream types drift. ::string, ::number, ::timestamp_ntz give you real SQL values. Use TRY_CAST-style tolerance (TRY_TO_NUMBER, TRY_TO_TIMESTAMP) where the source is untrustworthy.

Flattening arrays

FLATTEN is a table function: it takes a VARIANT and returns one row per element. Because it needs a value from the current row, you join it laterally — Snowflake accepts both the explicit LATERAL FLATTEN(...) form and the comma form with TABLE(FLATTEN(...)).

sql

SELECT o.payload:order_id::number AS order_id,
       f.index                    AS line_no,
       f.value:sku::string        AS sku,
       f.value:qty::number        AS qty
FROM orders_raw o,
     LATERAL FLATTEN(input => o.payload:items) f;

Output columns:

VALUE — the element itself, still a VARIANT, so keep casting.
INDEX — zero-based position within an array; NULL when flattening an object.
KEY — the field name when the input is an object; NULL for arrays.
PATH — the path from the input root to this element, useful with RECURSIVE.
SEQ — an identifier grouping the rows produced from one input value.
THIS — the container being flattened at this level.

The options that change semantics

OUTER => TRUE — emit one row with NULLs for a parent whose array is empty, missing or not an array. Without it, FLATTEN behaves like an inner join and those parents vanish. This is the single most common bug: an order with no line items disappears from the report entirely.
RECURSIVE => TRUE — descend through nested structures instead of only the top level, with PATH telling you where each row came from. Handy for exploring an unfamiliar payload.
MODE => 'ARRAY' | 'OBJECT' | 'BOTH' — restrict what gets expanded when the input can be either.
PATH => 'items' — flatten a sub-path of the input instead of the input itself.

Nesting two FLATTEN calls handles arrays inside arrays: flatten payload:orders, then flatten f1.value:items from that.

JSON null versus SQL NULL

A JSON null inside a VARIANT is not SQL NULL — it is a VARIANT holding the null value. IS NULL will not catch it; IS_NULL_VALUE(payload:field) will. Casting a JSON null yields SQL NULL, so payload:field::string IS NULL conflates the two. Interviewers like this distinction because it silently corrupts counts.

Exploring an unknown payload

SELECT * FROM TABLE(FLATTEN(input => payload, RECURSIVE => TRUE)) enumerates every path in a document, which is the fastest way to learn a payload's shape. For load-time schema work, INFER_SCHEMA over staged files and MATCH_BY_COLUMN_NAME on COPY let Snowflake map source fields to table columns by name rather than position.

Practical shape

Most teams keep a raw VARIANT landing table exactly as delivered, then build views or scheduled INSERT/MERGE jobs that project the hot fields into typed columns. That preserves the original document for replay while giving analysts stable, well-typed, prunable columns to query.

## VARIANT in one paragraph `VARIANT` is Snowflake's universal semi-structured type; `OBJECT` and `ARRAY` are its constrained cousins. It stores a parsed document, not a text blob, and a single `VARIANT` value is limited to 16 MB compressed. You get one by loading JSON, Avro, ORC or Parquet with a matching file format, or by calling `PARSE_JSON` on a string. `TO_JSON` goes the other way. ```sql CREATE TABLE orders_raw (payload VARIANT); COPY INTO orders_raw FROM @raw/orders/ FILE_FORMAT = (TYPE = JSON STRIP_OUTER_ARRAY = TRUE); ``` `STRIP_OUTER_ARRAY = TRUE` matters when each file is one big JSON array: without it you get a single enormous row instead of one row per element (and can hit the 16 MB ceiling). ## Navigating ```sql SELECT payload:order_id::number AS order_id, payload:customer.email::string AS email, payload['shipping']['country']::string AS country, payload:items[0]:sku::string AS first_sku FROM orders_raw; ``` The colon reaches into a `VARIANT`; dots and brackets continue the walk; brackets index arrays. Path segments are case-sensitive against the source document, which is a frequent source of unexplained NULLs. A missing path returns SQL `NULL` rather than erroring, so typos fail silently — a good reason to eyeball results early. **Always cast.** `payload:customer.email` is a `VARIANT`; displayed or concatenated it carries surrounding double quotes, comparisons against a `VARCHAR` behave surprisingly, and downstream types drift. `::string`, `::number`, `::timestamp_ntz` give you real SQL values. Use `TRY_CAST`-style tolerance (`TRY_TO_NUMBER`, `TRY_TO_TIMESTAMP`) where the source is untrustworthy. ## Flattening arrays `FLATTEN` is a table function: it takes a `VARIANT` and returns one row per element. Because it needs a value from the current row, you join it **laterally** — Snowflake accepts both the explicit `LATERAL FLATTEN(...)` form and the comma form with `TABLE(FLATTEN(...))`. ```sql SELECT o.payload:order_id::number AS order_id, f.index AS line_no, f.value:sku::string AS sku, f.value:qty::number AS qty FROM orders_raw o, LATERAL FLATTEN(input => o.payload:items) f; ``` Output columns: - **`VALUE`** — the element itself, still a `VARIANT`, so keep casting. - **`INDEX`** — zero-based position within an array; `NULL` when flattening an object. - **`KEY`** — the field name when the input is an object; `NULL` for arrays. - **`PATH`** — the path from the input root to this element, useful with `RECURSIVE`. - **`SEQ`** — an identifier grouping the rows produced from one input value. - **`THIS`** — the container being flattened at this level. ## The options that change semantics - **`OUTER => TRUE`** — emit one row with `NULL`s for a parent whose array is empty, missing or not an array. Without it, `FLATTEN` behaves like an inner join and those parents vanish. This is the single most common bug: an order with no line items disappears from the report entirely. - **`RECURSIVE => TRUE`** — descend through nested structures instead of only the top level, with `PATH` telling you where each row came from. Handy for exploring an unfamiliar payload. - **`MODE => 'ARRAY' | 'OBJECT' | 'BOTH'`** — restrict what gets expanded when the input can be either. - **`PATH => 'items'`** — flatten a sub-path of the input instead of the input itself. Nesting two `FLATTEN` calls handles arrays inside arrays: flatten `payload:orders`, then flatten `f1.value:items` from that. ## JSON null versus SQL NULL A JSON `null` inside a `VARIANT` is not SQL `NULL` — it is a `VARIANT` holding the null value. `IS NULL` will not catch it; `IS_NULL_VALUE(payload:field)` will. Casting a JSON null yields SQL `NULL`, so `payload:field::string IS NULL` conflates the two. Interviewers like this distinction because it silently corrupts counts. ## Exploring an unknown payload `SELECT * FROM TABLE(FLATTEN(input => payload, RECURSIVE => TRUE))` enumerates every path in a document, which is the fastest way to learn a payload's shape. For load-time schema work, `INFER_SCHEMA` over staged files and `MATCH_BY_COLUMN_NAME` on `COPY` let Snowflake map source fields to table columns by name rather than position. ## Practical shape Most teams keep a raw `VARIANT` landing table exactly as delivered, then build views or scheduled `INSERT`/`MERGE` jobs that project the hot fields into typed columns. That preserves the original document for replay while giving analysts stable, well-typed, prunable columns to query.

Orders with an empty items array vanished from a report built on LATERAL FLATTEN. Why?
`FLATTEN` produces no rows for an empty, missing or non-array input, so the lateral join drops the parent exactly as an inner join would. Add `OUTER => TRUE` and the parent comes back once with `VALUE`, `INDEX` and `KEY` set to NULL — the left-join equivalent. Reconciling parent counts before and after the flatten catches this in review.
Why does payload:status = 'PAID' behave unexpectedly without a cast?
An uncast path yields a `VARIANT`, and comparing a `VARIANT` against a `VARCHAR` forces conversion rules you did not choose; the value also renders with its JSON quotes when displayed or concatenated. Write `payload:status::string = 'PAID'`. Casting at the projection boundary keeps types explicit and makes the predicate behave like ordinary SQL.
How do you distinguish a JSON null from a missing field in a VARIANT?
A missing path evaluates to SQL NULL; a JSON `null` is a `VARIANT` carrying the null value, so plain `IS NULL` does not detect it. Use `IS_NULL_VALUE(payload:field)` for the JSON-null case and `payload:field IS NULL` for absence. Conflating them skews counts of "unknown" versus "not supplied".
When does STRIP_OUTER_ARRAY matter on a JSON file format?
When each file contains one top-level JSON array of records. Without it Snowflake loads the whole array as a single `VARIANT` row — awkward to query and liable to exceed the 16 MB per-value limit on large files. With it, each array element becomes its own row, which is almost always what you want for record-per-element payloads.

saying these in an interview costs you the question

Using a VARIANT path without casting it
Expecting FLATTEN to keep rows with empty arrays by default
Treating a JSON null as SQL NULL
Thinking VARIANT stores raw JSON text to be parsed per query
Forgetting VARIANT paths are case-sensitive against the document

In Snowflake, how do you query a JSON array inside a VARIANT column using LATERAL FLATTEN?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

VARIANT in one paragraph

Navigating

Flattening arrays

The options that change semantics

JSON null versus SQL NULL

Exploring an unknown payload

Practical shape

▾common follow-ups4 questions

▾red flags5 flags

§ context

▾related questions6 questions

▾related topics4 topics

VARIANT in one paragraph

Navigating

Flattening arrays

The options that change semantics

JSON null versus SQL NULL

Exploring an unknown payload

Practical shape

answer

go deeper

context