在Postgres的JSONB字段中进行全局替换

Question

我需要全局替换一个在嵌套的JSON结构中出现多次的特定字符串，该结构以jsonb形式存储在Postgres表中。例如：

{
  "location": "tmp/config",
  "alternate_location": {
    "name": "config",
    "location": "tmp/config"
  }
}

...应该变成：

{
  "location": "tmp/new_config",
  "alternate_location": {
    "name": "config",
    "location": "tmp/new_config"
  }
}

我尝试过：

UPDATE files SET meta_data = to_json(replace(data::TEXT, 'tmp/config', 'tmp/new_config'));

不幸的是，这会导致 JSON 格式不正确，出现三重转义的引号。

有什么想法如何解决这个问题吗？

- crowhoplaminar

---

这不是一个有效的JSON。 - klin

@klin，糟糕，我让示例JSON有效了。 - crowhoplaminar

Answer 1

使用简单的类型转换到 jsonb，而不是使用 to_json()，例如：

with files(meta_data) as (
values(
'{
  "location": "tmp/config",
  "alternate_location": {
    "name": "config",
    "location": "tmp/config"
  }
}'::jsonb)
)

select replace(meta_data::text, 'tmp/config', 'tmp/new_config')::jsonb
from files;

                                                replace                                                 
--------------------------------------------------------------------------------------------------------
 {"location": "tmp/new_config", "alternate_location": {"name": "config", "location": "tmp/new_config"}}
(1 row)

- klin

Answer 2

使用更新操作：

UPDATE files
SET meta_data = replace(data::TEXT, 'tmp/config', 'tmp/new_config')::jsonb;

- Sajad Abdollahi

---

一个好的答案总是会包括解释为什么这样做可以解决问题，这样原帖作者和任何未来的读者都可以从中学习。 - Tyler2P