Flattening Nested JSON With LATERAL FLATTEN in Snowflake

FLATTEN is the Snowflake answer to the question "how do I turn this array into rows". It is a table function that takes a VARIANT and produces one row per element, and with the LATERAL keyword it works on each row of the table beside it. It is also where most Snowflake JSON queries spend their complexity: nesting, empty arrays, objects whose keys are data. This article covers the output columns, the parameters, joining, chaining, the empty-array trap and the recursive mode.

The basic call

SELECT o.doc:id::number    AS order_id,
       i.index             AS line_no,
       i.value:sku::string AS sku,
       i.value:qty::number AS qty
FROM raw_orders o,
     LATERAL FLATTEN(input => o.doc:items) i;

FLATTEN is written with named arguments (input => ...). It returns a row for each element of the array in o.doc:items, and the comma-and-LATERAL makes it evaluate once per order. The i alias then gives access to the output columns:

For an array of objects, i.value is each object and you read its fields with colon paths: i.value:sku::string. For an array of scalars (tags, ids), i.value::string is the element. Strings in VALUE carry double quotes in a result grid until you cast, as with any VARIANT (querying VARIANT columns with colon syntax).

Parameters

The documented parameters of FLATTEN:

• **INPUT** (required): the VARIANT, OBJECT or ARRAY to expand.

• **PATH**: a path inside INPUT to start from, so FLATTEN(input => o.doc, path => 'items') is the same as passing o.doc:items.

• **OUTER**: when TRUE, a row is returned even if the input has nothing to expand.

• **RECURSIVE**: when TRUE, expand all levels of nesting, not only the first.

• **MODE**: 'OBJECT', 'ARRAY' or 'BOTH' (the default), to restrict which kinds of value are expanded.

Because the parameters are named, their order does not matter, and leaving out the optional ones gives their defaults (OUTER and RECURSIVE false).

Objects as well as arrays

On an object, FLATTEN returns one row per key, with KEY holding the key name and VALUE the value. That makes it the tool for documents whose keys are data, such as a map from a product id to its quantity:

SELECT o.doc:id::number AS order_id,
       k.key            AS product_id,
       k.value::number  AS qty
FROM raw_orders o,
     LATERAL FLATTEN(input => o.doc:quantities) k;

Each product id becomes a row. Compare the same job in the engines I could test: jsonb_each in PostgreSQL (expanding JSONB into rows) and json_each in SQLite (expanding JSON arrays).

Empty and missing arrays

With a comma join and no OUTER, FLATTEN behaves like an inner join. An order whose items array is empty, whose items is NULL, or that has no items key at all produces no rows and vanishes from the result. If a report must count every order, say so:

SELECT o.doc:id::number AS order_id,
       i.value:sku::string AS sku
FROM raw_orders o,
     LATERAL FLATTEN(input => o.doc:items, outer => TRUE) i;

With OUTER => TRUE, an order without elements gets one row, with NULL in the columns derived from VALUE. The same decision exists in every engine: SQL Server's OUTER APPLY (reading JSON with OPENJSON), BigQuery's LEFT JOIN UNNEST and PostgreSQL's left join lateral ... on true.

A subtle point: with OUTER, a row for an empty array and a row for an element that happens to be a JSON null are both rows with NULLs in the value-derived columns. If it matters, inspect INDEX: it is NULL for the "no elements" row and a number for a real element.

Chaining for nested levels

An order has items, and each item has options. Flatten twice, feeding the second call with a field of the first call's VALUE:

SELECT o.doc:id::number    AS order_id,
       i.value:sku::string AS sku,
       p.value::string     AS option
FROM raw_orders o,
     LATERAL FLATTEN(input => o.doc:items) i,
     LATERAL FLATTEN(input => i.value:opts, outer => TRUE) p;

Each item with n options gives n rows, and an item with an empty opts array gives one row with a NULL option, because the second call uses OUTER. The first call has no OUTER, so an order with no items still disappears; set it there as well to keep them. The number of rows is the product along each path, so watch the output size when arrays are long.

Recursive mode

RECURSIVE => TRUE flattens every level in one call. Each row is a node of the document with its PATH, and you filter to the nodes you want:

SELECT f.path, f.key, f.index, f.value
FROM raw_orders o,
     LATERAL FLATTEN(input => o.doc, recursive => TRUE) f
WHERE TYPEOF(f.value) NOT IN ('OBJECT', 'ARRAY');

That returns the leaves only: scalar values with the full path to each one. It is the equivalent of json_tree with atom is not null in SQLite (expanding JSON arrays with json_each and json_tree) and is the quickest way to learn what a new, undocumented document contains. Because it touches every node, use it on a sample, and not as the basis of a production query.

Putting rows back together

After you have filtered or aggregated, ARRAY_AGG and OBJECT_CONSTRUCT rebuild structure: ARRAY_AGG(i.value:sku::string) collects SKUs per order, and OBJECT_CONSTRUCT('sku', i.value:sku, 'qty', i.value:qty) rebuilds an item object. ARRAY_AGG can take a WITHIN GROUP (ORDER BY ...) to fix the order of the array, which matters because the order of rows from FLATTEN follows the input array only when you ask for it with ORDER BY i.index. Always order by INDEX if the position of the element has meaning.

Performance

FLATTEN multiplies rows, and the cost is in the rows it produces. A few habits from the documentation's performance guidance and from common sense:

• Filter the base table first, by columns Snowflake can prune on (a date, a partition key), and flatten after: the WHERE on the parent should come before the expansion does the work.

• Select only the paths you need from VALUE rather than carrying the whole VARIANT through later steps.

• If the same flatten feeds many reports, materialize it once into a typed table (CREATE TABLE order_items AS SELECT ...). The typed table prunes better and costs no parse.

For the broader comparison with the other engines, see JSON in SQL Server, Oracle, Snowflake and BigQuery compared. To explore a sample document by eye before you flatten it, the JSON formatter shows its structure in the browser.

When FLATTEN is more than you need

You want the number of elements. ARRAY_SIZE(doc:items) counts without producing rows.

You want one element by position. doc:items[0] is a path, with no row expansion.

You only test whether a value is present. ARRAY_CONTAINS('A1'::variant, doc:skus) or a similar function avoids the expansion for simple arrays of scalars.

The structure is regular and large. Load it into typed columns when you ingest, as with COPY INTO ... SELECT $1:id::number, ..., and skip repeated flattening.

You are exploring. Recursive mode is the right tool for a first look; do not leave it in a dashboard.

The reference is Snowflake's documentation of FLATTEN and of working with arrays and objects.