根据特定条件替换所有json数组元素中的键/值对

Question

我有这样的表：

create table view (id int, content jsonb);
insert into view values (0,'[{"owner":"foo", "value": 1},{"owner":"bar", "value": 2},{"owner":"foo", "value": 3}]'::jsonb);
insert into view values (1,'[{"owner":"bar", "value": 3},{"owner":"bar", "value": 5},{"owner":"foo", "value": 6}]'::jsonb);
insert into view values (2,'[{"owner":"foo", "value": 4},{"owner":"foo", "value": 8},{"owner":"bar", "value": 9}]'::jsonb);

我想重述一下"；所有者；在每个具有值"0"的数组元素中；foo1"；如果原始值是"0"；foo"；，其他一切都应该完好无损。

我最终得到了这样的更新查询：

WITH content_item AS (
SELECT 
('{' || index - 1 || ',"owner"}')::TEXT[] AS path,
id,
(item -> 'owner') as owner
FROM view,
jsonb_array_elements(content) WITH ORDINALITY arr(item, index)
)
UPDATE view v
SET content = jsonb_set(content, content_item.path, '"foo1"')
FROM content_item
WHERE owner = '"foo"' AND content_item.id = v.id;

问题是只有第一次出现"；foo"；用"；foo1"：

id  |content
------------------------------------------------------------------------------------------------
0   |[{"owner": "foo1", "value": 1}, {"owner": "bar", "value": 2}, {"owner": "foo", "value": 3}]
1   |[{"owner": "bar", "value": 3}, {"owner": "bar", "value": 5}, {"owner": "foo1", "value": 6}]
2   |[{"owner": "foo1", "value": 4}, {"owner": "foo", "value": 8}, {"owner": "bar", "value": 9}]

如何替换数组中的所有引用？

Answer 1

一旦您将UPDATE转换为SELECT，问题就会变得显而易见——对于owner='foo'的每一次出现，您都会得到一行，然后替换该行，但另一行保持不变。

当UPDATE对每一行执行时，它将更新n次出现n次的行，每次都保留n-1次。

可能有更好的方法，但你可以做的是递归地合并所有这些更新，一个接一个，然后在最后你会得到最终版本，并使用该版本进行更新。

WITH RECURSIVE content_item AS (
SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY index) AS index,
('{' || index - 1 || ',"owner"}')::TEXT[] AS path,
id,
(item -> 'owner') as owner
FROM view,
jsonb_array_elements(content) WITH ORDINALITY arr(item, index)
WHERE item->'owner' = '"foo"'
),
recursively AS 
(
SELECT v.id, content_item.index, jsonb_set(content, content_item.path, '"foo1"') as content
FROM content_item
JOIN view v
ON content_item.id = v.id
WHERE content_item.index = 1

UNION ALL

SELECT rec.id, content_item.index, jsonb_set(content, content_item.path, '"foo1"') as content
FROM recursively rec
JOIN content_item
ON content_item.id = rec.id
AND content_item.index = rec.index+1
)
UPDATE view v
SET content = up.content
FROM recursively up
WHERE up.id = v.id 
-- select the last of recursive iterations
AND index = (SELECT MAX(index) FROM recursively down WHERE up.id = down.id)

因此，在第一个CTE中，加法是I为"0"的每一次出现生成一个索引；foo"；作为使用CCD_ 1的所有者。这给了我们一个很好的小分解，我们需要改变的所有事件。然后，在递归CTE中，我从index=1(第一次出现"foo"(开始，逐个应用每个更改。最后，我使用最后一个(相关的子查询来获得每个id的MAX(索引((来执行UPDATE。

我非常相信，应该有一种更简单、更优雅的方法来做这件事，我会很高兴地投票支持提供答案的人。也许在此期间你会发现我的答案很有用。

这是一个关于dbfiddle的工作演示。

Answer 2

使用基于文本的方法：

update view set
content = replace(content::text, '"owner": "foo"', '"owner": "foo1"')::json

观看现场演示。

注意：Postgres不支持从文本直接转换为jsonb，但它会在更新期间将文本转换为json，然后自动转换为jsonn。

还有一个风格问题：如果可能的话，最好不要在实体名称中使用保留字，例如view。