需求

数据表有个 json 数据类型的字段，字段名为: data_fomat。这个json数据有 Image 和 Thumbnail 键值对。
已知 Image 的值为:
https://mpi.halaracdn.com/upload/online/41/16/26/05/23/_9794347619.jpg
想通过规则替换，把 Thumbnail 赋值为:
https://mpir.halarastatic.com/upload/online/41/16/26/05/23/_9794347619-342x.webp

format函数和json_set函数

• FORMAT函数

SELECT FORMAT('Hello %s', 'Geeks');
SELECT FORMAT('"%s"', 'Geeks');

• jsonb_set 函数

函数原型: jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])

-- 属性值为数组
jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)
jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]')
-- 属性值为字符串
SELECT jsonb_set('{"x": 1}', '{x}', '"x"');
-- 属性值为整数
SELECT jsonb_set('{"x": 1}', '{y}', '2');

修改

update public.ods_cwr_end_url_request_nd set data_format = jsonb_set(ur.data_format::jsonb, '{Thumbnail}', FORMAT('"%s"', replace(replace(ur.data_format->>'Image', 'mpi.halaracdn.com', 'mpir.halarastatic.com'), '.jpg', '-342x.webp'))::jsonb)
from public.ods_cwr_end_sites_nd st
left join public.ods_cwr_end_url_request_nd ur on  ur.site_id = st.id
where st."name" = 'thehalara' and ur.step=0 and ur.id = public.ods_cwr_end_url_request_nd.id
;

注: 在 update 语句中使用 join 语法，在 where 条件后记得添加关联条件:
ur.id = public.ods_cwr_end_url_request_nd.id

9.15. JSON Functions and Operators https://www.postgresql.org/docs/9.5/functions-json.html
Update bool value in a jsonb column - jsonb_set https://stackoverflow.com/questions/69757640/update-bool-value-in-a-jsonb-column-jsonb-set