Retrieve a single row from tbl: SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list Poor man's unquoting with trim(): SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS listįROM tbl t, json_array_elements(t.data->'tags') d(elem) Incorrect, strictly speaking, and potentially ambiguous. Note that null is converted to the text value "null", unlike above. We need a unique key to aggregate: SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS listĬROSS JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)ĪRRAY constructor, still with quoted strings: SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr May also be useful for processing elements. CROSS JOIN removes rows with missing or empty arrays. But we get double quoted strings from it.Īlternative query with aggregation in the outer query. SELECT ARRAY(SELECT jsonb_array_elements_text(t.data->'tags'))Įven shorter (and faster) with correlated subquery, returns empty array for empty array or null input: SELECT tbl_id, ARRAY(SELECT jsonb_array_elements_text(t.data->'tags')) AS txt_arrĪll of the above preserve original order of elements. Shorter (and faster) with ARRAY constructor, returns empty array for empty array or null input: SELECT t.tbl_id, t.data->'tags' AS jsonb_arr, d.txt_arr Short syntax, returns null for empty array or null input: SELECT t.tbl_id, d.txt_arrįROM jsonb_array_elements_text(t.data->'tags') - default name is "value"
0 Comments
Leave a Reply. |