The JSONB || Operator in PostgreSQL: Merging Documents and Patches

The || operator on jsonb does a shallow merge of two documents: it keeps every key on the left, overlays every key on the right, and on a collision the right-hand value wins. It is the shortest way to apply a partial patch to JSON straight in the database without rewriting the whole document from your application.

Merging objects: the right key wins

When both operands are JSON objects, the result is the union of their keys. If a key appears in both, the value comes from the right operand.

SELECT '{"verified": false, "city": "Lima"}'::jsonb
       || '{"verified": true}'::jsonb;
-- {"city": "Lima", "verified": true}

A few facts worth keeping in mind:

• The operator does not mutate the source; it returns a new document, like every jsonb operation.
• Operand order matters: a || b and b || a differ when keys overlap.
• Both operands must be jsonb. If the column is typed json, cast it with ::jsonb.
• A right-hand key whose value is null does not drop the key; it overwrites it with null.

Appending to an array

When both operands are arrays, || concatenates them. That is the handy way to push an element onto the end of a JSON array.

SELECT '["sql", "json"]'::jsonb || '["postgres"]'::jsonb;
-- ["sql", "json", "postgres"]

When one operand is an array and the other is a scalar or object, the latter is wrapped in a one-element array and glued on:

SELECT '["a", "b"]'::jsonb || '"c"'::jsonb;
-- ["a", "b", "c"]

A typical case is appending a tag to an array inside a column:

UPDATE users
SET profile = profile || '{"tags": []}'::jsonb
WHERE profile -> 'tags' IS NULL;

A partial patch in an UPDATE

The headline use case is updating several fields in one expression. Say orders has a JSONB column meta; we mark an order paid and record the method:

UPDATE orders
SET meta = meta || '{"paid": true, "method": "card"}'::jsonb
WHERE id = 1001;

You can build the patch dynamically from columns with jsonb_build_object instead of stitching strings by hand:

UPDATE users
SET profile = profile || jsonb_build_object(
        'country', country,
        'email', email
    )
WHERE id = 42;

It is also clean to build an enriched copy right in a SELECT, never touching the table:

SELECT id,
       profile || jsonb_build_object('active', status = 'active') AS enriched
FROM users;

Gotcha: nested objects are replaced wholesale

The most common mistake is expecting || to deep-merge; it only works at the top level. If a right-hand key is an object, it replaces the left-hand object entirely instead of merging field by field.

SELECT '{"address": {"city": "Lima", "zip": "15001"}}'::jsonb
       || '{"address": {"city": "Quito"}}'::jsonb;
-- {"address": {"city": "Quito"}}

The zip key vanished because the right address overwrote the left one whole. To change just one nested field, reach for jsonb_set with a path:

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

The rule is simple: || is for flat patches and concatenation; jsonb_set is for surgical edits deeper in the tree.

Differences in other engines

• MySQL has no || operator for JSON (there it is logical OR). Merging is done with JSON_MERGE_PATCH(a, b), which behaves as a recursive patch and additionally drops keys whose value is null. There is also JSON_MERGE_PRESERVE, which collects values into arrays instead of replacing.
• ClickHouse is not built for partial JSON edits: its JSON functions are mostly read-oriented, and changing a document usually means rewriting the whole value.

Bottom line: in PostgreSQL || is a fast shallow merge where right-hand keys win; remember its shallowness and swap in jsonb_set when you need to go inside.