Return jsonb_array_elements result as comma-separated list

Question

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.

Answer 1

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;

Answer 2

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.