Postgres - remove element from jsonb array

问题:

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.

回答1:

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: