Postgres - remove element from jsonb array

Question

I have an array of jsonb elements (jsonb[]), with id and text. To remove an element I could use:

UPDATE "Users" SET chats = array_remove(chats, '{"id": 2, "text": "my message"')

But I want to delete the message just by the id, cause getting the message will cost me another query.

Please provide a table definition and example values. I can derive that you are using Postgres 9.4, but that should be in your question, too. Do you want to remove the element from all rows like your example suggests or from just one row? Can there be various combinations of id and text? — Erwin Brandstetter

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2015-05-03T04:07:46

Assuming missing information:

Your table has a PK called user_id.
You want to remove all elements with id = 2 across the whole table.
You don't want to touch other rows.
id is unique within each array of chats.

UPDATE "Users" u
SET    chats = array_remove(u.chats, d.chat)
FROM  (
   SELECT user_id, chat
   FROM   "Users", unnest(chats) chat
   WHERE  chat->>'id' = '2'
   ) d
WHERE  d.user_id = u.user_id;

The following explanation matches the extent of provided information in the question:

Postgres - remove element from jsonb array

1 Answers