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.

Answer 2

I turned the json (select rooms from users where username='test_user') into jsonb, so I can use jsonb_set() function. Finally added jsonb object{"2": "test2"} to the destructered the jsonb [{"1": "test1"}]```

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

If anyone knows is there a way to reference a query because (select rooms from users where username='test_user')::jsonb is run twice which I think is inefficient (1 sec 362 msec)