Flatten Function in Snowflake: How to Use It

Formulas & functions · Updated November 22, 2026

The FLATTEN function in Snowflake converts a nested array or object into a set of rows, effectively unpivoting semi-structured data like JSON or VARIANT columns into a relational table format. You call it in the FROM clause of a SELECT statement, and it returns one row per element in the input path, with metadata columns describing each element’s position and key.

Why FLATTEN Exists: Semi-Structured Data in Snowflake

Snowflake stores JSON, Avro, ORC, Parquet, and XML data in VARIANT, OBJECT, and ARRAY columns. Unlike a relational table, this data is hierarchical—a single row might contain a list of orders, each with its own line items. You can’t directly join or aggregate inside those nested structures. FLATTEN bridges the gap by expanding each nested value into its own row, so you can use standard SQL operations like WHERE, GROUP BY, and JOIN on the extracted data.

For example, a table customer_events might have a column payload containing { "items": [ {"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1} ] }. Without FLATTEN, querying the quantity of each SKU is impossible. With FLATTEN, you get two rows, one per item.

FLATTEN Syntax and Parameters

The basic syntax is:

SELECT ...
FROM table, LATERAL FLATTEN(input => table.column, path => 'path.to.array', outer => false, recursive => false, mode => 'ARRAY')

Key parameters (all optional except input):

  • input: The expression to flatten. Usually a column of type VARIANT, OBJECT, or ARRAY, or a path like payload:items.
  • path: A string pointing to a sub-element within the input (e.g., 'items'). If omitted, flattens the whole input.
  • outer: When TRUE, rows with no matching elements are still returned with NULL values (like a LEFT JOIN). Default FALSE.
  • recursive: When TRUE, flattens all nested levels, not just the immediate children. Default FALSE.
  • mode: ‘OBJECT’, ‘ARRAY’, or ‘BOTH’ (default). Controls whether keys, values, or both are flattened.

The function returns these columns: SEQ (sequence number of the original row), KEY (key for OBJECT elements, NULL for ARRAY), PATH (path to the element), INDEX (position in the array, or NULL for objects), VALUE (the actual element value), and THIS (the original element before flattening).

Practical Example: Flattening a JSON Array

Assume a table orders with columns order_id (INT) and order_json (VARIANT) containing:

{"customer": "Alice", "lines": [{"product": "Laptop", "price": 1200}, {"product": "Mouse", "price": 25}]}

To get each product line as a row:

SELECT order_id, f.value:product::STRING AS product, f.value:price::NUMBER AS price
FROM orders, LATERAL FLATTEN(input => order_json, path => 'lines') f;

Result:

  • Row 1: order_id=1, product=’Laptop’, price=1200
  • Row 2: order_id=1, product=’Mouse’, price=25

Note the ::STRING and ::NUMBER casts—they convert the VARIANT values to native SQL types. Without casts, you’ll get VARIANT values that may not sort or compare correctly.

Using LATERAL vs. Comma Join

In Snowflake, you can write FROM orders, LATERAL FLATTEN(...) or simply FROM orders, FLATTEN(...). The comma implies a lateral join, meaning the FLATTEN function sees the current row from the left table. This is required because FLATTEN depends on the row’s data. You cannot use a regular JOIN without the LATERAL keyword because the right side references the left side’s column. Both forms work, but the explicit LATERAL is clearer for readers.

If you have multiple arrays to flatten, you can chain them: FROM orders, LATERAL FLATTEN(input => order_json, path => 'lines') l, LATERAL FLATTEN(input => l.value, path => 'subitems') s. This creates a cross product of the two flattened levels, which is often what you need for deeply nested data.

Common Mistakes and How to Avoid Them

Several pitfalls trip up new users:

  • Forgetting the path: If you flatten the whole VARIANT without a path, you get one row per top-level key, not per array element. Always specify the path to the array you care about.
  • Ignoring NULLs: By default, rows with an empty array or missing path are dropped. Use outer => true to keep the parent row with NULL values.
  • Using the wrong cast: f.value is a VARIANT. Casting to VARCHAR or NUMBER explicitly prevents type errors in later operations.
  • Confusing INDEX and SEQ: INDEX is the position within the array (0-based). SEQ is a per-row counter that increments for each new input row, not for each flattened element. Don’t use SEQ for array ordering.
  • Not using LATERAL: If you try FROM orders JOIN FLATTEN(...) without LATERAL, Snowflake will error because the FLATTEN function can’t reference order_json from the left table.

FLATTEN vs. Alternative Approaches

Snowflake offers other ways to handle nested data:

  • Dot notation and bracket access: For a single value, you can use order_json:customer or order_json['customer'] without FLATTEN. This works for scalars but not for arrays you want to expand.
  • ARRAY functions: ARRAY_SIZE, ARRAY_SLICE, and ARRAY_CONTAINS let you inspect arrays but not turn them into rows.
  • UNNEST: Some databases use UNNEST; Snowflake does not. FLATTEN is the equivalent.
  • Recursive FLATTEN: For deeply nested structures, recursive => true flattens every level into a single set of rows, with the PATH column showing the full path. This is powerful but can produce many rows; use it carefully.

For most use cases, FLATTEN is the right tool. If you only need to extract a few scalar fields, dot notation is simpler and faster.

Performance and Cost Considerations

FLATTEN is a compute operation that runs on Snowflake’s virtual warehouses. The cost depends on the number of rows processed and the size of the arrays. A table with 10 million rows, each containing a 100-element array, will generate 1 billion output rows—that can consume significant credits. To optimize:

  • Filter early: Use WHERE clauses on the parent table before flattening if possible (though Snowflake’s optimizer may reorder).
  • Limit the path: Flatten only the specific array you need, not the entire VARIANT.
  • Use recursive => false unless you truly need all levels.
  • Consider materializing the flattened result into a separate table if you query it repeatedly, using CREATE TABLE AS SELECT (CTAS).

Snowflake’s billing is per-second based on warehouse size. A small warehouse (X-Small) might handle a few million rows quickly; a large flatten job may require a Medium or Large warehouse to finish in reasonable time. Monitor the query profile to see how many bytes are scanned and how much time is spent on the FLATTEN operator.

Frequently Asked Questions

Can I flatten an array inside a VARIANT column without specifying the path? Yes, if the VARIANT itself is an array, you can omit the path. For example, if col is [1,2,3], then FLATTEN(input => col) returns three rows. If it’s an object, you’ll get one row per key.

What’s the difference between FLATTEN and UNPIVOT? UNPIVOT works on relational columns (turning multiple columns into rows), while FLATTEN works on semi-structured data inside a single column. They are not interchangeable.

How do I keep the parent row when the array is empty? Use outer => true. That returns a single row with NULL values for the flattened columns, similar to a LEFT JOIN.

Does FLATTEN preserve order? Yes, the INDEX column gives the 0-based position of each element in the array. If you need the original order, sort by INDEX after flattening.

Can I flatten a column that is not VARIANT? FLATTEN expects a semi-structured type (VARIANT, OBJECT, ARRAY). If you have a string that looks like JSON, you first need to parse it with PARSE_JSON() to convert it to VARIANT.

Is FLATTEN available in all Snowflake editions? Yes, it’s a core SQL function available in Standard, Enterprise, and Business Critical editions. No special licensing is required.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.