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 => trueto keep the parent row with NULL values. - Using the wrong cast:
f.valueis a VARIANT. Casting to VARCHAR or NUMBER explicitly prevents type errors in later operations. - Confusing INDEX and SEQ:
INDEXis the position within the array (0-based).SEQis 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 referenceorder_jsonfrom 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:customerororder_json['customer']without FLATTEN. This works for scalars but not for arrays you want to expand. - ARRAY functions:
ARRAY_SIZE,ARRAY_SLICE, andARRAY_CONTAINSlet 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 => trueflattens every level into a single set of rows, with thePATHcolumn 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 => falseunless 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
- Snowflake SPLIT: Turn a String Into an Array
- C++ String Data Type: How std::string Works
- Snowflake Split String Into Columns: SQL Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Wildcard SUMIF Formula Examples That Match TextTo total values for text that contains a word, use SUMIF with asterisks around the word: with product…
- Rank in Excel: Formula Examples, Ties, and Sort OrderTo rank values in Excel, use RANK.EQ: with scores in B2:B6, enter =RANK.EQ(B2,$B$2:$B$6,0) in C2 and fill down.…
- Excel SORT and INDEX: How to Sort Data and Return ValuesTo sort a range in Excel for Microsoft 365, use =SORT(A2:C6,3,-1) to sort its rows by the third…
- Excel IFS Statement Examples and How to Use the FunctionExcel’s IFS function checks conditions in order and returns the result paired with the first condition that is…
- Arduino Pass String to Function: Examples, Types, and SafetyYes. In Arduino C++, pass a String to a function by putting its type in the parameter list,…
- Excel Formula for General Average and When to Use ItIf your values are in A2:A10, the Excel formula for their general arithmetic average is =AVERAGE(A2:A10). It adds…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.