Combining Postgres jsonb query statements

Question

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

Answer 1

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

Answer 2

From what I gathered, you want to do:

SET votes = jsonb_set(
      test.votes,
      '{commentid}',
      coalesce(test.votes->'commentid', '{}') || EXCLUDED.votes->'commentid'
    )

The trick here is to use jsonb_set using {commentid} as the path you want to construct (second argument) and the third argument as already built value of commentid you want.

In order to build the third argument I first took the value of commentid from the existent row (test.votes->'commentid') and used COALESCE to set it to an empty json object ({}) if the current row does not have commentid. In other words, it will just assume empty json instead of NULL (SQL NULL, not json NULL). Then I simple concatenated it with the given commentid value (I assume that it does always have one, otherwise you'd need COALESCE there too).