Removing json objects from an array stored in postgresql column

Question

I have an array of json objects, from which I want to removed three specific objects. For example:

[{"id": "aaa"}, {"id": "aab"}, {"id": "aac"}, {"id": "aad"}]

I need to update the table and remove from the array the objects that contain "aaa", "aab", and "aac" so that the array would only contain

[{"id": "aad"}]

or whatever other json objects that are stored in the array and don't contain the previous mentioned values stored in "id".

I'm using Postgres, and I know just that I need to use UPDATE, but that's about it (I'm still a beginner).

user330315 · Accepted Answer · 2019-10-14 08:57:24Z

1

You need to unnest the array, filter out the unwanted elements and then aggregate back:

update the_table
   set the_column = (select jsonb_agg(j)
                     from jsonb_array_elements(the_table.the_column) as t(j)
                     where j ->> 'id' not in ('aaa','aab','aac'));

Online example: https://rextester.com/HCJK50138

answered Oct 14, 2019 at 8:57

user330315

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

Collectives™ on Stack Overflow

Removing json objects from an array stored in postgresql column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related