how to update a property value of a jsonb field?

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

I have a jsonb type named attr field that contains the following:

{ 
  "pid": 1,
  "name": "john",
  "is_default": true
 }

how to change is_default to false?

I try to run below, but no luck.

update attr set attr ->> 'is_default' = false where sales_type = 2

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

There are two ways to accomplish this:

Simply concatenating the new key/value pair:

update the_table
  set attr = attr || '{"is_default": false}';

This works because when concatenating two jsonb values, existing keys will be overwritten.

The other way is to use jsonb_set() that updates a value at a location specified through a "path" (which is defined through the elements of an array)

update the_table
  set attr = jsonb_set(attr, array['is_default'], to_jsonb(false));

With the upcoming version 14 (to be released in Q4/2021) you will be able to simplify this to:

update the_table
   set attr['is_default'] = to_jsonb(false);

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