如何從json數組中刪除對象？

我的桌子：

CREATE TABLE items (
   id BIGINT PRIMARY KEY NOT NULL,
   name VARCHAR,
   images json
);

圖片格式：

[
 {
   "id": "owner",
   "full": "<url>",
   "thumb": "<url>"
 },
 {
   "id": "note_0",
   "full": "<url>",
   "thumb": "<url>"
 },
 {
   "id": "note_1",
   "full": "<url>",
   "thumb": "<url>"
 },
 {
   "id": "note_2",
   "full": "<url>",
   "thumb": "<url>"
 }
]

我需要這樣的東西：

UPDATE items SET images = delete(images, 'note_1');

要從列中刪除所有元素images（保存一個 json 數組），其中'id'是'note_1'：

第 9.3 頁

UPDATE items i
SET    images = i2.images
FROM  (
 SELECT id, array_to_json(array_agg(elem)) AS images
 FROM   items i2
      , json_array_elements(i2.images) elem
 WHERE  elem->>'id' <> 'note_1'
 GROUP  BY 1
 ) i2
WHERE  i2.id = i.id
AND    json_array_length(i2.images) < json_array_length(i.images);

SQL小提琴。

解釋

1. 使用set-returning 函式json_array_elements()的隱式在子查詢中取消嵌套 JSON 數組。JOIN LATERAL細節：

• 使用 JSONB 加入 PostgreSQL
• 如何將 json 數組轉換為 postgres 數組？

2. 加入基表並添加另一個WHERE條件 usingjson_array_length()以排除未受影響的行 - 因此您不會更新表的每一行，這將是昂貴的（和廣泛傳播的）廢話。

第 9.4 頁

使用額外的運算符jsonb，這會變得容易得多。jsonb

UPDATE items i
SET    images = i2.images
FROM  (
 SELECT id, array_to_json(array_agg(elem)) AS images
 FROM   items cand
      , json_array_elements(cand.images) elem
 **WHERE cand.images @> '{[{"id":"note_1"}]}'::jsonb**
 AND    elem->>'id' <> 'note_1'
 GROUP  BY 1
 ) i2
WHERE i2.id = i.id;

在開始時消除不受影響的行，這要快得多。另外，現在也有廣泛的原生索引支持jsonb。

以下是一些範例、基準和新功能與舊 json 和 MongoDB 的比較，以及（一些）主要作者 Alexander Korotkov、Oleg Bartunov 和 Teodor Sigaevat PGCon 2014 對 jsquery 的展望：

• “創建索引……使用伏特加”

引用自：https://dba.stackexchange.com/questions/84472