我有DB表和jsonb列.
number | data
1 | {"name": "firstName","city": "toronto","province": "ON"}
{"name": "firstName","city": "ottawa","province": "ON","phone": "phonenum","prefix": "prefixedName"}
update table_name set data = jsonb_set(data,'{city}','"ottawa"') where number = 1;
documentation says：
原文链接：https://www.f2er.com/postgresql/192754.htmlThe || operator concatenates the elements at the top level of each of its operands. … For example,if both operands are objects with a common key field name,the value of the field in the result will just be the value from the right hand operand.
所以使用你的示例数据：
update table_name set
data = data || '{"city": "ottawa","prefix": "prefixedName"}'
where number = 1;
此外,如果您要编辑的对象不在顶层 – 只需组合连接和jsonb_set函数.例如,如果原始数据看起来像
{"location": {"name": "firstName","province": "ON"}}
然后
...
data = jsonb_set(data,'{location}',data->'location' || '{"city": "ottawa","prefix": "prefixedName"}')
...
