How to Flatten JSON in Snowflake
Last updated July 25, 2026 · By the SaturnSQL team
Use LATERAL FLATTEN(input => col) to turn each element of a VARIANT array into its own row. Extract fields with the :key path syntax and cast them with ::type, since VARIANT values come back untyped.
Explode a JSON array into rows
SELECT
e.id,
f.value:sku::VARCHAR AS sku,
f.value:qty::INTEGER AS qty
FROM events e,
LATERAL FLATTEN(input => e.payload:items) f;Extract scalar fields without flattening
Plain field access needs no FLATTEN; use the colon path and a cast.
SELECT
payload:user.email::VARCHAR AS email,
payload:created_at::TIMESTAMP_NTZ AS created_at
FROM events;Add OUTER => TRUE to FLATTEN to keep rows whose array is empty or NULL; otherwise they are dropped, like an inner join.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free