Postgres 11

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   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 AS
'SELECT ARRAY(SELECT json_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+ 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

SELECT 4

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

SELECT 4

-- 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
{}

SELECT 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
{}

SELECT 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

SELECT 2

-- 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
{}

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, json_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, json_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   json_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   json_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"}