How to Flatten JSON Data in Snowflake 

Working with JSON in Snowflake is manageable right up until the data stops looking flat.

Pulling a value from a nested object is usually simple enough. But once your payload includes arrays like order items, user roles, product tags, or event attributes, you run into a different problem. You do not just need to extract a value. You need to turn repeated elements into rows.

That is what FLATTEN is for.

In Snowflake, FLATTEN is the function you use when semi-structured data needs to behave more like a table. Most of the time, that means pairing it with LATERAL so each row in your source table can expand its own JSON array cleanly.

If you are still working through how to parse raw JSON strings or extract values from nested objects, start with How to Query JSON Data in Snowflake. This guide focuses on the next step: turning arrays into rows you can actually filter, aggregate, and report on.

Situation	What to use
Need a field from a JSON object	Path notation like payload:user.id
Need one row per array element	LATERAL FLATTEN()
Need to preserve rows with empty arrays	OUTER => TRUE
Need to flatten nested arrays	Chain multiple FLATTEN() calls

What FLATTEN does in Snowflake

FLATTEN expands a VARIANT, OBJECT, or ARRAY value into rows.

In practical terms, most teams use it to unnest arrays inside JSON payloads so the result can be queried like a normal table. If one order has three line items, flattening the items array gives you three rows. If one user has five roles, flattening the roles array gives you five rows.

That is the job.

When to use LATERAL FLATTEN

Use LATERAL FLATTEN when:

• your JSON contains an array
• you need one row per element in that array
• you want to join, filter, aggregate, or report on those values

Do not use FLATTEN just to pull a field from a regular JSON object. For that, path notation or GET_PATH() is usually the better tool.

Use path extraction for this

Copy Code

SELECT

payload:user.id::INT AS user_id,

payload:user.email::STRING AS email

FROM events;

Use FLATTEN for this

Copy Code

SELECT

payload:order_id::STRING AS order_id,

item.value:sku::STRING AS sku

FROM orders,

LATERAL FLATTEN(INPUT => payload:items) AS item;

Basic syntax

The core pattern looks like this:

Copy Code

SELECT

t.payload:id::STRING AS id,

f.value

FROM my_table t,

LATERAL FLATTEN(INPUT => t.payload:array_field) AS f;

Here is what is happening:

• t.payload:array_field points to the array
• FLATTEN expands each element into a row
• LATERAL lets Snowflake evaluate that expansion against each row from my_table
• f.value gives you the current element

Snowflake also returns metadata columns like SEQ, KEY, PATH, INDEX, VALUE, and THIS, though VALUE is the one most people use most often.

A simple example

Copy Code

WITH source AS (

SELECT PARSE_JSON(‘{

“user_id”: 1001,

“roles”: [“admin”, “editor”, “viewer”]

}’) AS data

)

SELECT

data:user_id::INT AS user_id,

role.value::STRING AS role

FROM source,

LATERAL FLATTEN(INPUT => data:roles) AS role;

Result

Copy Code

user_id | role

——–|——–

1001    | admin

1001    | editor

1001    | viewer

PARSE_JSON converts a JSON string into Snowflake’s VARIANT type, which is what you want before you start querying or flattening semi-structured data.

Why LATERAL matters

This part sounds more intimidating than it is.

FLATTEN turns arrays into rows. LATERAL makes sure that happens row by row against the source table.

That is the bit that matters.

Flattening order line items

This is the use case most people actually care about because it maps cleanly to real reporting work.

Copy Code

CREATE OR REPLACE TEMP TABLE orders AS

SELECT PARSE_JSON(column1) AS order_data

FROM VALUES

(‘{

“order_id”: “ord_7821”,

“customer_id”: 5502,

“status”: “shipped”,

“items”: [

{“sku”: “PRO-ANNUAL”, “qty”: 1, “unit_price”: 2988.00},

{“sku”: “ADDON-SEATS-5”, “qty”: 2, “unit_price”: 199.00}

]

}’),

(‘{

“order_id”: “ord_7822”,

“customer_id”: 5503,

“status”: “pending”,

“items”: [

{“sku”: “STARTER-MONTHLY”, “qty”: 1, “unit_price”: 49.00}

]

}’);

Now flatten the items array:

Copy Code

SELECT

order_data:order_id::STRING AS order_id,

order_data:customer_id::INT AS customer_id,

order_data:status::STRING AS status,

item.value:sku::STRING AS sku,

item.value:qty::INT AS qty,

item.value:unit_price::NUMBER(10,2) AS unit_price,

item.value:qty::INT * item.value:unit_price::NUMBER(10,2) AS line_total

FROM orders,

LATERAL FLATTEN(INPUT => order_data:items) AS item;

