postgres remove specific element from jsonb array

Question

I am using postgres 10 I have a JsonArray in a jsonb column named boards. I have a GIN index on the jsonb column. The column values look like this:

[{"id": "7beacefa-9ac8-4fc6-9ee6-8ff6ab1a097f"}, 
 {"id": "1bc91c1c-b023-4338-bc68-026d86b0a140"}]

I want to delete in all the rows in the column the element

{"id": "7beacefa-9ac8-4fc6-9ee6-8ff6ab1a097f"} if such exists(update the column).

I saw that it is possible to delete an element by position with operator #- (e.g. #-'{1}') and I know you can get the position of an element using "with ordinality" but i cant manage to combine the two things.

How can i update the jsonarray?

select boards - (position-1)::int from user_boards, jsonb_array_elements(boards) with ordinality arr(elem, position) WHERE elem->>'id' = '7beacefa-9ac8-4fc6-9ee6-8ff6ab1a097f'; return me the desired output but how do I update the rows now? — Boris Golovey

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2020-06-23T14:01:00

One option would be using an update statement containing a query selecting all the sub-elements except {"id": "7beacefa-9ac8-4fc6-9ee6-8ff6ab1a097f"} by using an inequality, and then applying jsonb_agg() function to aggregate those sub-elements :

UPDATE user_boards
   SET boards = (SELECT jsonb_agg(j.elm)
                   FROM user_boards u
                  CROSS JOIN jsonb_array_elements(boards) j(elm)
                  WHERE j.elm->>'id' != '7beacefa-9ac8-4fc6-9ee6-8ff6ab1a097f'
                    AND u.ID = user_boards.ID
                  GROUP BY ID)

where ID is an assumed identity(unique) column of the table.

Demo

postgres remove specific element from jsonb array

1 Answers