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
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.
room instead of select. Thank you. My english is not that great, couldn't undestand a thing how the function's parameters were working from the docs. jsonb_set('param1', 'param2', 'param3' ) param1 = result, param2 = destructuring, param3 = is destructuring it again ?
jsonb_set() may look more logical.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)
