8

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!

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

1 Answer 1

8

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

2
  • 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? Apr 19, 2017 at 13:10
  • 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?..
    – Vao Tsun
    Apr 19, 2017 at 14:07

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.