Split Text string and make it a aggregated list in Postgres

Question

I have 2 columns in my PostgreSQL table as shown below, customer ID tag values .I want to get the name values of all tags separated by comma .How can i do it.

Input

Customer_ID_343        [{"tagId": "W", "name": "ds_initialsegment04", "href": "https://api3.getresponse360.pl/v3/tags/W", "color": ""}
    , {"tagId": "I", "name": "rea", "href": "https://api3.getresponse360.pl/v3/tags/I", "color": ""}
    , {"tagId": "c", "name": "rea_gen0220_7mbis10m", "href": "https://api3.getresponse360.pl/v3/tags/c", "color": ""}
    , {"tagId": "o", "name": "rea_20200220_gen", "href": "https://api3.getresponse360.pl/v3/tags/o", "color": ""}
    , {"tagId": "5", "name": "no_wholesale_order_greater_0", "href": "https://api3.getresponse360.pl/v3/tags/5", "color": ""}
    , {"tagId": "B", "name": "H0710gen", "href": "https://api3.getresponse360.pl/v3/tags/B", "color": ""}]

Expected output

Customer_ID_343  ds_initialsegment04,rea,rea_gen0220_7mbis10m,rea_20200220_gen,no_wholesale_order_greater_0,H0710gen

Answer 1

It looks like you have json data. If so, you can unnest the array elements with json[b]_array_elements(), then use string aggregation. A lateral join comes handy to handle the logic:

select t.customer_id, x.names
from mytable t
cross join lateral (
    select string_agg(x.obj ->> 'name', ',') as names
    from jsonb_array_elements(t.tags) as x(obj)
) x