PostgreSQL update JSONB without jsonb_set

Posted on

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!

Answer :

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.


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


t=# update so63 set j = jsonb_set(j,'{a}','[2,3,4]');
Time: 1.699 ms
t=# select j from so63;
 {"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]}';
Time: 0.918 ms
t=# select j from so63;
 {"a": [2, 3, 4]}
(1 row)

Time: 0.241 ms

whole jsonb is overwritten. not just “a” key

Leave a Reply

Your email address will not be published. Required fields are marked *