Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tbl (
   tbl_id int PRIMARY KEY
 , data   jsonb
);

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
CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
  RETURNS text[]
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS  -- Postgres 9.6+
'SELECT ARRAY(SELECT jsonb_array_elements_text(_js))';

TABLE tbl;

CREATE TABLE

INSERT 0 4

CREATE FUNCTION

tbl_id
data

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

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

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

4
{"name": "none"}

SELECT 4

-- 9.4+ verbose
SELECT t.tbl_id, t.data->'tags' AS jsonb_arr, 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;

tbl_id
jsonb_arr
txt_arr

1
["foo", "bar"]
{foo,bar}

2
[null]
{NULL}

3
[]
null

4
null
null

SELECT 4

-- 9.4+ short syntax
SELECT t.tbl_id, t.data->'tags' AS jsonb_arr, d.txt_arr
FROM   tbl t, LATERAL (
   SELECT array_agg(value) AS txt_arr
   FROM   jsonb_array_elements_text(t.data->'tags')  -- col name default: "value"
   ) d;

tbl_id
jsonb_arr
txt_arr

1
["foo", "bar"]
{foo,bar}

2
[null]
{NULL}

3
[]
null

4
null
null

SELECT 4

-- 9.4+ shorter syntax with ARRAY constructor
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);

tbl_id
jsonb_arr
txt_arr

1
["foo", "bar"]
{foo,bar}

2
[null]
{NULL}

3
[]
{}

4
null
{}

SELECT 4

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

tbl_id
jsonb_arr
txt_arr

1
["foo", "bar"]
{foo,bar}

2
[null]
{NULL}

3
[]
{}

4
null
{}

SELECT 4

-- 9.4+ shortest with custom ARRAY function
-- Also: most accurate
SELECT tbl_id, t.data->'tags' AS jsonb_arr, jsonb_array_to_text_array(t.data->'tags') AS txt_arr
FROM   tbl t;

tbl_id
jsonb_arr
txt_arr

1
["foo", "bar"]
{foo,bar}

2
[null]
{NULL}

3
[]
{}

4
null
null

SELECT 4

----------- Outdated stuff ---------
-- 9.3- string, verbose (alt query)
SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL jsonb_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

tbl_id
list

1
"foo", "bar"

2
null

SELECT 2

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

tbl_id
txt_arr

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

2
{"null"}

3
{}

4
{}

SELECT 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, jsonb_array_elements(t.data->'tags') d(elem)
GROUP  BY 1;

tbl_id
list

1
foo, bar

2
null

SELECT 2

-- 9.3- string, single row
SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, jsonb_array_elements(t.data->'tags') d(elem)
WHERE  t.tbl_id = 1;

list

foo, bar

SELECT 1

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

tbl_id
list

1
foo, bar

2
null

3
null

4
null

SELECT 4

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

tbl_id
txt_arr

1
{foo,bar}

2
{"null"}

3
{}

4
{}

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