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)
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:

Need to select a JSON array element dynamically from a PostgreSQL table

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 ...
