PostgreSQL技术问题：如何向嵌套JSON数组中添加元素？

这个报错很典型——PostgreSQL的json类型并不支持||拼接运算符，这个运算符是专门给jsonb类型设计的。而且你的语句还有个小问题：你只取出了roomID数组并尝试拼接，但没把拼接后的数组放回原来的rooms对象结构里。下面给你两种可行的解决方案：

方案1：转为jsonb类型利用拼接运算符 #

如果你的字段是json类型，我们可以临时转为jsonb完成数组拼接，再转回json；如果字段本身就是jsonb，直接去掉类型转换即可：

UPDATE users 
SET rooms = json_build_object(
    'roomID',
    -- 把原json转成jsonb，取出roomID数组，拼接新元素
    (rooms::jsonb -> 'roomID') || '{"2": "test2"}'::jsonb
)::json -- 转回json类型（如果字段是jsonb可省略这步）
WHERE username = 'test_user'
RETURNING *;

方案2：使用JSON专用函数json_array_append #

针对json类型，PostgreSQL提供了专门的数组追加函数json_array_append，不需要转类型就能直接用：

UPDATE users 
SET rooms = json_build_object(
    'roomID',
    -- 给roomID数组追加新的json元素
    json_array_append(rooms -> 'roomID', '{"2": "test2"}'::json)
)
WHERE username = 'test_user'
RETURNING *;

两种方案执行后，rooms字段都会变成你想要的结果：{"roomID":[{"1":"test1"},{"2":"test2"}]}。

内容的提问来源于stack exchange，提问作者Itgelt Gankhulug