Postgres 9.3
You can use the function json_array_elements()
to unnest the JSON array and array_agg()
to build an actual text
array from it. Or string_agg()
to build a (comma-separated) text string.
You need columns to uniquely identify each row. The primary key would be your natural choice. If you should not have one, the ctid
can be your surrogate primary key for the purpose of this query.
Text array with double-quoted values:
SELECT t.tbl_id, string_agg(elem::text, ', ') AS list
FROM tbl t, json_array_elements(t.data->'tags') elem
GROUP BY 1;
This is making use of the implicit LATERAL JOIN
in Postgres 9.3 or later.
Poor man's unquoting with trim()
:
SELECT t.tbl_id, string_agg(trim(elem::text, '"'), ', ') AS list
FROM tbl t, json_array_elements(t.data->'tags') elem
GROUP BY 1;
Extract a single value:
SELECT string_agg(trim(elem, '"'), ', ') AS list
FROM tbl t, json_array_elements(t.data->'tags') elem
WHERE tbl_id = 1;
Or even simpler and faster (update): use a correlated subquery:
SELECT tbl_id, (SELECT string_agg(trim(elem::text, '"'), ', ')
FROM json_array_elements(data->'tags') elem) AS list
FROM tbl;
Simpler yet with an ARRAY constructor to produce an actual array:
SELECT tbl_id, ARRAY(SELECT trim(elem::text, '"')
FROM json_array_elements(data->'tags') elem) AS txt_arr
FROM tbl;
SQL Fiddle.
Note:
We would need a json_array_elements_text(json)
, the twin of json_array_elements(json)
to return proper text
values from a JSON array. But that seems to be missing from the provided arsenal of JSON functions. Or some other function to extract a text
value from a scalar JSON
value. I seem to be missing that one, too.
So I improvised with trim()
, but that will fail for non-trivial cases ...
Postgres 9.4
Obviously inspired by this post, the Postgres 9.4 provides exactly the function I was wishing for: json_array_elements_text(json)
and jsonb_array_elements_text(jsonb)
.
Thanks to Laurence Rowe for providing a patch and Andrew Dunstan for committing it!
So:
SELECT t.tbl_id, string_agg(elem, ', ') AS list
FROM tbl t, json_array_elements_text(t.data->'tags') elem
GROUP BY 1;
Or jsonb_array_elements_text(jsonb)
for jsonb
.
Related answer on SO.
Or with ARRAY constructor like demonstrated for pg 9.3:
SELECT tbl_id, ARRAY(SELECT jsonb_array_elements_text(data->'tags')) AS txt_arr
FROM tbl;
Encapsulated in a function
If you need this often and the above expression is still not simple enough, you might encapsulate the logic in a simple function:
CREATE OR REPLACE FUNCTION jsonb_arr2text_arr(_js jsonb)
RETURNS text[] AS
$func$
SELECT ARRAY(SELECT jsonb_array_elements_text(_js))
$func$
LANGUAGE sql IMMUTABLE;
Make it an SQL function, so it can be inlined in bigger queries.
Make it IMMUTABLE
(because it is) to avoid repeated evaluation in bigger queries and allow to use it in index expressions.
Then:
SELECT tbl_id, jsonb_arr2text_arr(data->'tags')
FROM tbl;
json_extract_path_text(your_column, 'tags')
what you are looking for? – a_horse_with_no_name Dec 2 '13 at 21:45