CODEWITHSUNDEEP
jsonpostgresqlaggregate-functionsjsonbpostgresql-9.5
Joebocop
Joebocop

Reputation: 569

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.

Upvotes: 10

Views: 15355

Answers (3)

M.Ermatinger
M.Ermatinger

Reputation: 101

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

Upvotes: 0

Dan Lenski
Dan Lenski

Reputation: 79810

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.

Upvotes: 4

klin
klin

Reputation: 121794

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;

Upvotes: 11

Related Questions