So i have this json column in my postgres (v.10) database.

It can look like this:

[{"_destroy": false, "gruppe_id": "verwalter"}, {"_destroy": "", "gruppe_id": "budget_nur_anzeigen"}]

I just want to get values from the gruppe_id elements concatenated in a new column like this:

Gruppe_ID
verwalter, budget_nur_anzeigen

Fiddle

CodePudding user response：

You need to unnest the array, then aggregate back:

select m.id, 
       (select string_agg(c.item ->> 'gruppe_id', ',')
        from jsonb_array_elements(m.context_data::jsonb) as c(item)) as gruppe_id
from mutations m;