PostgreSQL update JSONB without jsonb_set

Question

Would there be any drawbacks to using a normal update statement to update a json(b) column like so:

update "events" set "properties" = '{"type":"graph"}'

Instead of using the jsonb_set function provided by PostgreSQL, which would turn into this statement:

update "events" set jsonb_set("properties", {'type'}, 'graph')

When using eg. an ORM, and calling .save() on a Model for which you've just updated a JSON field, the first method will be called, but as there is no mention of this way of doing things in the PostgreSQL documentation, I fear this may have some drawbacks.

Given that I'm not incredibly familiar with anything regarding performance in databases, I thought I'd come and ask a question here.

Thanks in advance!

• Vao Tsun: and you have a typo in path - instead of {'type'} there should be '{type}' I suppose
• Vao Tsun: update "events" set jsonb_set("properties", {'type'}, 'graph') won't work, you'll have still to set propertirs = jsonb_set("properties", {'type'}, 'graph')

Answer 1

Update: If the result value of jsonb is the same, then the only difference is jsonb_set would take additional CPU (and ms) to run. In both cases you run SET column = VALUE, but if resulted jsonb value is bifferent,both your statements are very much different, here is example.

sample:

t=# create table so63(j jsonb);
CREATE TABLE
Time: 6.290 ms
t=# insert into so63 select '{"a":0,"b":true}';
INSERT 0 1
Time: 1.137 ms

jsonb_set

t=# update so63 set j = jsonb_set(j,'{a}','[2,3,4]');
UPDATE 1
Time: 1.699 ms
t=# select j from so63;
 j
-----------------------------
 {"a": [2, 3, 4], "b": true}
(1 row)

jsonb value changed at specified path! And now with update .. set:

Time: 0.278 ms
t=# update so63 set j = '{"a":[2,3,4]}';
UPDATE 1
Time: 0.918 ms
t=# select j from so63;
 j
------------------
 {"a": [2, 3, 4]}
(1 row)
Time: 0.241 ms

whole jsonb is overwritten. not just "a" key

• Pieter-Jan Vandenbussche: However if in the code I merge the original JSON property with the changed JSON property so it passes the entire object to update ... set, it'll end up being the same result but I assume it'll mess with any potential indexes set on any keys?
• Vao Tsun: In both cases you run SET column = VALUE, so the simple update should be faster then jsonb_set. Indexes built on json are not maintained by jsonb_set, so no difference for them. or I'm totally wrong here?..