使用 WHERE 子句更新數組的第 n 個元素

我在 PostgreSQL 10 數據庫中有一個包含名為“元數據”product的列的表。jsonb這是我第一次使用文件和 Postgres。jsonb值看起來像這樣：

{
“名稱”：“l33t 襯衫”，
“價格”：“1200”，
“數量”：“60”，
“選項” ：
{
“類型”：“收音機”，
“標題”：“顏色”，
“選擇”：[
{“價值”：“紅色”，“價格”：“-100”，“數量”：“30”}，
{“價值”：“藍色”，“價格”：“+200”，“數量”：“10”}，
{“價值”：“綠色”，“價格”：“+300”，“數量”：“20”}
]
}
}

兩個問題：

1.如何選擇“opts”數組中的特定元素？

select metadata->'options'->'opts'->(element here) from product
where  metadata->'options'->'opts' @> '[{"value" : "blue"}]'

2.當一件或多件售出時，如何更新“數量”（減去目前“數量”）？

進一步連結到指南/註釋表示讚賞。

1. 如何在“opts”數組中選擇特定元素？

按照手冊中的說明使用元素編號的索引。對於更長的路徑，路徑符號更短：

SELECT metadata -> 'options' -> 'opts' -> 0 AS elem0
    , metadata #> '{options, opts, 0}'     AS elem0_path
FROM   product;

獲取第n 個元素，如問題標題所要求的（JSON 數組索引以 0 開頭）。但是您的範例表明您實際上想要帶有"value":"blue". 這不是微不足道的。jsonb_array_elements()您可以在連接中取消嵌套 JSON 數組LATERAL並過濾您想要的。

SELECT opt AS elem_blue
--   , metadata #> '{options, opts}' -> (arr.ord::int - 1) AS elem_blue2
FROM   product
    , jsonb_array_elements(metadata #> '{options, opts}') WITH ORDINALITY arr(opt, ord)
WHERE  opt ->> 'value' = 'blue';

如果沒有找到匹配的元素，則不返回任何內容。

WITH ORDNALITY並且elem_blue2在這裡不需要，但展示我們將在下一步中使用的技術。

更多解釋：

• 帶有元素編號的 PostgreSQL unnest()
• 查詢 JSON 列中的數組元素

2.當一件或多件售出時，如何更新“數量”（減去目前數量）？

從 Postgres 9.5 開始，有jsonb_set(). 更新第一個元素的qty鍵值：

UPDATE product
SET    metadata =  jsonb_set(metadata, '{options, opts, 0, qty}', '"29"', false)
WHERE  ... -- some filter

旁白：有什麼理由將您的整數qty儲存為字元串（"30"）而不是數字（30）？

為了更新“藍色”元素，我們使用上面展示的技術確定數組索引，加上一些UPDATE魔法使其完全動態：

UPDATE product p
SET    metadata = jsonb_set(p.metadata, path, qty, false)
FROM   product p1
    , LATERAL ( -- move computations to subquery
  SELECT ARRAY['options', 'opts', (ord - 1)::text, 'qty'] AS path  -- fix off-by-one
       , to_jsonb((opt ->> 'qty')::int - 1)               AS qty   -- subtract here!
  FROM   jsonb_array_elements(p1.metadata #> '{options, opts}') WITH ORDINALITY arr(opt, ord)
  WHERE  opt ->> 'value' = 'blue'
-- AND    ...  -- more filters
-- FOR UPDATE  -- see below
  ) opt
WHERE  p1.product_id = p.product_id  -- use PK for match

最後一個查詢將一個數字寫入“數量” '9'（'"9"'

我們需要再次product在FROM子句中列出該表以允許LATERAL連接（否則這是不可能的） - 並自連接到它。

注意一個微小的競爭條件。在並發寫入負載較重的情況下，您可能希望在子查詢 ( FOR UPDATE) 中添加一個鎖定子句，以防止其他事務更改內部SELECT和外部之間的行UPDATE。看：

• Postgres 更新…限制 1

---

但你不應該，真的。如您所見，jsonb（或與此相關的任何文件類型）不適合定期更新單個屬性。這很麻煩，而且相對昂貴。每次都必須寫入帶有完整文件新版本的新行。改用標準化數據庫設計，其中只需要重寫相對非常小的行，其他部分以及索引保持不變。

如果只是定期更新數量，您可以將其移至 1:n 表並將其合併到 aVIEW或中的 JSON 文件中MATERIALIZED VIEW。重新設計超出了這個問題的範圍。

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