jsonb_set returns a JSONB value with the value at a specified path replaced or optionally inserted. Use it when only one field or nested structure needs changing, avoiding transfer of the entire JSON object between application and database. section 5.5
The function accepts the JSONB object, target path, replacement value, and an insertion option.
- Write paths in {a,b,c} form, where each element identifies a nested object or the target field. section 5.5
- Use jsonb_set inside UPDATE to change an existing field or replace a nested array. section 5.5
Change a pizza crust
UPDATE pizzeria.order_items SET pizza = jsonb_set(pizza, '{crust}', '"regular"', false) WHERE order_id = 20 AND order_item_id = 5; The stored crust changes from stuffed to regular. The same pattern replaces the toppings,veggies array with tomato and spinach entries. section 5.5
Listing 5.11 Updating JSON fields with jsonb_set
UPDATE pizzeria.order_items
SET pizza = jsonb_set(pizza,'{crust}', '"regular"', false)
WHERE order_id = 20 and order_item_id = 5;
Runnable SQL demonstrates replacing a JSONB field with jsonb_set. section 5.5
Listing 5.12 Updating JSON arrays with jsonb_set
UPDATE pizzeria.order_items
SET pizza = jsonb_set(
pizza,
'{toppings,veggies}',
'[{"tomato":"extra"}, {"spinach":"regular"}]',
false
)
WHERE order_id = 20 and order_item_id = 5;
Runnable SQL demonstrates replacing a nested JSONB array through a multi-element path. section 5.5
