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