问如何从JSON[]数组中删除PostgreSQL中的已知元素？
EN

Database Administration用户

提问于 2014-05-08 05:36:13

回答 1查看 22.2K关注 0票数 8

关于在PostgreSQL中使用JSON数据类型，我面临一个问题。我试图实现在DB中存储一个非规范化的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对象附加到数组中。但是，我无法从数组中删除一个已知值。考虑一下f.e。这个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。

postgresql

update

array

json

回答 1

Database Administration用户

回答已采纳

发布于 2014-05-08 07:03:17

`jsonb` in 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\"}"}');

Demos

演示1

您可以将array_remove()与text表示(不可靠)结合使用。

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;