SQLearnAcademy
←Function reference
JSONPostgreSQL only

JSONB_ARRAY_ELEMENTS

Expands a JSON array into rows — one row per element.

Syntax

JSONB_ARRAY_ELEMENTS(<json array>)
JSONB_ARRAY_ELEMENTS_TEXT(<json array>)

Like UNNEST, but for JSON: from one array it makes as many rows as the array has elements. Each element stays jsonb; if you need text, use JSONB_ARRAY_ELEMENTS_TEXT. Handy when the application stores a list in a JSON field and in SQL you need to work with it like a table.

An array into rows
SELECT JSONB_ARRAY_ELEMENTS('["sql", "postgres", "database"]') AS tag;
tag
sql
postgres
database

The result table shows sql without quotes, but each element is jsonb: in psql it would look like "sql".

Objects from an array into columns
SELECT item ->> 'name' AS name,
(item ->> 'salary')::int AS salary
FROM JSONB_ARRAY_ELEMENTS('[{"name": "Emma", "salary": 1200}, {"name": "Liam", "salary": 1000}]') AS item;
namesalary
Emma1200
Liam1000
As text — for filtering
SELECT tag
FROM JSONB_ARRAY_ELEMENTS_TEXT('["sql", "postgres"]') AS tag
WHERE tag LIKE 'p%';
tag
postgres

LIKE works with text, hence _TEXT here. With the ordinary JSONB_ARRAY_ELEMENTS you'd get operator does not exist: jsonb ~~ unknown.

Watch out

The elements stay jsonb, so comparing with text breaks: WHERE tag = 'sql' throws invalid input syntax for type json. Use JSONB_ARRAY_ELEMENTS_TEXT.

Not an array — an error: for '{"a": 1}' you'll get cannot extract elements from an object.

An empty array or NULL gives zero rows, so in a query with another table such a row disappears, just as with UNNEST.