When an array lives inside a JSONB column, you cannot filter or join on its items directly: SQL has no way to "step inside" a single value. jsonb_array_elements fixes that by expanding the array into a set of rows, one row per element. It is the JSON analog of UNNEST, and it is where any analytics over nested arrays begins.
One row per element
jsonb_array_elements is a set-returning function. Put it in the FROM clause next to a table with a comma, and each array element becomes its own row. That comma is an implicit LATERAL, so the function can see the columns of the table on its left.
SELECT u.id, e.value AS tag
FROM users u, jsonb_array_elements(u.prefs -> 'tags') AS e;
If prefs -> 'tags' has three elements, each user produces three rows. The default column is named value and holds jsonb. Keep the key properties in mind:
- It returns
jsonb, so e.value is "vip" with quotes, not vip.
- It preserves the order of the array elements.
- An empty array
[] produces no rows at all, so that user drops out of the result.
Filter and join on items
Once the array is expanded, you filter and group on its items like ordinary rows. This is the core move: "find everyone whose array contains X."
SELECT DISTINCT u.id, u.email
FROM users u, jsonb_array_elements(u.prefs -> 'tags') AS e
WHERE e.value = '"vip"';
Note the comparison against '"vip"': that is a jsonb string literal, complete with inner quotes. To write a plain 'vip', reach for the text variant (below). Here is how to expand an order's line items and total revenue by SKU:
SELECT
item ->> 'sku' AS sku,
sum((item ->> 'qty')::int * (item ->> 'price')::numeric) AS revenue
FROM orders o, jsonb_array_elements(o.payload -> 'items') AS item
WHERE o.status = 'paid'
GROUP BY item ->> 'sku'
ORDER BY revenue DESC;
WITH ORDINALITY: get the index
Sometimes the position matters: the first tag, the step in a funnel, the ordinal number. WITH ORDINALITY adds a column with the row number, starting at 1.
SELECT u.id, t.tag, t.pos
FROM users u,
jsonb_array_elements_text(u.prefs -> 'tags') WITH ORDINALITY AS t(tag, pos)
WHERE t.pos <= 2;
Here t(tag, pos) names the outputs: tag is the value, pos is the index. The numbering is 1-based, unlike the arrow operators, where array indexing starts at zero.
The _text variant: no quotes
jsonb_array_elements_text does the same expansion but returns text instead of jsonb. That removes the quoting pain: compare and display the items as plain strings.
SELECT DISTINCT u.id
FROM users u, jsonb_array_elements_text(u.prefs -> 'tags') AS tag
WHERE tag = 'vip';
The rule is simple: an array of strings or numbers, use _text; an array of objects you need to drill into with ->/->>, use the plain jsonb variant.
Gotchas
- An empty array or a SQL
NULL quietly yields no rows. A function in FROM behaves like a CROSS JOIN: zero elements means zero rows, so a user with no tags simply disappears from the result. There is no error here: feeding a SQL NULL into the function returns an empty set, exactly like []. To keep those rows, use LEFT JOIN LATERAL ... ON true.
- A JSON value that is not an array is an error. The empty-set behavior above is only for a SQL
NULL or []. If the JSON value is an object or a scalar, the function raises cannot extract elements from a scalar/object. The trap is the JSON null scalar: jsonb_array_elements('null'::jsonb) fails, because that null is a JSON value that is not an array, not a SQL NULL. Guard it: jsonb_array_elements(COALESCE(col -> 'tags', '[]'::jsonb)).
value versus '"value"'. In the plain variant the element is jsonb, so you compare a string against '"vip"'. Getting this wrong is a common cause of a mysteriously empty result.
SELECT u.id, e.value AS tag
FROM users u
LEFT JOIN LATERAL jsonb_array_elements(COALESCE(u.prefs -> 'tags', '[]'::jsonb)) AS e ON true;
MySQL has no direct equivalent before 8.0: use JSON_TABLE(doc, '$.tags[*]' COLUMNS(tag VARCHAR(50) PATH '$')) to expand an array into rows. ClickHouse leans on arrayJoin(JSONExtractArrayRaw(col)), or stores the data in an Array column and calls arrayJoin directly. The idea is universal -- array into rows -- but jsonb_array_elements is the most direct path in PostgreSQL.
When an array lives inside a
JSONBcolumn, you cannot filter or join on its items directly: SQL has no way to "step inside" a single value.jsonb_array_elementsfixes that by expanding the array into a set of rows, one row per element. It is the JSON analog ofUNNEST, and it is where any analytics over nested arrays begins.One row per element
jsonb_array_elementsis a set-returning function. Put it in theFROMclause next to a table with a comma, and each array element becomes its own row. That comma is an implicitLATERAL, so the function can see the columns of the table on its left.-- users.prefs holds a jsonb array of tags: ["vip", "eu", "beta"] SELECT u.id, e.value AS tag FROM users u, jsonb_array_elements(u.prefs -> 'tags') AS e;If
prefs -> 'tags'has three elements, each user produces three rows. The default column is namedvalueand holdsjsonb. Keep the key properties in mind:jsonb, soe.valueis"vip"with quotes, notvip.[]produces no rows at all, so that user drops out of the result.Filter and join on items
Once the array is expanded, you filter and group on its items like ordinary rows. This is the core move: "find everyone whose array contains X."
-- find users whose tags array contains 'vip' SELECT DISTINCT u.id, u.email FROM users u, jsonb_array_elements(u.prefs -> 'tags') AS e WHERE e.value = '"vip"';Note the comparison against
'"vip"': that is ajsonbstring literal, complete with inner quotes. To write a plain'vip', reach for the text variant (below). Here is how to expand an order's line items and total revenue by SKU:-- orders.payload = {"items": [{"sku": "A1", "qty": 2, "price": 10}, ...]} SELECT item ->> 'sku' AS sku, sum((item ->> 'qty')::int * (item ->> 'price')::numeric) AS revenue FROM orders o, jsonb_array_elements(o.payload -> 'items') AS item WHERE o.status = 'paid' GROUP BY item ->> 'sku' ORDER BY revenue DESC;WITH ORDINALITY: get the index
Sometimes the position matters: the first tag, the step in a funnel, the ordinal number.
WITH ORDINALITYadds a column with the row number, starting at 1.-- keep only the first two tags per user, with their position SELECT u.id, t.tag, t.pos FROM users u, jsonb_array_elements_text(u.prefs -> 'tags') WITH ORDINALITY AS t(tag, pos) WHERE t.pos <= 2;Here
t(tag, pos)names the outputs:tagis the value,posis the index. The numbering is 1-based, unlike the arrow operators, where array indexing starts at zero.The _text variant: no quotes
jsonb_array_elements_textdoes the same expansion but returnstextinstead ofjsonb. That removes the quoting pain: compare and display the items as plain strings.-- compare with plain 'vip', no JSON quoting needed SELECT DISTINCT u.id FROM users u, jsonb_array_elements_text(u.prefs -> 'tags') AS tag WHERE tag = 'vip';The rule is simple: an array of strings or numbers, use
_text; an array of objects you need to drill into with->/->>, use the plainjsonbvariant.Gotchas
NULLquietly yields no rows. A function inFROMbehaves like aCROSS JOIN: zero elements means zero rows, so a user with no tags simply disappears from the result. There is no error here: feeding a SQLNULLinto the function returns an empty set, exactly like[]. To keep those rows, useLEFT JOIN LATERAL ... ON true.NULLor[]. If the JSON value is an object or a scalar, the function raisescannot extract elements from a scalar/object. The trap is the JSONnullscalar:jsonb_array_elements('null'::jsonb)fails, because thatnullis a JSON value that is not an array, not a SQLNULL. Guard it:jsonb_array_elements(COALESCE(col -> 'tags', '[]'::jsonb)).valueversus'"value"'. In the plain variant the element isjsonb, so you compare a string against'"vip"'. Getting this wrong is a common cause of a mysteriously empty result.-- keep every user, even those with an empty or missing tags array SELECT u.id, e.value AS tag FROM users u LEFT JOIN LATERAL jsonb_array_elements(COALESCE(u.prefs -> 'tags', '[]'::jsonb)) AS e ON true;MySQL has no direct equivalent before 8.0: use
JSON_TABLE(doc, '$.tags[*]' COLUMNS(tag VARCHAR(50) PATH '$'))to expand an array into rows. ClickHouse leans onarrayJoin(JSONExtractArrayRaw(col)), or stores the data in anArraycolumn and callsarrayJoindirectly. The idea is universal -- array into rows -- butjsonb_array_elementsis the most direct path in PostgreSQL.