How to Update JSONB Field in PostgreSQL: Syntax and Examples

To update a jsonb field in PostgreSQL, use the jsonb_set() function which modifies a specified key or path inside the JSONB data. You can update nested keys by providing the path as a text array and the new value as JSON. Use UPDATE with jsonb_set() to change the JSONB column in your table.

Syntax

The main function to update a jsonb field is jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean).

• target: The original jsonb column.
• path: An array of keys representing the location inside the JSON to update.
• new_value: The new JSON value to set at the specified path.
• create_missing (optional): If true, missing keys in the path are created; defaults to false.

Use this function inside an UPDATE statement to modify the JSONB column.

sql

UPDATE table_name
SET jsonb_column = jsonb_set(jsonb_column, '{key}', '"new_value"', true)
WHERE condition;

Example

This example shows how to update the value of a key inside a jsonb column in a table called users. It changes the user's city inside the address JSON object.

sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  info JSONB
);

INSERT INTO users (info) VALUES
  ('{"name": "Alice", "address": {"city": "Oldtown", "zip": "12345"}}'),
  ('{"name": "Bob", "address": {"city": "Newcity", "zip": "54321"}}');

-- Update city for user with id = 1
UPDATE users
SET info = jsonb_set(info, '{address,city}', '"Newtown"', true)
WHERE id = 1;

-- Check updated data
SELECT * FROM users ORDER BY id;

Output

 id |                          info                          
----+-----------------------------------------------------
  1 | {"name": "Alice", "address": {"city": "Newtown", "zip": "12345"}}
  2 | {"name": "Bob", "address": {"city": "Newcity", "zip": "54321"}}

Common Pitfalls

• Forgetting to wrap string values in double quotes inside single quotes when passing new_value (e.g., use '"value"' not just 'value').
• Using a wrong path format; the path must be a text array like {key1,key2} without spaces.
• Not specifying create_missing when you want to add new keys; it defaults to false, but explicitly setting it helps clarity.
• Trying to update the whole JSON with a non-JSON value or wrong type.

sql

/* Wrong: missing quotes around string new_value */
UPDATE users
SET info = jsonb_set(info, '{address,city}', 'Newtown')
WHERE id = 1;

/* Right: quotes around string new_value */
UPDATE users
SET info = jsonb_set(info, '{address,city}', '"Newtown"')
WHERE id = 1;

Quick Reference

Remember these tips when updating jsonb fields:

• Use jsonb_set() to update nested keys.
• Pass the path as a text array {key1,key2}.
• Wrap string values in double quotes inside single quotes.
• Use create_missing to add keys if they don't exist.
• Combine with UPDATE and WHERE to target rows.

Key Takeaways

Use jsonb_set() to update specific keys inside a jsonb column in PostgreSQL.

Provide the path to the key as a text array and the new value as properly quoted JSON.

Remember to wrap string new values in double quotes inside single quotes.

Set create_missing to true to add keys if they don't exist in the JSON.

Always use UPDATE with a WHERE clause to modify only desired rows.