Flattening JSON in SQL With Lateral Joins in PostgreSQL

SQL joins rows to rows. A JSON array is a set of values inside one row. To bring them together, you need a join in which the right side is computed from each row on the left, which is what LATERAL provides. The idea takes a minute to learn, and then every JSON flattening query in PostgreSQL has the same shape. This article builds the queries on 20,000 orders and 5,000 blog posts, covers the variants you will actually use (typed columns, position, empty arrays), shows the errors for input that is not an array, and ends with the MySQL and SQLite equivalents so you can compare.

The basic shape

SELECT o.doc->>'id' AS order_id,
       i.item->>'sku' AS sku,
       (i.item->>'qty')::int AS qty,
       (i.item->>'price')::numeric AS price
FROM raw_orders o
CROSS JOIN LATERAL jsonb_array_elements(o.doc->'items') AS i(item);

jsonb_array_elements(x) is a set-returning function: given a JSON array it returns one row per element, in a column named value unless you alias it as above (AS i(item)). LATERAL allows the function call to refer to o, the row from the left side. Without it, the right side of a join cannot see the left. In PostgreSQL a function in the FROM list is treated as lateral automatically, so FROM raw_orders o, jsonb_array_elements(o.doc->'items') AS i(item) works the same way, and many people write it as a comma join. I prefer to write CROSS JOIN LATERAL, because the word tells the reader what is going on.

The query returned 50,000 rows. Wrapped in count(*) and sum((i.item->>'qty')::int * (i.item->>'price')::numeric) it gave 50000 and 6375490.00 in 0.20 s, the same count and total as the pandas flattening of the same data (exploding a JSON array into one row per item).

Note the operators: -> returns JSON, ->> returns text. Every value pulled out of an element needs a cast to become a typed column, which is verbose and easy to get wrong. That is the reason for the next function.

Typed columns with jsonb_to_recordset

SELECT o.doc->>'id' AS order_id, i.*
FROM raw_orders o
CROSS JOIN LATERAL jsonb_to_recordset(o.doc->'items') AS i(sku text, qty int, price numeric);

jsonb_to_recordset expands an array of objects and picks the named keys into typed columns, so the casts are done once, in the column list. A missing key becomes NULL, and extra keys are ignored. The aggregate query returned the same 50,000 rows and 6375490.00 in 0.07 s, against 0.20 s for the version with casts (one warm run each, so read it as the same order of magnitude and not as a benchmark). A value that cannot be cast, say a string "slow" for an integer column, raises an error that stops the query, so on dirty data you want a text column first and a CASE or a nullif after it. If one element is not an object, this function raises as well. The sibling jsonb_to_record does the same for a single object.

For arrays of scalars use jsonb_array_elements_text, which returns text and spares the #>>'{}' incantation:

SELECT tg, count(*)
FROM raw_posts p
CROSS JOIN LATERAL jsonb_array_elements_text(p.doc->'tags') AS tg
GROUP BY 1 ORDER BY 2 DESC LIMIT 3;

That returned postgres 1,024, python 1,014 and mysql 1,011, from the 9,964 tag entries across 5,000 posts.

The position inside the array

WITH ORDINALITY numbers the rows of a set-returning function from 1:

FROM raw_orders o
CROSS JOIN LATERAL jsonb_array_elements(o.doc->'items') WITH ORDINALITY AS i(item, n)

Ordered by order id and n, the first rows were order 1 with items 1 (S2) and 2 (S3), then order 2 with items 1 (S3) and 2 (S4). Keep the position if order inside the array carries meaning (steps in a workflow, lines on an invoice). If you drop it when flattening, you cannot reconstruct the original, and two identical elements become indistinguishable. A parent id and the position together are a natural primary key for the child table.

Keeping parents with empty arrays

CROSS JOIN LATERAL is an inner join. A parent whose array is empty has no rows on the right, and so it has no rows in the result:

SELECT count(*) FROM raw_posts p
CROSS JOIN LATERAL jsonb_array_elements(p.doc->'comments') c;            -- 9183

SELECT count(*) AS left_rows, count(c.value) AS real_comments
FROM raw_posts p
LEFT JOIN LATERAL jsonb_array_elements(p.doc->'comments') c ON true;     -- 10795 | 9183

The first query returned 9,183 rows from 3,388 posts. The second kept all 5,000 posts: 10,795 rows, of which 1,612 are posts with NULL in the comment columns. ON true is required syntax; there is no condition to join on, since the function is already tied to the row. Use count(c.value), not count(*), when you want the number of real children: count(column) skips NULLs, and count(*) counts the placeholder rows too.

