0

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

2 Answers 2

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

0

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).

3
  • Thanks a ton for setting me on the right track! For some reason your statement would still overwrite 'commentid' object completely with a new one. And I can't really figure out why, because it seems like it should merge the old commentid with new one and then set it inside votes objects. This is how I modified it, ant it works like a charm SET votes = (test.votes || coalesce(test.votes->'commentid', '{"commentid" : {}}')) || jsonb_set(test.votes, '{commentid,vote}', 'true'::jsonb)
    – PijusV
    Aug 28, 2016 at 15:49
  • Oh. You are right @PijusV. If you could only give a table definition and data to play with, it would be great.
    – MatheusOl
    Aug 30, 2016 at 12:16
  • hey, i put it as text here jsfiddle.net/dd0vfeew/1 it should work. Although I don't think that it will be possible to use it with parameterized querys
    – PijusV
    Aug 30, 2016 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.