postgresql – postgres jsonb_set多个密钥更新

发布时间：2020-12-13 16:29:36 所属栏目：百科来源：网络整理

导读：我有DB表和jsonb列. number | data 1 | {"name": "firstName","city": "toronto","province": "ON"} 我需要一种更新数据列的方法. 所以我的输出应该是这样的： {"name": "firstName","city": "ottawa","province": "ON","phone": "phonenum","prefix": "pref

我有DB表和jsonb列.

number  | data
    1   | {"name": "firstName","city": "toronto","province": "ON"}

我需要一种更新数据列的方法.
所以我的输出应该是这样的：

{"name": "firstName","city": "ottawa","province": "ON","phone": "phonenum","prefix": "prefixedName"}

json_set可以吗？
我添加了如下查询：

update table_name set data = jsonb_set(data,'{city}','"ottawa"') where number = 1;

但是,我需要一种方法来添加新的键值(如果它不存在)并更新键值(如果它存在).是否可以在单个查询中实现此目的？

documentation says：

The || 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"}')
...

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!