Convert Postgres JSONB array to comma separated list in a single column

Question

I have JSONB casted column in table that I'd like to represent as a comma separated list in a single column. I've tried a million different approaches and am coming up short. I can't find anything in the Postgres docs that addresses this particular situation, so hoping for some help!

The table in question looks a little like this:

date       | order_id | sales_reps
2019-12-01 | 1234     | [{"id": 100, "user": "Jane Doe"}, {"id": 101, "user": "John Doe"}]

I'd like to render it as:

date       | order_id | sales_reps
2019-12-01 | 1234     | Jane Doe, John Doe

I'm able to get relatively close with:

select
    date,
    order_id, 
    (select jsonb_agg(t -> 'user') from jsonb_array_elements(sales_reps::jsonb) as x(t)) as sales_reps
from table

date       | order_id | sales_reps
2019-12-01 | 1234     | ["Jane Doe", "John Doe"]

But for the life of me I can't seem to get the output I want - have tried a ton of aggregators and jsonb functions to no avail.

klin · Accepted Answer · 2019-12-11 17:10:46Z

3

Use the ->> operator to get json values as text and the string_agg() aggregate:

select
    date,
    order_id,
    (
        select string_agg(t->>'user', ',') 
        from jsonb_array_elements(sales_reps::jsonb) as x(t)
    ) as sales_reps
from my_table

Db<>fiddle.

answered Dec 11, 2019 at 17:10

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert Postgres JSONB array to comma separated list in a single column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related