Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 LATERAL keyword, 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 index or key columns 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

TermDescription
LATERALAllows 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.
valueThe actual element from the array or object in each flattened row.
indexThe position of the element in the array (starting at 0).
keyThe 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.