How to Flatten JSON in Snowflake: Simple Guide
In Snowflake, you can flatten JSON data using the
FLATTEN table function, which expands nested arrays or objects into a set of rows. Use LATERAL FLATTEN(input => json_column) in your FROM clause to extract and work with nested JSON elements.Syntax
The FLATTEN function in Snowflake is used to expand nested JSON arrays or objects into rows. It is typically used with LATERAL in the FROM clause.
input: The JSON column or expression to flatten.path(optional): The JSON path to the nested array or object to flatten.outer(optional): If true, includes rows for null or empty arrays.
sql
SELECT f.value FROM my_table, LATERAL FLATTEN(input => my_table.json_column) f;
Example
This example shows how to flatten a JSON array stored in a column named data in table events. It extracts each element of the array as a separate row.
sql
CREATE OR REPLACE TABLE events (id INT, data VARIANT); INSERT INTO events VALUES (1, PARSE_JSON('[{"name": "Alice"}, {"name": "Bob"}]')), (2, PARSE_JSON('[{"name": "Carol"}]')); SELECT id, f.value:name AS name FROM events, LATERAL FLATTEN(input => data) f ORDER BY id;
Output
ID | NAME
---|-------
1 | Alice
1 | Bob
2 | Carol
Common Pitfalls
Common mistakes when flattening JSON in Snowflake include:
- Not using
LATERALkeyword, which causes errors. - Flattening a non-array JSON element, which returns no rows.
- Ignoring nested objects inside arrays, requiring additional flatten calls.
- Not handling empty or null arrays, which can be controlled with the
outerparameter.
sql
/* Wrong: Missing LATERAL */ SELECT f.value FROM events, FLATTEN(input => data) f; /* Right: Using LATERAL */ SELECT f.value FROM events, LATERAL FLATTEN(input => data) f;
Quick Reference
| Parameter | Description | Default |
|---|---|---|
| input | JSON column or expression to flatten | Required |
| path | JSON path to nested array/object to flatten | Root of input |
| outer | Include rows for null or empty arrays if true | false |
| recursive | Flatten nested arrays recursively if true | false |
Key Takeaways
Use LATERAL FLATTEN(input => json_column) to expand JSON arrays into rows.
The FLATTEN function works only on arrays or objects inside JSON VARIANT columns.
Use the path parameter to target nested arrays inside JSON structures.
Set outer=true to include rows for empty or null arrays when needed.
Always test flattening on sample data to understand the output structure.
