PostgreSQL jsonb_set多键更新

Question

我有一个带有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;

然而，我需要一种方式来在键不存在时添加新的键值对，如果存在则更新键值对。是否可以在单个查询中实现这一点？

- stack_d_code

Answer 1

文档中提到：

|| 运算符连接其操作数顶层的元素。... 例如，如果两个操作数都是具有公共键字段名称的对象，则结果中该字段的值将只是右操作数的值。

因此，使用您的示例数据：

update table_name set
    data = data || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}'
    where number = 1;

此外，如果您想编辑的对象不在顶层 - 只需组合连接和jsonb_set函数即可。例如，如果原始数据看起来像这样：

{"location": {"name": "firstName", "city": "toronto", "province": "ON"}}

那么

...
data = jsonb_set(
    data, 
    '{location}', data->'location' || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}')
...

- Abelisto

---

太棒了，这个东西运行得很好。谢谢！我还没有足够的积分来点赞，但肯定给你竖起大拇指。 - stack_d_code

你尝试过阅读Oracle文档吗？相较之下，PostgreSQL文档简直是一篇文学杰作 :o) - Abelisto

有时候当你遇到错误：unknown signature: jsonb_set(jsonb, string, jsonb) 时，这是由于类型转换引起的。 在我的情况下，像下面这样添加 ::TEXT[] 就解决了这个问题 ... data = jsonb_set(data, '{location}' ::TEXT[] , data->'location' || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}' ::jsonb) ... - U R

如果您正在从查询中的动态数据填充更新字段和/或更新多个字段，则可以使用json_build_object创建更新对象。例如：data->'location' || jsonb_build_object('city', "cityColName", 'phonenum', "phoneCalName")。这比尝试像@Paarth在下面建议的那样动态创建jsonb字符串要容易得多。 - mmigdol

我无法使用JPA查询来更新多个jsonb列字段，有什么帮助吗？ - dynamo

我收到了错误信息：操作符不存在：character varying -> unknown。 提示：没有匹配给定名称和参数类型的操作符。如何解决？ - CoderBeginner

Answer 2

你可以尝试这个。

在这里，我们使用 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

- Paarth

---

请参考 PostgreSQL 中的 jsonb。 - Paarth