PostgreSQL中结合CASE与json_extract_path_text修改JSON的报错解决

先解决你的报错问题 #

你遇到的ERROR: column "foo" does not exist，是因为PostgreSQL中双引号" "用于标识列名、表名等数据库对象，而字符串常量需要用**单引号' '**包裹。把查询语句里的双引号改成单引号就能正常运行：

select (
case when (
json_extract_path_text( '{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') = 'foo') then 'loo'
else 'oo' end)

替换JSON对象中的指定值（而非仅返回字符串） #

如果你的需求是修改整个JSON对象里的"foo"为"loo"，推荐使用jsonb类型（PostgreSQL对jsonb的修改支持更友好，若原字段是json类型可转成jsonb操作），结合jsonb_set函数实现：

1. 查询时返回修改后的JSON #

select 
  case 
    -- 用->>操作符直接获取指定路径的文本值，比json_extract_path_text更简洁
    when data->'f4'->>'f6' = 'foo' 
    then jsonb_set(data::jsonb, '{f4,f6}', '"loo"')::json
    else data
  end as updated_json
from (select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}'::json as data) t;

2. 更新表中的JSON字段 #

假设你的表名为your_table，JSON字段名为json_column，执行以下语句即可批量替换符合条件的JSON值：

update your_table
set json_column = jsonb_set(json_column::jsonb, '{f4,f6}', '"loo"')::json
where json_column->'f4'->>'f6' = 'foo';

说明：

• jsonb_set的第一个参数是目标jsonb对象，第二个参数是要修改的路径（用数组形式'{f4,f6}'表示f4下的f6字段），第三个参数是要替换成的新值（需写成JSON格式的字符串，所以用"loo"包裹后再用单引号引起来）
• 如果你的字段本身就是jsonb类型，不需要额外转成jsonb，直接使用即可

内容的提问来源于stack exchange，提问作者CoderBeginner