Postgresql – Combining Postgres jsonb query statements

jsonpostgresqlpostgresql-9.5

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

Best Answer

What I ended up doing using inspiration from MatheusOl answer.

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 || coalesce(test.votes->'commentid', '{"commentid" : {"vote: true"}}'))
   || jsonb_set(test.votes, '{commentid,vote}', 'true'::jsonb) 

WHERE NOT test.votes @> EXCLUDED.votes
RETURNING *;

When using his answer, "commentid" would still be overwritten, but I don't really understand why.

This doesn't do things very efficiently, as it recreates the "commentid" key every time as a new object, but in my case it will have significantly more reads than writes, so it should be fine

Related Question