This turns semi-structured order data into something you can actually use for line-item reporting, revenue analysis, or product mix reporting.

How to flatten nested arrays

Sometimes one array sits inside another. In that case, chain FLATTEN calls.

Copy Code

WITH orders AS (

SELECT PARSE_JSON(‘{

“order_id”: “ord_9001”,

“items”: [

{

“sku”: “PRO-ANNUAL”,

“discounts”: [

{“type”: “promo”, “amount”: 100},

{“type”: “volume”, “amount”: 50}

]

}

]

}’) AS order_data

)

SELECT

order_data:order_id::STRING AS order_id,

item.value:sku::STRING AS sku,

discount.value:type::STRING AS discount_type,

discount.value:amount::NUMBER AS discount_amount

FROM orders,

LATERAL FLATTEN(INPUT => order_data:items) AS item,

LATERAL FLATTEN(INPUT => item.value:discounts) AS discount;

The second flatten works because it uses item.value from the first one.

That is the pattern to remember.

OUTER => TRUE is the parameter most people miss

By default, FLATTEN skips rows where the target array is empty or missing. If you need to preserve the parent row, use OUTER => TRUE.

Without OUTER => TRUE

Copy Code

SELECT

p.product_id,

p.data:name::STRING AS product_name,

t.value::STRING AS tag

FROM products p,

LATERAL FLATTEN(INPUT => p.data:tags) AS t;

With OUTER => TRUE

Copy Code

SELECT

p.product_id,

p.data:name::STRING AS product_name,

t.value::STRING AS tag

FROM products p,

LATERAL FLATTEN(INPUT => p.data:tags, OUTER => TRUE) AS t;

With OUTER => TRUE, the parent row stays in the result even if the array is empty or missing.

Common mistakes

• Flattening raw JSON text before parsing it
  If your JSON is stored as a string, parse it first. FLATTEN works on semi-structured values, so trying to flatten raw text usually creates problems immediately. When the input may be messy, TRY_PARSE_JSON is the safer option.
• Using FLATTEN when path extraction is enough
  Not every JSON query needs flattening. If you only need a field from an object, path notation or GET_PATH() is usually cleaner. FLATTEN should be reserved for arrays that need to become rows.
• Flattening too early in the query
  If you can filter the parent rows first, do that before expanding arrays. It keeps the result set smaller and makes the query easier to reason about.
• Using vague or unreadable aliases
  Short aliases are fine, but they still need to be readable. item, tag, and discount are much clearer than one-letter aliases when someone else has to read the query later.
• Forgetting OUTER => TRUE when row preservation matters
  By default, rows can disappear when the target array is empty or missing. If you need to preserve the parent row, this setting matters.
• Trying to flatten everything recursively by default
  Recursive flattening has its place, but it is rarely the best starting point for reporting queries. Most of the time, it is better to target the specific array you actually need.

How to get flattened Snowflake data into Google Sheets or Excel

Once the SQL works, the next step is usually not more SQL. It is getting the output somewhere the rest of the business can use it.

That is where Coefficient fits naturally. If the final table needs to land in a spreadsheet, Coefficient’s Snowflake connector gives teams a way to bring Snowflake data into Google Sheets, refresh it on a schedule, and skip the manual export cycle.

A simple workflow looks like this:

1. Connect Snowflake with Coefficient’s Snowflake connector to Google Sheets or Excel.
2. Import the result into Google Sheets or Excel
3. Refresh it on a schedule

If you want to try it, get started.

If you still need help extracting values from raw JSON or nested objects before flattening arrays, read How to Query JSON Data in Snowflake.

Conclusion

Most of the confusion around LATERAL FLATTEN comes from using it for the wrong job. If you just need to pull a field from a JSON object, path notation is usually enough. But if your payload contains arrays and you need each element to become its own row, FLATTEN is the right tool.

Once you have that query working, the next challenge is usually getting the result into a format the rest of the team can use. If your workflow ends in a spreadsheet, Coefficient’s Snowflake connector makes it easier to bring flattened Snowflake data into Google Sheets or Excel and keep it refreshed without repeated exports.

FAQs

What is LATERAL FLATTEN in Snowflake?

It is the common pattern used to turn JSON arrays into rows. FLATTEN is the table function, and LATERAL lets it reference the JSON field from each row in the source table.

When should I use FLATTEN in Snowflake?

Use it when you need each element in a JSON array to become its own row.

What does the VALUE column do?

VALUE contains the current array element or object value returned by the flattening step.

What happens if the array is empty?

By default, no rows are returned for that path. Use OUTER => TRUE if you need to preserve the parent row.

Can FLATTEN work on objects too?

Yes. FLATTEN works on VARIANT, OBJECT, or ARRAY values, though most reporting use cases use it for arrays.