如何從 JSON 中刪除已知元素PostgreSQL中的數組？

我面臨一個關於在 PostgreSQL 中使用 JSON 數據類型的問題。我嘗試實現在數據庫中儲存非規範化的 Java 模型。該模型具有復雜對象的列表。因此，我決定將它們建模為原生 PostgreSQL 數組中的 JSON。

這是我的表創建語句的精簡片段：

CREATE TABLE test.persons
(
 id UUID,
 firstName TEXT,
 lastName TEXT,
 communicationData JSON[],
 CONSTRAINT pk_person PRIMARY KEY (id)
);

如您所見，這是一個以 JSON 格式提供通信數據對象列表的人。其中一個對象可能如下所示：

{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf6"}

我可以使用 PostgreSQL 的 array_append 輕鬆地將這樣的 JSON 對象附加到數組中。但是，我無法從數組中刪除已知值。考慮這個 SQL 語句：

UPDATE test.persons
SET communicationData = array_remove(
     communicationData, 
     '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf6"}'::JSON
   )
WHERE id = 'f671eb6a-d603-11e3-bf6f-07ba007d953d';

這失敗了ERROR: could not identify an equality operator for type json。您是否有提示我如何從 JSON 數組中刪除已知值？也可以按數組中的位置刪除，因為我知道還有一個……

PostgreSQL 版本是 9.3.4。

jsonb在 Postgres 9.4 或更高版本中

考慮 Postgres 9.4 或更高版本中的**jsonb**數據類型。末尾的“b”代表“二進制”。除其他外，還有一個相等運算符 ( =)jsonb。大多數人都會想要切換。

關於 jsonb 電纜的部落格。

json

沒有=為數據類型定義運算符json，因為沒有明確定義的方法來建立整個json值的相等性。但見下文。

您可以轉換為text然後使用=運算符。這很短，但僅在您的文本表示恰好匹配時才有效。本質上是不可靠的，除了極端情況。看：

• 如何查詢空對象的 json 列？

或者您可以unnest使用數組並使用**->>**運算符來 ..get JSON object field as text並比較各個欄位。

測試台

2 行：第一個像問題中一樣，第二個帶有簡單值。

CREATE TABLE tbl (
  tbl_id int PRIMARY KEY
, jar    json[]
);

INSERT INTO t VALUES
  (1, '{"{\"value\" : \"03334/254146\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f5\"}"
       ,"{\"value\" : \"03334/254147\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f6\"}"
       ,"{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"}')

, (2, '{"{\"value\" : \"a\", \"typeId\" : \"x\"}"
       ,"{\"value\" : \"b\", \"typeId\" : \"y\"}"
       ,"{\"value\" : \"c\", \"typeId\" : \"z\"}"}');

展示

展示 1 您可以使用array_remove()withtext表示（不可靠）。

SELECT tbl_id
    , jar, array_length(jar, 1) AS jar_len
    , jar::text[] AS t, array_length(jar::text[], 1) AS t_len
    , array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text) AS t_result
    , array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text)::json[] AS j_result
FROM   tbl;

展示 2 取消嵌套單個元素的數組和測試欄位。

SELECT tbl_id, array_agg(j) AS j_new
FROM   tbl, unnest(jar) AS j   -- LATERAL JOIN
WHERE  j->>'value' <> '03334/254146'
AND    j->>'typeId' <> 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5'
GROUP  BY 1;

展示 3 行類型的替代測試。

SELECT tbl_id, array_agg(j) AS j_new
FROM   tbl, unnest(jar) AS j   -- LATERAL JOIN
WHERE  (j->>'value', j->>'typeId') NOT IN (
        ('03334/254146', 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5')
       ,('a', 'x')
      )
GROUP  BY 1;

UPDATE按照要求

最後，這就是你如何實現你的UPDATE：

UPDATE tbl t
SET    jar = j.jar
FROM   tbl t1
CROSS  JOIN LATERAL (
  SELECT ARRAY(
     SELECT j
     FROM   unnest(t1.jar) AS j  -- LATERAL JOIN
     WHERE  j->>'value'  <> 'a'
     AND    j->>'typeId' <> 'x'
     ) AS jar
  ) j
WHERE  t1.tbl_id = 2              -- only relevant rows
AND    t1.tbl_id = t.tbl_id;

db<>在這裡擺弄

關於隱式LATERAL JOIN：

• 如何將 JSON 數組轉換為 Postgres 數組？
• 查詢 JSON 列中的數組元素

關於取消嵌套數組：

• 如何保留未嵌套數組中元素的原始順序？

數據庫設計

為了簡化您的情況，請考慮規範化模式：值的單獨表json（而不是數組列），以與主表的：1 關係連接。

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