postgresql - 更改 Postgres 中 JSONB 列中值的类型

Question

我有存储 JSON 对象的 JSONB 类型的“主题”列。示例:{"team": "1234", "user": 5678} 或 {"org": 123} 或 {"team": 1234}。

我应该使用什么查询来将所有出现的 {"team": "1234", ...} 更改为 {"team": 1234, ...}？

我试过:

UPDATE the_table SET subject = jsonb_set(subject, '{team}', (subject->>'team')::int)

但我得到:

ERROR: function jsonb_set(jsonb, unknown, integer) does not exist
LINE 2: SET subject = jsonb_set(subject, 'team', (subject->>'team'):... 
                      ^ 
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Answer 1

只需将 subject->>'team' 结果直接转换为 jsonb 而不是 int。不要忘记添加 WHERE 过滤器，否则您的第二条记录将被删除。

demo:db<>fiddle

UPDATE the_table 
SET subject = jsonb_set(subject, '{team}', (subject->>'team')::jsonb)
WHERE subject->>'team' IS NOT NULL;