how to update a property value of a jsonb field?

Question

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

Answer 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));

---

If you're on version 14 (released September 2021) or greater, you can simplify this to:

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

Answer 2

Let's say we got this jsonb

{
    "animals": [
        {"name": "Cat", "age": 5},
        {"name": "Dog", "age": 10}
    ]
}

We want to update the age of Cat from 5 to 15. The function jsonb_set(...) returns a new jsonb with updated value.

select
    jsonb_set(
        '{"animals": [{"name": "Cat", "age": 5}, {"name": "Dog", "age": 10}]}',
        '{animals,0,age}',  -- Path to the field to update
        '15'::jsonb,  -- New value to set
        false  -- If true, this will make a new key if it doesn't exist in the jsonb
    )
;

If you would like to do something like that in an SQL update, this would go like;

update animals
set
    animals_metadata = jsonb_set(animals_metadata, '{animals,0,age}', to_jsonb(15), false)
where id = '123';

Check out the official documentation for more!