JSONB_SET in PostgreSQL: Patch a Single Field Inside a JSON Document

jsonb_set returns a copy of a JSON document with the value at a given path replaced by a new one. It is the workhorse for partial updates: instead of rewriting the whole JSON from your application, you change one nested field directly in the database and leave every other key untouched.

Syntax and the path

The signature is jsonb_set(target, path, new_value [, create_missing]). The path is an array of text keys that walks down to the spot you want to change.

SELECT jsonb_set(
    '{"address": {"city": "Quito", "zip": "170150"}}'::jsonb,
    '{address,city}',
    '"Lima"'
);
-- {"address": {"city": "Lima", "zip": "170150"}}

A few things to keep straight:

• path is written as an array literal: '{address,city}' means "key address, then key city inside it".
• new_value must be valid JSON. The string "Lima" has to carry its quotes; a bare Lima raises an error.
• You can drop array indexes into the path: '{tags,0}' is the first element of the tags array.
• The function only works on jsonb, not json. Cast the column with ::jsonb if needed.

The create_missing flag

The fourth argument controls what happens when the key is not yet in the document. It defaults to true, so an absent key is created.

-- key "verified" is absent; with create_missing = true (default) it is added
SELECT jsonb_set(
    '{"city": "Lima"}'::jsonb,
    '{verified}',
    'true',
    true
);
-- {"city": "Lima", "verified": true}

-- with create_missing = false the document is returned unchanged
SELECT jsonb_set(
    '{"city": "Lima"}'::jsonb,
    '{verified}',
    'true',
    false
);
-- {"city": "Lima"}

Gotcha: create_missing only creates the final key in the path. If an intermediate object does not exist (say there is no address key at all, yet you write to '{address,city}'), the path is unreachable and the document comes back unchanged with no error. That silently looks like success, so verify the result.

Patching a nested field in an UPDATE

The headline use case is fixing one field straight in a table. Suppose users has a JSONB column profile, and you need to change one user's city:

UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"Lima"')
WHERE id = 42;

Because jsonb_set returns a copy, you assign that copy back into the column. The rest of profile stays as it was.

Calls nest, so you can change several fields in one pass:

UPDATE users
SET profile = jsonb_set(
        jsonb_set(profile, '{address,city}', '"Lima"'),
        '{verified}', 'true'
    )
WHERE country = 'PE';

For numbers, insert an actual number without quotes: jsonb_set(data, '{score}', '10') yields 10, while '"10"' yields the string "10". A dynamic value is easiest to build with to_jsonb:

UPDATE orders
SET meta = jsonb_set(meta, '{discount}', to_jsonb(amount * 0.1))
WHERE status = 'paid';

Removing keys with the minus operator

To drop a field you do not need jsonb_set at all; the - operator does it. It removes a top-level key or an array element by index.

-- remove a top-level key
SELECT '{"city": "Lima", "tmp": 1}'::jsonb - 'tmp';
-- {"city": "Lima"}

-- remove an array element by index
SELECT '["a", "b", "c"]'::jsonb - 1;
-- ["a", "c"]

For a nested key, use the #- operator and hand it a path:

UPDATE users
SET profile = profile #- '{address,zip}'
WHERE id = 42;

Several top-level keys can be dropped at once with an array:

SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{a,b}'::text[];
-- {"c": 3}

Differences in other engines

PostgreSQL is the smoothest engine for this work, but the syntax differs elsewhere:

• MySQL uses JSON_SET(col, '$.address.city', 'Lima') with a string path like $.a.b. It also has JSON_REMOVE for deletion and JSON_REPLACE, which never creates missing keys, effectively the create_missing = false behavior.
• ClickHouse is not built for partial JSON updates: its JSON functions are mostly read-oriented, and changing a document usually means rewriting the whole value.

The rule of thumb for PostgreSQL: jsonb_set always hands back a new document, so it only works paired with an assignment (SET col = jsonb_set(...)); for deletion reach for - and #-.