-1

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

user2210516
  • 613
  • 3
  • 15
  • 32
  • The way SO works, your whole question (including any necessary code) has to be **in** your question, not just linked. Three reasons: People shouldn't have to go off-site to help you; some sites are blocked for some users; and links rot, making the question and its answers useless to people in the future. Please put a [mcve] **in** the question. More: [*How do I ask a good question?*](/help/how-to-ask) and [*Something in my web site or project doesn't work. Can I just paste a link to it?*](https://meta.stackoverflow.com/questions/254428/) – T.J. Crowder Nov 24 '21 at 09:55
  • Unrelated, but: [don't use `char`](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_char.28n.29) and JSON data should be stored in a `jsonb` column –  Nov 24 '21 at 09:59
  • Do you really need a comma separated result? It would be quite easy to get it as a JSON array, using `jsonb_path_query_array(context_data::jsonb, '$[*].gruppe_id')` –  Nov 24 '21 at 10:03
  • Yes i know, i don't want more get more rows like in jsonb_array_elements. In my DB it's not a json field it's a text field – user2210516 Nov 24 '21 at 10:03
  • json_path_query is not in postgres 10 as far as i know? – user2210516 Nov 24 '21 at 10:04

1 Answers1

1

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;