How to turn JSON array into Postgres array?

I have a column data of type json that holds JSON documents like this:

{
    "name": "foo",
    "tags": ["foo", "bar"]
}

I would like to turn the nested tags array into a concatenated string ('foo, bar'). That would be easily possible with the array_to_string() function in theory. However, this function does not accept json input. So I wonder how to turn this JSON array into a Postgres array (type text[])?

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!

• json_array_elements_text(json)

• jsonb_array_elements_text(jsonb)

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
BEGIN ATOMIC
SELECT ARRAY(SELECT jsonb_array_elements_text(_js));
END;

See:

• What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

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:

• When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

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:

• How to apply ORDER BY and LIMIT in combination with an aggregate function?

• What is the difference between LATERAL and a subquery in PostgreSQL?

• How to apply ORDER BY and LIMIT in combination with an aggregate function?

• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

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

StackExchange Database Administrators Q#54283, answer score: 181