CODEWITHSUNDEEP
postgresqlsql-updatejsonb
Boris Golovey
Boris Golovey

Reputation: 65

postgres remove specific element from jsonb array

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?

Upvotes: 1

Views: 214

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 1

Related Questions