sqlindex

JSON

Expand a JSON array into rows

3 min · updated June 16, 2026

A column holds {"items":[{"sku":"A"},{"sku":"B"}]} and you want one row per item. Each engine has its own table-valued function.

-- PostgreSQL (jsonb)
SELECT e.id, elem ->> 'sku' AS sku
FROM events e,
     jsonb_array_elements(e.data -> 'items') AS elem;

-- MySQL 8+
SELECT e.id, j.sku
FROM events e,
     JSON_TABLE(e.data, '$.items[*]'
       COLUMNS (sku VARCHAR(50) PATH '$.sku')) AS j;

-- SQLite (json1)
SELECT e.id, json_extract(j.value, '$.sku') AS sku
FROM events e,
     json_each(e.data, '$.items') AS j;

-- BigQuery
SELECT e.id, JSON_VALUE(item, '$.sku') AS sku
FROM events e,
     UNNEST(JSON_EXTRACT_ARRAY(e.data, '$.items')) AS item;

-- Snowflake (VARIANT)
SELECT e.id, f.value:sku::string AS sku
FROM events e,
     LATERAL FLATTEN(input => e.data:items) AS f;

-- DuckDB
SELECT e.id, item ->> 'sku' AS sku
FROM events e,
     UNNEST(CAST(e.data -> 'items' AS JSON[])) AS t(item);

It’s a lateral join. Each form joins every base row to the elements of its own array — the comma in FROM events e, jsonb_array_elements(...) is an implicit LATERAL/CROSS JOIN. Rows whose array is empty or NULL drop out (it’s an inner join); use LEFT JOIN LATERAL (Postgres) or LEFT JOIN UNNEST(...) (BigQuery) / OUTER => TRUE in Snowflake’s FLATTEN to keep them.

← All recipes