I have test table with votes column and I want to upsert nested jsonb object into it. My current query:
INSERT INTO test (post_id, username, votes)
VALUES (12345, 'testuser', '{"commentid" : {"vote": true }}'::jsonb)
ON CONFLICT ON CONSTRAINT test_pkey DO UPDATE
SET votes = test.votes ||
('{"commentid" : {"vote": true} }'::jsonb || test.votes->'commentid')
WHERE NOT test.votes @> EXCLUDED.votes
RETURNING *;
test_pkey constraint is PRIMARY KEY (post_id, username)
How can I construct this statement
('{"commentid" : {"vote": true} }'::jsonb || test.votes->'commentid')
So postgresql would read it as
'{"commentid" : {"vote": true, test.votes->'commentid'} }'::jsonb
is it possible to somehow combine it like in json?
What this is supposed to do is make "commentid" key-value pair if one doesnt exist and combine them if it does.
I have tried using different variations of jsonb_set like
jsonb_set(test.votes,'{"commentid", vote}','true', true);
or
jsonb_set(test.votes,'{"commentid"}', test.votes->'commentid' || '{"vote":true}', true)
But the first one erases the whole votes object if "commentid" doesn't exist and second one replaces the whole votes object with second part of the statement.
If it's possible to make it work this way, I'd be able to return this data straight to frontend without any modification