PostgreSQL 18 reference · JSON Functions
PostgreSQL JSONB_ARRAY_ELEMENTS() Function
Expand a JSON array into one row per element. jsonb_array_elements_text() returns the elements as text.
PostgreSQL 18 Since 9.4
Syntax
SELECT * FROM jsonb_array_elements(jsonb) | jsonb_array_elements_text(jsonb)
Parameters
| Parameter | Description |
|---|---|
jsonb |
A jsonb (or json) value. |
Return value
A set of jsonb (or text) rows.
Examples
SELECT value FROM jsonb_array_elements('[1, "two", {"three":3}]');
Output
value
--------------
1
"two"
{"three": 3}
(3 rows)Run this on your computer — see “Get Started” for setup.
WITH posts(id, tags) AS (VALUES (1, '["sql","json"]'::jsonb), (2, '["sql"]'))
SELECT tag, count(*) AS posts
FROM posts, jsonb_array_elements_text(tags) AS tag
GROUP BY tag
ORDER BY tag;
Output
tag | posts ------+------- json | 1 sql | 2 (2 rows)
Run this on your computer — see “Get Started” for setup.
Version history
| Version | Change |
|---|---|
| PostgreSQL 9.4 | Added |
In other languages
JSON_EACH()· SQLite
Related
JSONB_EACH()· Expand a JSON object into one row per key, with key and value columns. jsonb_each_text() returns text values.JSONB_ARRAY_LENGTH()· Return the number of elements in a JSON array. Raises an error for objects and scalars.UNNEST()· Expand an array into a set of rows, one per element. With several arrays it zips them into columns.- SQL String Functions · tutorial