Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 38 ยท Querying and Semi-structured Data

FLATTEN and LATERAL

Turn JSON arrays and objects into rows with Snowflake FLATTEN and LATERAL. Learn the output columns, OUTER and RECURSIVE options, nested arrays and pivoting back.

A JSON order might contain an array of line items. In a table you would want one row per item. FLATTEN does exactly that: it takes an array (or object) and produces one row per element. Combined with LATERAL, it joins those rows back to the parent row so you keep the order id next to each item.

The basic pattern

SQL
-- Snowflake SQL (read-only)
SELECT e.payload:order_id::NUMBER   AS order_id,
       i.value:sku::STRING          AS sku,
       i.value:qty::NUMBER          AS qty,
       i.index                      AS position
FROM shop.raw.orders_json e,
     LATERAL FLATTEN(INPUT => e.payload:items) i;

LATERAL lets the table function refer to columns of the table on its left (e.payload), evaluated row by row. The comma is a join; each event row is paired with its own flattened items.

FLATTEN returns these columns:

Column Meaning
VALUE The element (a VARIANT; cast it)
INDEX Position in an array (0-based)
KEY Key name when flattening an object
PATH Path to this element
THIS The element being flattened
SEQ A sequence number for the input row

A runnable equivalent

SQLite's json_each is the closest relative: it unnests arrays and objects. The stand-in below plays out the same example. json_each.value and json_each.key mirror VALUE and KEY/INDEX:

SQL Stand-in: one row per array element

Press Run (or Ctrl + Enter) to see the result here.

Order 3 has an empty array and disappears from the result: an inner join with nothing to join. Snowflake's FLATTEN behaves the same by default.

OUTER => TRUE keeps empty rows

To keep parent rows whose array is empty or missing, use OUTER => TRUE; the flattened columns are then NULL:

SQL
-- Snowflake SQL (read-only)
SELECT e.payload:order_id::NUMBER AS order_id, i.value:sku::STRING AS sku
FROM shop.raw.orders_json e,
     LATERAL FLATTEN(INPUT => e.payload:items, OUTER => TRUE) i;

The portable way is a LEFT JOIN:

SQL Stand-in: keep orders with no items (like OUTER => TRUE)

Press Run (or Ctrl + Enter) to see the result here.

Flattening objects

When the input is an object, KEY holds each key and VALUE its value. That turns a dynamic set of attributes into rows:

SQL
-- Snowflake SQL (read-only)
SELECT e.payload:user.id::NUMBER AS user_id,
       p.key                     AS property,
       p.value::STRING           AS property_value
FROM shop.raw.events e,
     LATERAL FLATTEN(INPUT => e.payload:properties) p;

If properties is {"plan":"pro","country":"DE"}, you get two rows for that user. This "key-value" shape is perfect for counting how often each property appears.

SQL Stand-in: object keys become rows

Press Run (or Ctrl + Enter) to see the result here.

Nested arrays and RECURSIVE

When arrays contain arrays, flatten twice, once per level:

SQL
-- Snowflake SQL (read-only)
SELECT o.payload:order_id AS order_id, i.value:sku::STRING AS sku, t.value::STRING AS tag
FROM shop.raw.orders_json o,
     LATERAL FLATTEN(INPUT => o.payload:items) i,
     LATERAL FLATTEN(INPUT => i.value:tags)    t;

RECURSIVE => TRUE descends into every level automatically and returns every leaf and container, with PATH showing where each came from. Add MODE => 'ARRAY' or 'OBJECT' to restrict what it flattens, and filter on PATH or typeof(value) to keep only what you need. Recursive flattening is great for exploring an unknown document. For production queries, explicit paths are clearer and faster.

Aggregating back: ARRAY_AGG and OBJECT_AGG

After flattening and filtering you often rebuild a document:

SQL
-- Snowflake SQL (read-only)
SELECT payload:order_id::NUMBER                AS order_id,
       ARRAY_AGG(i.value:sku::STRING)          AS skus,
       SUM(i.value:qty::NUMBER)                AS total_qty
FROM shop.raw.orders_json,
     LATERAL FLATTEN(INPUT => payload:items) i
GROUP BY payload:order_id;

The runnable version aggregates the unnested rows with group_concat and SUM:

SQL Stand-in: flatten, filter and re-aggregate

Press Run (or Ctrl + Enter) to see the result here.

Performance notes

  • Flattening multiplies rows: an event with 50 items yields 50 rows. Filter parent rows first, flatten after, and flatten only the paths you need.
  • Cast VALUE fields once in a CTE rather than repeatedly in the query.
  • If you flatten the same arrays all the time, materialize the result into a table once and query that.

LATERAL FLATTEN and TABLE(FLATTEN(...)) mean the same thing; the keyword LATERAL is simply the modern, more readable spelling.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does LATERAL allow FLATTEN to do?

    LATERAL evaluates the function for each preceding row so FLATTEN can use that row's JSON column.
  2. 2. An order has an empty items array. With plain FLATTEN, what happens to that order?

    Without OUTER => TRUE, rows with nothing to flatten produce no output.
  3. 3. Which FLATTEN column holds the position of an element in an array?

    INDEX is the 0-based array position; KEY is for object keys.
  4. 4. How do you flatten arrays nested inside array elements?

    Chain another LATERAL FLATTEN over the inner path, or use RECURSIVE => TRUE for exploration.

Summary

  • LATERAL FLATTEN(INPUT => path) produces one row per array element or object entry.
  • Use VALUE, INDEX, KEY and PATH; cast VALUE to the type you need.
  • OUTER => TRUE keeps parents with empty arrays; chain several FLATTENs for nested arrays.
  • ARRAY_AGG and OBJECT_AGG rebuild documents from rows.
  • Flatten late and only what you need, because it multiplies rows.

Next: Querying JSON: Paths, Arrays and Objects