Snowflake LATERAL FLATTEN for Nested JSON — The Practical Guide (Plus Browser Prototyping)

Snowflake's LATERAL FLATTEN is the function you reach for when your VARIANT column contains nested JSON you need to unpack. It is powerful, but the syntax has enough moving parts that most people look up an example every time. This post is the practical reference — common patterns, edge cases, and a workflow tip: prototype your flatten in a browser-based JSON Flattener before writing SQL, so you know exactly what paths your query should target.

The Basic LATERAL FLATTEN Pattern

A typical Snowflake table has a VARIANT column holding raw JSON:

CREATE TABLE events (id INT, raw VARIANT);
INSERT INTO events SELECT 1, PARSE_JSON('{"user":{"name":"Alice","orders":[{"sku":"A"},{"sku":"B"}]}}');

To extract top-level user name:

SELECT id, raw:user.name::STRING AS user_name FROM events;

No LATERAL FLATTEN needed — just dot notation on the VARIANT column. Use FLATTEN when you need to explode an array or walk into unknown keys:

SELECT id, o.value:sku::STRING AS sku
FROM events, LATERAL FLATTEN(input => raw:user.orders) o;

Each element of the orders array becomes a row. The o.value reference gives you the array element; o.path gives you the path string; o.index gives you the position.

Recursive Flatten — Unknown-Shape JSON

When you do not know the JSON structure ahead of time, pass RECURSIVE => TRUE:

SELECT id, o.path, o.value
FROM events, LATERAL FLATTEN(input => raw, RECURSIVE => TRUE) o
WHERE o.value != PARSE_JSON('null');

This produces one row per leaf in the JSON tree. The path column contains the dot-and-bracket path (e.g., user.orders[0].sku). Perfect for discovery queries or when building a flat audit log of every field in a payload.

The output of recursive FLATTEN is also what our browser JSON Flattener produces conceptually — the difference is Snowflake gives it to you as rows, while the browser tool gives it as a flat key-value object.

Sell Custom Apparel — We Handle Printing & Free Shipping

Five Patterns That Cover Most Use Cases

1. Flatten an array column into rows.

SELECT e.id, o.value:sku::STRING AS sku, o.value:qty::NUMBER AS qty
FROM events e, LATERAL FLATTEN(input => e.raw:orders) o;

2. Flatten nested arrays (array of arrays).

SELECT e.id, inner.value::STRING AS tag
FROM events e, LATERAL FLATTEN(input => e.raw:tag_groups) outer,
LATERAL FLATTEN(input => outer.value) inner;

3. Flatten and pivot. Combine FLATTEN with PIVOT to turn array elements into columns.

4. Filter within a FLATTEN.

SELECT e.id, o.value:sku::STRING
FROM events e, LATERAL FLATTEN(input => e.raw:orders) o
WHERE o.value:status::STRING = 'paid';

5. Count array elements without flattening.

SELECT id, ARRAY_SIZE(raw:orders) AS order_count FROM events;

ARRAY_SIZE avoids the explode-then-count overhead when you only need the length.

Edge Cases That Trip People Up

NULL vs missing keys. raw:user.missing returns NULL silently. If you need to distinguish "key is null" from "key does not exist," use raw:user::OBJECT IS NOT NULL AND NOT raw:user.missing IS NULL or the TYPEOF function.

PARSE_JSON on malformed input. Snowflake's PARSE_JSON errors out on invalid JSON. Use TRY_PARSE_JSON to get NULL instead of a query failure. For upstream debugging, paste the failing row into our browser JSON Formatter to see exactly which character is invalid.

Empty arrays. FLATTEN on an empty array produces zero rows. If you need a "left-join-style" behavior (keep the parent row even if the array is empty), use OUTER => TRUE: LATERAL FLATTEN(input => raw:orders, OUTER => TRUE).

Type coercion. VARIANT values are untyped until you cast. o.value:qty::NUMBER coerces to number; o.value:qty::STRING keeps string. Forgetting the cast returns VARIANT, which you cannot use in most aggregate functions.

Case sensitivity. JSON keys in Snowflake are case-sensitive. raw:User.name and raw:user.name return different results. Double-quote the path if your keys have mixed case.

Prototyping Flatten Queries Before You Run Them

Snowflake charges by warehouse compute. Running exploratory LATERAL FLATTEN queries on a large VARIANT column burns credits for what could be a free browser preview.

Recommended workflow:

Step 1 — Export a sample. SELECT raw FROM events LIMIT 1; Copy the JSON value to your clipboard.

Step 2 — Browser prototype. Paste into our JSON Flattener. See every path in the document. Note which paths are objects vs arrays.

Step 3 — Design your SELECT. Now you know the exact dot paths your LATERAL FLATTEN should target. Write the query against the design.

Step 4 — Run on a small LIMIT first. Validate on a small result set before removing the LIMIT.

This replaces 10 minutes of trial-and-error LATERAL FLATTEN with 30 seconds of browser paste-and-click plus a well-designed query. Cheaper, faster, less frustrating.

Frequently Asked Questions

Does LATERAL FLATTEN work on VARCHAR columns or only VARIANT?

VARIANT is preferred. If your JSON is in a VARCHAR column, wrap it in PARSE_JSON first: LATERAL FLATTEN(input => PARSE_JSON(raw_string_column)). For large VARCHARs, converting the column to VARIANT permanently is more efficient than parsing on every query.

What is the difference between the FLATTEN function and LATERAL FLATTEN?

They are the same function — LATERAL is SQL syntax that allows the right side of a join to reference the left side. Snowflake documentation uses LATERAL FLATTEN because that is the typical usage: join a table with a FLATTEN of one of its VARIANT columns.

Can LATERAL FLATTEN handle deeply nested JSON without RECURSIVE?

Only one level at a time. Without RECURSIVE, each FLATTEN walks one step into the JSON. For nested arrays-in-arrays, chain multiple LATERAL FLATTEN clauses. With RECURSIVE, one call walks the entire tree.

Why do I get NULL rows when my array is empty?

Default FLATTEN behavior is to produce no rows for empty arrays. If you want the parent row preserved (with NULL for the flattened value), add OUTER => TRUE to your FLATTEN call. This matches a LEFT JOIN semantic.