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.

Zacheryzack

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;

Griseofulvin

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.

Parceling

Yes, and I should have noted that in the question, sorry. For whatever reason, the string_agg function sees the result of jsonb_array_elements() as type "jsonb" rather than "setof text". Here's the error: "ErrorMessage: function string_agg(jsonb, text) does not exist" – Zacheryzack

I overlooked something too in your question. You need to use jsonb_array_elements_text (added in PG 9.4) to get setof text rather than jsonb as the output. – Parceling

See the other question I linked to if you have to use PG 9.3 for some reason; it has some workarounds. – Parceling

When I replace the jsonb_array_elements() function with jsonb_array_elements_text() I get a new error: "ErrorMessage: set-valued function called in context that cannot accept a set" – Zacheryzack

After assuming I remember how this worked, twice… I went and tried it and realized that a syntactic change is necessary here. Third time's a charm, I hope. – Parceling

Answer 3

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

Harwin

Could you please remove some of the extraneous text from your answer? – Bostwick

Please see stackoverflow.com/conduct for how to write a concise and factual answer. – Timotheus