Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

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 LATERAL keyword, 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 outer parameter.
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

ParameterDescriptionDefault
inputJSON column or expression to flattenRequired
pathJSON path to nested array/object to flattenRoot of input
outerInclude rows for null or empty arrays if truefalse
recursiveFlatten nested arrays recursively if truefalse
✅

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.