clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 462196 distinct fiddles created so far.

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;
4 rows affected
tbl_id data
1 {"name": "foo", "tags": ["foo", "bar"]}
2 {"name": "empty", "tags": [null]}
3 {"name": "empty", "tags": []}
4 {"name": "none"}
 hidden batch(es)


-- 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
3
4
 hidden batch(es)


-- 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
3
4
 hidden batch(es)


-- 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 {}
 hidden batch(es)


-- 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 {}
 hidden batch(es)


-- 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
 hidden batch(es)


-- 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 {}
 hidden batch(es)


-- 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
 hidden batch(es)


-- 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
 hidden batch(es)


-- 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
4
 hidden batch(es)


-- 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 {}
 hidden batch(es)