sql - PostgreSQL:如何将元素添加到嵌套的 JSON 数组？

Question

select rooms from users where username='test_user';

**returns** {"roomID":[{"1":"test1"}]}

我想添加到[{"1":"test1"}] 这个{"2": "test2"} --> [ {"1":"test1"}, {"2": "test2"}]

我目前的尝试是这样的。

UPDATE users
SET rooms=(
    (select rooms from users where username='test_user')::json
    -> 'roomID' || '{"2": "test2"}' ::json
)
WHERE username='test_user'
RETURNING *
;

消息错误:运算符不存在:json || JSON第 4 行:-> 'roomID' || '{"2": "test2"}'::json

Answer 1

您可以使用jsonb_build_object():

update users set
    rooms = jsonb_build_object('roomID', rooms -> 'roomID' || '{"2": "test2"}')
where username = 'test_user'
returning *;

或jsonb_set():

update users set
    rooms = jsonb_set(rooms, '{roomID}', rooms -> 'roomID' || '{"2": "test2"}')
where username = 'test_user'
returning *;

在 Db<>fiddle. 中测试它

我假设 rooms 列的类型是 jsonb。如果是 json 则需要将列转换为 jsonb，例如:

update users set
    rooms = jsonb_set(rooms::jsonb, '{roomID}', rooms::jsonb -> 'roomID' || '{"2": "test2"}')
where username = 'test_user'
returning *;

另请注意，您不必在 update 中使用 select 来获取列的值，只需使用该列即可。