How do you turn a JSONB array into rows in PostgreSQL?
SHORT ANSWER
Use jsonb_array_elements (or jsonb_array_elements_text for plain strings) in the FROM clause, usually with a lateral join. Each array element becomes one row you can filter, join or aggregate.
Explain it simply
A JSONB array is a packed parcel of items. jsonb_array_elements unpacks the parcel onto the table, one item per row, so ordinary SQL such as WHERE and GROUP BY can work on them.
Example
sql
CREATE TABLE orders (id int, doc jsonb);
INSERT INTO orders VALUES
(1, '{"items":[{"sku":"A1","qty":2},{"sku":"B7","qty":1}]}'),
(2, '{"items":[{"sku":"A1","qty":5}]}');
SELECT o.id, item ->> 'sku' AS sku, (item ->> 'qty')::int AS qty
FROM orders o,
jsonb_array_elements(o.doc -> 'items') AS item;
-- id | sku | qty
-- 1 | A1 | 2
-- 1 | B7 | 1
-- 2 | A1 | 5
-- total quantity per sku
SELECT item ->> 'sku' AS sku, sum((item ->> 'qty')::int)
FROM orders, jsonb_array_elements(doc -> 'items') AS item
GROUP BY 1;
-- A1 -> 7, B7 -> 1The comma in FROM is an implicit lateral join, so the function can refer to the orders row. Add WITH ORDINALITY to also get each element's position.
What to say in the interview
"I unpack JSONB arrays with jsonb_array_elements in a lateral join, then cast fields with ->> and aggregate as normal. If I do this often on the same data, it tells me the array deserves its own table."
Common mistakes
- Using jsonb_array_elements on a column that may hold a non-array, which raises an error.