Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (
   tbl_id int PRIMARY KEY
 , data   json
);

INSERT INTO tbl VALUES
   (1, '{"name": "foo", "tags": ["foo", "bar"]}')
 , (2, '{"name": "empty", "tags": [null]}')  -- null element
 , (3, '{"name": "empty", "tags": []}')      -- empty array
 , (4, '{"name": "none"}');                  -- no 'tags' key

-- function, see below 
 CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
  RETURNS text[] LANGUAGE sql IMMUTABLE PARALLEL SAFE  -- Postgres 9.6+
  AS
 'SELECT ARRAY(SELECT json_array_elements_text(_js))';

 TABLE tbl;
4 rows affected



tbl_id
data


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

2
{"name": "empty", "tags": [null]}

3
{"name": "empty", "tags": []}

4
{"name": "none"}


-- 9.4+ string, verbose
SELECT t.tbl_id, d.list
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT string_agg(d.elem::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags') AS d(elem)
   ) d;


tbl_id
list


1
foo, bar

2
null

3
null

4
null


-- 9.4+ string, short syntax
SELECT t.tbl_id, d.list
FROM   tbl t, LATERAL (
   SELECT string_agg(value::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags')  -- col name default: "value"
   ) d;


tbl_id
list


1
foo, bar

2
null

3
null

4
null


-- 9.4+ ARRAY constructor in correlated subquery
SELECT tbl_id, ARRAY(SELECT json_array_elements_text(t.data->'tags')) AS txt_arr
FROM   tbl t;


tbl_id
txt_arr


1
{foo,bar}

2
{NULL}

3
{}

4
{}


-- 9.4+ custom ARRAY function
SELECT tbl_id, json_arr2text_arr(t.data->'tags') AS txt_arr
FROM   tbl t;


tbl_id
txt_arr


1
{foo,bar}

2
{NULL}

3
{}

4
{}


-- 9.3- string, verbose (alt query)
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;


tbl_id
list


1
"foo", "bar"

2
null


-- 9.3- ARRAY constructor in correlated subquery
SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS txt_arr
FROM   tbl t;


tbl_id
txt_arr


1
{""foo"",""bar""}

2
{"null"}

3
{}

4
{}


-- 9.3- string, 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;


tbl_id
list


1
foo, bar

2
null


-- 9.3- string, single row
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;


list


foo, bar


-- 9.3- string, correlated subquery
SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
                FROM   json_array_elements(t.data->'tags')) AS list
FROM   tbl t;


tbl_id
list


1
foo, bar

2
null

3
null

4
null


-- 9.3- ARRAY constructor in correlated subquery
SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
                     FROM   json_array_elements(t.data->'tags')) AS txt_arr
FROM   tbl t;


tbl_id
txt_arr


1
{foo,bar}

2
{"null"}

3
{}

4
{}
tbl_id	data
1	{"name": "foo", "tags": ["foo", "bar"]}
2	{"name": "empty", "tags": [null]}
3	{"name": "empty", "tags": []}
4	{"name": "none"}