I am accessing an array (a json object called 'choice_values') in a jsonb field, and would like to parse its contents into a comma-separated text field.
SELECT
jsonb_array_elements(doc -> 'form_values' -> '8189' -> 'choice_values')
FROM
field_data.exports;
That jsonb_array_elements function returns a "setof text", which I would like converted to a comma separated list of the array values, contained within a single field.
Thank you.
3 Answers
Set returning functions (like jsonb_array_elements_text()) can be called in SELECT list, but then they cannot be used in aggregate functions.
This is a good practice to call set returning functions in FROM clause, often in a lateral join like in this example:
with the_data as (
select '["alfa", "beta", "gamma"]'::jsonb as js
)
select string_agg(elem, ',')
from
the_data,
jsonb_array_elements_text(js) elem;
string_agg
-----------------
alfa,beta,gamma
(1 row)
So your query should look like this:
select string_agg(elem, ',')
from
field_data.exports,
jsonb_array_elements_text(doc -> 'form_values' -> '8189' -> 'choice_values') elem;
Using the string_agg aggregate function with a sub-select from jsonb_array_elements_text seems to work (tested on PG 9.5). Note the use of jsonb_array_elements_text, added in PostgreSQL 9.4, rather than jsonb_array_elements, from PostgreSQL 9.3.
with exports as (
select $${"form_values": {"8189": {"choice_values": ["a","b","c"]}}}$$::jsonb as doc
)
SELECT
string_agg(values, ', ')
FROM
exports, jsonb_array_elements_text(doc -> 'form_values' -> '8189' -> 'choice_values') values
GROUP BY
exports.doc;
Output:
'a, b, c'
Also see this question and its answers.
Maybe not best practice: convert the json array to text, then remove the brackets.
WITH input AS (
SELECT '["text1","text2","text3"]'::jsonb as data
)
SELECT substring(data::text,2,length(data::text)-2) FROM input
It has the advantage that it converts "in-place", not by aggregating. This could be handy if you can only access part of the query, e.g. for some synchronization tool where there's field-based conversion rules, or something like the following:
CREATE TEMP TABLE example AS (SELECT '["text1","text2","text3"]'::jsonb as data);
ALTER TABLE example ALTER COLUMN data TYPE text USING substring(data::text,2,length(data::text)-2);
