Postgres JSONB字段中的全局替换

使用简单的强制转换 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)

使用更新:

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