Return jsonb_array_elements result as comma-separated list

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);