Postgres 10 or newer
Postgres 10 improved the functionality of jsonb_to_record()
and friends. Now even multi-dimensional JSON arrays can be converted efficiently. Quoting the release notes:
Make json_populate_record()
and related functions process JSON arrays and objects recursively (Nikita Glukhov)
With this change, array-type fields in the destination SQL type are
properly converted from JSON arrays, and composite-type fields are
properly converted from JSON objects. Previously, such cases would
fail because the text representation of the JSON value would be fed to
array_in()
or record_in()
, and its syntax would not match what
those input functions expect.
jsonb_to_record()
requires a column definition list, where the output column name(s) must match the key(s), so move it to the FROM
clause. Additional benefit: you can coerce to an element type of your choice right away.
SELECT tbl_id, t.data -> 'tags' AS jsonb_arr, j.tags AS txt_arr
FROM tbl t
LEFT JOIN LATERAL jsonb_to_record(t.data) AS j(tags text[]) ON true;
fiddle
Or json_to_record()
for json
input.
The LEFT JOIN
is optional to preserve all input rows.
If you still want to use a drop-in function, here is a shorter version with the SQL-standard syntax (Postgres 14+):
CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
RETURNS text[]
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN ARRAY(SELECT jsonb_array_elements_text(_js));
Details below.
Postgres 9.6 or older
For multi-dimensional arrays see:
Postgres 9.4 or newer
Inspired by this post, Postgres 9.4 added the missing functions to unnest JSON arrays.
Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!
Use array_agg()
or an ARRAY constructor to build a Postgres array (type text[]
) from the resulting set of text
.
Or string_agg()
to build a string with a list of values (type text
).
Focusing on array output (text[]
), not string (text
). Important difference: null
elements are preserved in actual arrays. This is not possible in a string, which cannot contain null
values. The true representation is an array.
Replace 'jsonb' with 'json' for type json
in all following SQL code.
TLDR: Use a custom function
Encapsulate the logic in a function for repeated use:
CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
RETURNS text[]
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';
In Postgres 14 or later, consider the new SQL standard form:
CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
RETURNS text[]
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN ARRAY(SELECT jsonb_array_elements_text(_js));
See:
Call (same for either function):
SELECT tbl_id, jsonb_array_to_text_array(data->'tags')
FROM tbl;
LANGUAGE sql
for the simple function. (Fastest in my latest tests with Postgres 14.)
IMMUTABLE
(because it is) to avoid repeated evaluation in bigger queries and allow its use in index expressions.
STRICT
to return null
for null
input. Also: faster. The function cannot be inlined anyway because of the ARRAY constructor / the aggregate function, so STRICT
cannot harm that.
PARALLEL SAFE
(in Postgres 9.6 or later!) to allow parallel execution in big queries. See:
This function with a STRICT
modifier is also as true to the original as possible in that it returns null
for null
input and an empty array for empty array input. Better than all of the below queries.
For completeness: use to_jsonb()
for the reverse SQL array → jsonb
conversion.
Various solutions, step-by-step
Immediately aggregate per row in a LATERAL
or correlated subquery, then original order is preserved and we don't need ORDER BY
, GROUP BY
or even a unique key in the outer query. See:
Basic query, returns null
for empty array or null
input:
SELECT t.tbl_id, d.txt_arr
FROM tbl t
CROSS JOIN LATERAL (
SELECT array_agg(d.elem) AS txt_arr
FROM jsonb_array_elements_text(t.data->'tags') AS d(elem)
) AS d;
Short syntax, returns null
for empty array or null
input:
SELECT t.tbl_id, d.txt_arr
FROM tbl t, LATERAL (
SELECT array_agg(value) AS txt_arr
FROM jsonb_array_elements_text(t.data->'tags') -- default name is "value"
) d;
Shorter (and faster) with ARRAY constructor, returns empty array for empty array or null
input:
SELECT t.tbl_id, t.data->'tags' AS jsonb_arr, d.txt_arr
FROM tbl t, LATERAL (
SELECT ARRAY(SELECT jsonb_array_elements_text(t.data->'tags'))
) d(txt_arr);
Even shorter (and faster) with correlated subquery, returns empty array for empty array or null
input:
SELECT tbl_id, ARRAY(SELECT jsonb_array_elements_text(t.data->'tags')) AS txt_arr
FROM tbl t;
db<>fiddle here
All of the above preserve original order of elements.
Postgres 9.3 or older
Use the function json_array_elements()
. But we get double quoted strings from it.
Alternative query with aggregation in the outer query. CROSS JOIN
removes rows with missing or empty arrays. May also be useful for processing elements. We need a unique key to aggregate:
SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM tbl t
CROSS JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP BY t.tbl_id;
ARRAY constructor, still with quoted strings:
SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM tbl t;
Note that null
is converted to the text value "null", unlike above. Incorrect, strictly speaking, and potentially ambiguous.
Poor man's unquoting with trim()
:
SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP BY 1;
Retrieve a single row from tbl:
SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE t.tbl_id = 1;
Strings form correlated subquery:
SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
FROM json_array_elements(t.data->'tags')) AS list
FROM tbl t;
ARRAY constructor:
SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
FROM json_array_elements(t.data->'tags')) AS txt_arr
FROM tbl t;
db<>fiddle here
Old sqlfiddle
Related:
Original notes (outdated since pg 9.4)
We would need a json_array_elements_text(json)
, the twin of json_array_elements(json)
to return proper text
values from a JSON array. But that seems to be missing from the provided arsenal of JSON functions. Or some other function to extract a text
value from a scalar json
value. I seem to be missing that one, too.
So I improvised with trim()
, but that will fail for non-trivial cases ...