how to update more than 1 properties of jsonb field?

All we need is an easy explanation of the problem, so here it is.

how to update the age and the status at once,

I have a jsonb field type, I need to update 2 properties (age and status).
I am able to update only either age and status, using the command below, how to do update both ‘age’ and ‘status’ at once?

**Update test Set attributes = jsonb_set(attributes, array['age'],to_jsonb(32))**

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The jsonb_set function accepts a jsonb value as its first argument and returns another jsonb value, which is the result of the modification applied. You can pass the resulting jsonb value to another jsonb_set call where you would apply the second change. This would result in nested jsonb_set calls, as a_horse_with_no_name suggested in the comments. The statement would look something like this:

UPDATE
  test
SET
  attributes = jsonb_set(
                 jsonb_set(attributes, array['age'], to_jsonb(32))
               , array['status']
               , to_jsonb('some new status'::text)
               )
;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply