LATERAL FLATTENJSON
Buy Me A Coffee

Expanding an array or JSON array column into multiple rows using a lateral (correlated) join, common in analytical SQL dialects.

A join where the right side can reference columns from the left side row-by-row, enabling per-row computation. Particularly powerful for top-N per group, row-level calculations, and converting arrays to rows. Can be expensive -- test on large datasets.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 No FLATTEN function. Use JSON_TABLE to shred a JSON array into rows — it is the closest equivalent.
PostgreSQL ✓ Supported 9.3 Use json_array_elements() or jsonb_array_elements() as a set-returning function in a LATERAL join. For native arrays, use UNNEST().
SQL Server ✓ Supported 2016 Use OPENJSON() with CROSS APPLY. No dedicated FLATTEN; OPENJSON shreds a JSON string into rows.
Oracle ✓ Supported 12c Use JSON_TABLE (12c+) to expand JSON arrays. For nested tables (Oracle collection types), use TABLE() in a CROSS JOIN.
SQLite ✓ Supported 3.38.0 json_each() and json_tree() are table-valued functions that shred JSON arrays into rows.

Details

LATERAL FLATTEN is a Snowflake/DuckDB/BigQuery-native term, but every engine here has an equivalent mechanism under a different name. PostgreSQL's unnest() (native arrays) and jsonb_array_elements() (JSON arrays) are the most ergonomic. SQL Server's OPENJSON and Oracle/MySQL's JSON_TABLE share a more verbose column-definition syntax inherited from the SQL/JSON standard. SQLite's json_each() is clean and compact. The fundamental pattern is the same everywhere: a set-returning (table-valued) function is cross-joined laterally to the source row, producing one output row per array element. Performance note: these operations can be expensive on large JSON payloads; storing arrays as normalized rows is still preferable for high-frequency queries.

Standard Syntax

-- PostgreSQL SELECT t.id, elem FROM t, LATERAL jsonb_array_elements_text(t.tags) AS elem;

Version Support

MySQL: Since 8.0 PostgreSQL: Since 9.3 SQL Server: Since 2016 Oracle: Since 12c SQLite: Since 3.38.0

Per-Database Syntax & Notes

MySQL

No FLATTEN function. Use JSON_TABLE to shred a JSON array into rows — it is the closest equivalent.

SELECT t.id, jt.tag FROM t, JSON_TABLE(t.tags, '$[*]' COLUMNS (tag VARCHAR(100) PATH '$')) jt;

PostgreSQL

Use json_array_elements() or jsonb_array_elements() as a set-returning function in a LATERAL join. For native arrays, use UNNEST().

-- JSON array: SELECT t.id, elem FROM t, LATERAL jsonb_array_elements_text(t.tags) AS elem; -- Native array column: SELECT t.id, unnested FROM t, UNNEST(t.tags_array) AS unnested;

SQL Server

Use OPENJSON() with CROSS APPLY. No dedicated FLATTEN; OPENJSON shreds a JSON string into rows.

SELECT t.id, j.value FROM t CROSS APPLY OPENJSON(t.tags) AS j;

Oracle

Use JSON_TABLE (12c+) to expand JSON arrays. For nested tables (Oracle collection types), use TABLE() in a CROSS JOIN.

SELECT t.id, jt.tag FROM t, JSON_TABLE(t.tags, '$[*]' COLUMNS (tag VARCHAR2(100) PATH '$')) jt;

SQLite

json_each() and json_tree() are table-valued functions that shred JSON arrays into rows.

SELECT t.id, e.value FROM t, json_each(t.tags) e;