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