Postgres-从jsonb数组中删除元素 [英] Postgres - remove element from jsonb array

问题描述

我有一个jsonb个元素(jsonb[])的数组，带有ID和文本.要删除元素，我可以使用:

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"')

但是我只想通过ID删除邮件，因为获取邮件将使我再次查询.

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

推荐答案

假设缺少信息:

• 您的表有一个名为user_id的PK.
• 您要在整个表格中使用id = 2删除所有元素.
• 您不想触摸其他行.
• id在每个chats数组中都是唯一的.

• 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-从jsonb数组中删除元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！