PostgreSQL: How to add element to nested JSON array?

Question

select rooms from users where username='test_user';

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

I want to add to[{"1":"test1"}] this {"2": "test2"} --> [{"1":"test1"}, {"2": "test2"}]

My current attempt is this.

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

Messages ERROR: operator does not exist: json || json LINE 4: -> 'roomID' || '{"2": "test2"}' ::json

Answer 1

You can use jsonb_build_object():

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

or jsonb_set():

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

Test it in Db<>fiddle.

I have assumed the type of the rooms columns is jsonb. If it is json you need to cast the column to jsonb, e.g.:

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

Note also, that you do not have to use select in update to get a value of a column, use just the column instead.