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
CodePudding user response:
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.
CodePudding user response:
I turned the json (select rooms from users where username='test_user')
into jsonb, so I can use jsonb_set() function. Finnaly 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 quiry because (select rooms from users where username='test_user')::jsonb
is ran twice which I think is unefficient.(1secs 362msec)