Snowflake SQL Patterns

Flatten nested JSON without exploding the grain

Two LATERAL FLATTEN calls multiply rows. Totals quietly go wrong, and empty arrays drop parents.

The problem

An event payload has more than one array — line items and tags, vehicles and extras, challenges and keys. You need those arrays as rows. The instinct is to add a second LATERAL FLATTEN in the same FROM clause. That often produces a Cartesian product inside each parent row, and any measure you SUM from the parent is multiplied by both array lengths.

A working example

Snowflake syntax (verified against official FLATTEN documentation, not executed on Snowflake):

SELECT
  e.event_id,
  item.value:sku::STRING  AS sku,
  tag.value::STRING       AS tag
FROM events AS e,
     LATERAL FLATTEN(INPUT => e.payload:items) AS item,
     LATERAL FLATTEN(INPUT => e.payload:tags)  AS tag;

Official docs describe FLATTEN as a table function that returns SEQ, KEY, PATH, INDEX, VALUE, and THIS. For chained, nested structures they tell you to use LATERAL so each call can see the previous one’s output. Default OUTER => FALSE drops input rows whose path is missing or whose array has zero entries. OUTER => TRUE keeps those parent rows and fills KEY, INDEX, and VALUE with NULL.

The multiplication problem is not Snowflake-specific. The same shape was executed in DuckDB 2.0.0 with UNNEST, which is not Snowflake’s FLATTEN but exhibits the same join grain:

WITH ev AS (
  SELECT
    1 AS id,
    15840.50 AS total,
    ['A', 'B', 'C'] AS items,
    ['x', 'y', 'z'] AS tags
)
SELECT count(*) AS row_count, sum(e.total) AS summed_total
FROM ev AS e,
     UNNEST(e.items) AS i(item),
     UNNEST(e.tags)  AS t(tag);

Result: 9 rows and a summed total of 142564.50, which is 15840.50 * 9. The parent total was never 142564.50. Three items and three tags multiplied.

If one of the arrays is empty, an inner flatten/unnest drops the parent:

WITH ev AS (
  SELECT 1 AS id, []::VARCHAR[] AS items
  UNION ALL
  SELECT 2, ['keep']
)
SELECT e.id, i.item
FROM ev AS e, UNNEST(e.items) AS i(item);

Executed result: only (2, 'keep'). Event 1 disappeared. Official Snowflake OUTER => FALSE (the default) is the equivalent footgun. A left join to the unnest kept event 1 with a NULL item in DuckDB; on Snowflake, OUTER => TRUE is the documented way to keep zero-row expansions.

The gotcha

OUTER => TRUE does not undo a two-array multiplication. It only prevents a parent with an empty or missing array from vanishing. If you flatten items and tags together, three items and three tags are still nine rows.

RECURSIVE => TRUE is a second, separate footgun. Official docs say it expands all sub-elements, not just the path you pointed at. A recursive flatten of a nested object returns branches and leaves. If you want leaf values only, official semi-structured examples filter with TYPEOF(value) NOT IN ('OBJECT', 'ARRAY').

MODE => 'ARRAY' over an object (or 'OBJECT' over an array) can return no rows without erroring — the parent then looks like it had no data. The official default is MODE => 'BOTH'.

How to keep the grain honest

Illustrative Snowflake form for a nested child (docs-verified syntax, not executed):

SELECT
  e.event_id,
  item.value:sku::STRING AS sku,
  extra.value::STRING    AS extra
FROM events AS e,
     LATERAL FLATTEN(INPUT => e.payload:items) AS item,
     LATERAL FLATTEN(INPUT => item.value:extras) AS extra;

Here the second flatten is a child of the first, not a sibling cross join.

When not to use this

Verification

The row-multiplication (9 rows, 15840.50 × 9) and the empty-array drop were executed in DuckDB 2.0.0 with UNNEST. Snowflake FLATTEN arguments (INPUT, PATH, OUTER, RECURSIVE, MODE), default values, output columns, and the advice to use LATERAL when chaining are from official Snowflake documentation. Snowflake LATERAL FLATTEN(...) was not executed.

Official docs