Postgresql: display values in jsonb array column as CSV

Question

I have a jsonb column that is used to store an array of value:

["value1", "value2", "value3"]

I would like to concatenate the values inside that array with the value of another column, such as instead of having:

string column | jsonb column
--------------+-------------------------------
   mystring   | ["value1", "value2", "value3"]

I can have:

           string + jsonb
------------------------------------
   mystring, value1, value2, value3

I naively tried using jsonb_array_elements to no avail.

SELECT string_column || ', ' || jsonbvalues
FROM mytable, jsonb_array_elements(mytable.jsonb_column) as jsonbvalues

Could someone indicate what the correct syntax is?

Answer 1

The function json_array_elements() returns set, so you need an aggregate function. An aggregation will be correct if the column it is grouped by is unique.

The query below assumes that string_column is unique.

SELECT format('%s, %s', string_column, string_agg(jsonbvalues, ', ')) result
FROM (
    SELECT string_column, jsonbvalues
    FROM mytable, jsonb_array_elements_text(mytable.jsonb_column) as jsonbvalues
    ) sub
GROUP BY string_column;

              result              
----------------------------------
 mystring, value1, value2, value3
(1 row)

If string_column is not unique, e.g.:

SELECT * FROM mytable;

 string_column |          jsonb_column          
---------------+--------------------------------
 mystring      | ["value1", "value2", "value3"]
 mystring      | ["value4", "value5", "value6"]
(2 rows)

you can use row_number() to identify original rows:

SELECT format('%s, %s', string_column, string_agg(jsonbvalues, ', ')) result
FROM (
    SELECT rn, string_column, jsonbvalues
    FROM (
        SELECT *, row_number() over () as rn
        FROM mytable
        ) t, 
        jsonb_array_elements_text(jsonb_column) as jsonbvalues
    ) sub
GROUP BY rn, string_column;

              result              
----------------------------------
 mystring, value1, value2, value3
 mystring, value4, value5, value6
(2 rows)