postgresql postgres jsonb_set 多键更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38883233/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgres jsonb_set multiple keys update
提问by stack_d_code
I have DB table with jsonb column.
我有带有 jsonb 列的数据库表。
number | data
1 | {"name": "firstName", "city": "toronto", "province": "ON"}
I need a way to update data column. So my output should look like:
我需要一种更新数据列的方法。所以我的输出应该是这样的：
{"name": "firstName", "city": "ottawa", "province": "ON", "phone": "phonenum", "prefix": "prefixedName"}
Is it possible with json_set? I have added query like:
json_set 可以吗？我添加了如下查询：
update table_name set data = jsonb_set(data, '{city}', '"ottawa"') where number = 1;
However, I need a way to add new key-value if it does not exists and update key value if it exists. Is it possible to achieve this in single query?
但是，我需要一种方法来添加新键值（如果不存在）并更新键值（如果存在）。是否可以在单个查询中实现这一点？
回答by Abelisto
The 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.
|| 运算符连接其每个操作数的顶层元素。...例如，如果两个操作数都是具有公共键字段名称的对象，则结果中字段的值将只是来自右侧操作数的值。
So using your example data:
因此，使用您的示例数据：
update table_name set
data = data || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}'
where number = 1;
Additionally if the object you want to edit is not at the top level - just combine the concatenation and jsonb_setfunction. For example, if the original data looks like
此外，如果您要编辑的对象不在顶层 - 只需将串联和jsonb_set函数结合起来。例如，如果原始数据看起来像
{"location": {"name": "firstName", "city": "toronto", "province": "ON"}}
then
然后
...
data = jsonb_set(data, '{location}', data->'location' || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}')
...
回答by Paarth
You can try this
你可以试试这个
Here we are using jsonbconcatation operator ||to Concatenate two jsonb objects
这里我们使用jsonb连接运算符||连接两个 jsonb 对象
update table_name set data = (select val from (
(select
CASE WHEN data ? key THEN jsonb_set(data, '{' || key || '}', quote_nullable(updated_value))
ELSE
data || ('{' || quote_ident(key) || ':' || quote_ident(some_value) || '}')::jsonb
END val
from json_each_text((select data::json from tbl))
CROSS JOIN tbl t
where key in ('city','phone','prefix') and number=1)) where number=1
