How to Use jsonb_set in PostgreSQL for JSONB Updates

Use jsonb_set in PostgreSQL to update a JSONB column by specifying the JSON path and the new value. It returns a new JSONB object with the updated data without modifying the original. The syntax is jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean).

Syntax

The jsonb_set function updates a JSONB value by replacing or adding a value at a specified path.

• target jsonb: The original JSONB data.
• path text[]: An array of keys representing the location in the JSON to update.
• new_value jsonb: The new JSONB value to set at the path.
• create_missing boolean (optional): If true, missing keys in the path are created; defaults to true.

sql

jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean DEFAULT true)

Example

This example shows how to update a JSONB column by changing the value of a key and adding a new key if it does not exist.

sql

CREATE TABLE products (id serial PRIMARY KEY, info jsonb);

INSERT INTO products (info) VALUES
  ('{"name": "Pen", "price": 5, "details": {"color": "blue", "stock": 100}}');

-- Update the price to 6
SELECT jsonb_set(info, '{price}', '6'::jsonb) AS updated_info FROM products WHERE id = 1;

-- Add a new key "discount" with value 10
SELECT jsonb_set(info, '{discount}', '10'::jsonb) AS updated_info FROM products WHERE id = 1;

-- Update nested key "details" -> "stock" to 90
SELECT jsonb_set(info, '{details,stock}', '90'::jsonb) AS updated_info FROM products WHERE id = 1;

Output

          updated_info          
--------------------------------
 {"name": "Pen", "price": 6, "details": {"color": "blue", "stock": 100}}
(1 row)

          updated_info          
--------------------------------
 {"name": "Pen", "price": 5, "details": {"color": "blue", "stock": 100}, "discount": 10}
(1 row)

          updated_info          
--------------------------------
 {"name": "Pen", "price": 5, "details": {"color": "blue", "stock": 90}}
(1 row)

Common Pitfalls

Common mistakes when using jsonb_set include:

• Not casting the new value to jsonb, which causes errors.
• Forgetting that jsonb_set returns a new JSONB value and does not modify the original column unless used in an UPDATE statement.
• Incorrectly specifying the path array, especially for nested keys.
• Not setting create_missing to true when adding new keys, which causes the function to ignore missing paths.

sql

/* Wrong: new_value not cast to jsonb */
SELECT jsonb_set('{"a":1}'::jsonb, '{a}', '2');

/* Right: cast new_value to jsonb */
SELECT jsonb_set('{"a":1}'::jsonb, '{a}', '2'::jsonb);

Quick Reference

Parameter	Description	Example
target jsonb	Original JSONB data to update	'{"key": "value"}'::jsonb
path text[]	Array of keys to locate the value	'{key}'
new_value jsonb	New value to set at the path	'"new"'::jsonb
create_missing boolean	Create missing keys if true (default true)	true or false

Key Takeaways

Use jsonb_set to update or add values inside JSONB columns by specifying a path and new value.

Always cast the new value to jsonb to avoid errors.

jsonb_set returns a new JSONB value; use UPDATE to save changes to the table.

Set create_missing to true to add new keys along the path if they don't exist.

The path parameter is an array of keys that points to the location inside the JSONB.