How to Use LATERAL FLATTEN in Snowflake: Syntax and Examples
Use
LATERAL FLATTEN in Snowflake to convert nested arrays or objects in semi-structured data into separate rows. It works by joining the original table with the flattened elements using LATERAL, allowing easy querying of nested data.Syntax
The LATERAL FLATTEN function is used with the FROM clause to expand arrays or objects inside a column. It returns a set of rows, one for each element in the array or object.
Key parts:
table: Your original table.LATERAL FLATTEN(input => column): Flattens the nested array or object in the specified column.AS alias: Gives a name to the flattened output for referencing.
sql
SELECT t.id, f.value FROM my_table t, LATERAL FLATTEN(input => t.json_column) f;
Example
This example shows how to flatten a JSON array stored in a column to get each element as a separate row.
sql
CREATE OR REPLACE TABLE my_table (id INT, json_column VARIANT); INSERT INTO my_table VALUES (1, PARSE_JSON('["apple", "banana", "cherry"]')), (2, PARSE_JSON('["dog", "cat"]')); SELECT t.id, f.value AS fruit_or_pet FROM my_table t, LATERAL FLATTEN(input => t.json_column) f ORDER BY t.id, f.index;
Output
ID | FRUIT_OR_PET
---|-------------
1 | apple
1 | banana
1 | cherry
2 | dog
2 | cat
Common Pitfalls
Common mistakes when using LATERAL FLATTEN include:
- Not using
LATERALkeyword, which causes errors because flatten returns a table function. - Flattening a column that is not an array or object, resulting in empty or unexpected results.
- Ignoring the
indexorkeycolumns from flatten output when order or keys matter.
Correct usage always includes LATERAL and references the input parameter properly.
sql
/* Wrong: Missing LATERAL keyword */ SELECT t.id, f.value FROM my_table t, FLATTEN(input => t.json_column) f; /* Right: Include LATERAL */ SELECT t.id, f.value FROM my_table t, LATERAL FLATTEN(input => t.json_column) f;
Quick Reference
| Term | Description |
|---|---|
| LATERAL | Allows the flatten function to access columns from the main table in the FROM clause. |
| FLATTEN(input => column) | Expands arrays or objects in the specified column into multiple rows. |
| value | The actual element from the array or object in each flattened row. |
| index | The position of the element in the array (starting at 0). |
| key | The key name if flattening an object instead of an array. |
Key Takeaways
Use LATERAL FLATTEN to convert nested arrays or objects into rows for easy querying.
Always include the LATERAL keyword before FLATTEN in the FROM clause.
Flatten returns columns like value, index, and key to help identify elements.
Flatten only works on VARIANT, OBJECT, or ARRAY data types containing nested data.
Check your data type before flattening to avoid empty or incorrect results.
