根据一定条件替换所有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);

如果原始值为“foo”，我想用值“foo1”重新填充每个数组元素中“owner”的值，其他所有内容都应保持不变。

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

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 view set
content = replace(content::text, '"owner": "foo"', '"owner": "foo1"')::json

请参阅现场演示。

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

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

Answer 2

一旦将 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 中，我使用 ROW_NUMBER() 为每个出现的“foo”生成一个索引，作为所有者。这为我们提供了所有需要更改的事件的详细分类。然后，在递归 CTE 中，我从索引 = 1（第一次出现“foo”）开始逐一应用每个更改。最终，我使用最后一个（相关子查询来获取每个 id 的 MAX（索引））来执行 UPDATE。

我相当确信应该有一种更简单、更优雅的方法来做到这一点，并且会很高兴地对提供的答案进行投票。也许您会发现我的回答同时有用。

这是 dbfiddle 的工作演示。