Input that is not an array

JSON data is irregular, and the function reacts differently to each kind of irregularity:

jsonb_array_elements(NULL)                  -> no rows
jsonb_array_elements('{"a":1}')             -> ERROR:  cannot extract elements from an object
jsonb_array_elements('null'::jsonb)         -> ERROR:  cannot extract elements from a scalar
jsonb_array_elements('[]')                  -> no rows

A missing key makes doc->'missing' SQL NULL, and the function is strict, so it returns nothing and the row drops out quietly (all 5,000 posts gave zero rows for a key none of them had). But a key whose value is JSON null is not SQL NULL, and it raises. If the same field is an array in some documents and an object or a null in others, the query dies on the first odd one. Guard it with the type:

CROSS JOIN LATERAL jsonb_array_elements(
  CASE WHEN jsonb_typeof(o.doc->'items') = 'array' THEN o.doc->'items' ELSE '[]'::jsonb END
) AS i(item)

and count the exceptions separately (WHERE jsonb_typeof(doc->'items') <> 'array') so they are reported and not hidden. Hiding malformed documents behind an empty array is a decision about data quality; make it in one place.

Filtering inside the array

When you only want some elements, filter after the expansion (WHERE (i.item->>'qty')::int > 3), or filter inside with a jsonpath query that returns just the matching elements:

SELECT count(*) FROM raw_orders o
CROSS JOIN LATERAL jsonb_path_query(o.doc, '$.items[*] ? (@.qty > 3)') x;

It returned 20,000 matching items out of 50,000. The jsonpath form is concise for conditions on nested values and lets you reach arrays at any depth with $.a[*].b[*], which replaces two chained laterals (PostgreSQL SQL/JSON path expressions). For a plain filter on the parent, put it in WHERE before the expansion, so the database expands fewer documents.

What the plan looks like

EXPLAIN (ANALYZE) on the count query showed:

Function Scan on jsonb_array_elements i (actual rows=2.50 loops=20000)
Execution Time: 27.790 ms

The function is called once per parent row (loops=20000) and returns 2.5 rows on average. There are no indexes to use, since the planner cannot index into elements it has not produced. The total was 28 ms for 20,000 documents, which is fast because the documents are small and already parsed (jsonb is stored in a binary form). For very large documents, the cost is dominated by reading and detoasting each one, and a filter on an indexed expression that eliminates documents first is the best optimization (indexing JSONB with GIN).

The same thing in MySQL and SQLite

MySQL's equivalent is JSON_TABLE, which combines the expansion and the typing in one clause:

SELECT o.doc->>'$.id' AS order_id, jt.n, jt.sku
FROM raw_orders o,
     JSON_TABLE(o.doc, '$.items[*]' COLUMNS (
       n   FOR ORDINALITY,
       sku varchar(10) PATH '$.sku',
       qty int         PATH '$.qty'
     )) jt;

It returned 50,000 rows for the items query and 6,375,490.00 as the total in 0.06 s. A DEFAULT '"none"' ON EMPTY clause replaced a missing key with a value, and ERROR ON ERROR on an integer column pointing at a string raised Invalid JSON value for CAST to INTEGER from column t at row 1, while the default behavior would have returned NULL. LEFT JOIN JSON_TABLE(...) AS jt ON TRUE kept the empty-array posts (10,795 rows). SQLite uses json_each, which returns a row per element with key (the index for arrays), value, and type:

SELECT count(*), sum(json_extract(i.value,'$.qty') * json_extract(i.value,'$.price'))
FROM raw_orders o, json_each(o.doc, '$.items') i;
-- 50000 | 6375490.0

All three engines agree on row counts and totals, and differ in where the typing happens: PostgreSQL in the function's column list, MySQL in the COLUMNS clause, SQLite in the expressions around json_extract (JSON_TABLE in MySQL, expanding JSON arrays with json_each).

When a lateral join is the wrong shape

You need an aggregate, not rows. SELECT doc->>'id', (SELECT sum(...) FROM jsonb_array_elements(doc->'items') ...) computes the order total without producing an item table, and cannot duplicate a total through a join.

The array is the whole table. If every document is a wrapper around one big array, you may be better off converting the file to JSON Lines before loading, so each row is already an element.

You run this query often on a large table. The expansion repeats on every query. Materialize it once into a real table (CREATE TABLE items AS SELECT ...) with keys and indexes, and query that (normalizing a JSON export into relational tables).

The functions are documented in the PostgreSQL manual under JSON processing functions, and the lateral concept in LATERAL subqueries. To check what an element looks like, paste it into the JSON formatter.