如何从JSON[]数组中删除PostgreSQL中的已知元素？
关于在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。
回答 1
Database Administration用户
发布于 2014-05-08 07:03:17
jsonb in Postgres 9.4或更高版本
考虑Postgres 9.4或更高版本中的jsonb数据类型。结尾的'b‘代表’二进制‘。除其他外，还有一个相等运算符(=)为jsonb。大多数人都会想要转换。
json
没有为数据类型=定义json操作符，因为没有为整个json值建立相等的定义良好的方法。但请看下面。
您可以转换为text，然后使用=运算符。这是短的，但只有当您的文本表示恰好匹配时才能工作。本质上不可靠，除了角落的情况。请参见：
或者您可以使用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;db<>fiddle 这里
关于隐式LATERAL JOIN：
关于取消嵌套数组：
DB设计
为了简化您的情况，请考虑一个规范化的模式:一个单独的json值表(而不是数组列)，它与主表的关系为n:1。
https://dba.stackexchange.com/questions/64759
复制相似问题
腾讯云开发者
