How to Merge JSONB Objects in PostgreSQL Easily

In PostgreSQL, you can merge jsonb objects using the || operator, which combines two JSONB values into one. If keys overlap, the right operand's values overwrite the left's. For more control, use jsonb_set to update specific keys.

Syntax

The main way to merge two jsonb objects is using the || operator. It combines two JSONB values into one, merging their keys and values.

• jsonb1 || jsonb2: Merges jsonb2 into jsonb1. If keys overlap, jsonb2 values overwrite jsonb1.

Another function is jsonb_set, which updates or adds a value at a specified path inside a JSONB object.

sql

SELECT '{"a":1, "b":2}'::jsonb || '{"b":3, "c":4}'::jsonb AS merged_jsonb;

Output

{"a": 1, "b": 3, "c": 4}

Example

This example shows how to merge two JSONB objects using the || operator and how overlapping keys are handled.

sql

WITH data AS (
  SELECT
    '{"name": "Alice", "age": 30}'::jsonb AS jsonb1,
    '{"age": 31, "city": "NY"}'::jsonb AS jsonb2
)
SELECT
  jsonb1,
  jsonb2,
  jsonb1 || jsonb2 AS merged
FROM data;

Output

{"name": "Alice", "age": 30}
{"age": 31, "city": "NY"}
{"name": "Alice", "age": 31, "city": "NY"}

Common Pitfalls

One common mistake is expecting the || operator to merge nested objects deeply. It only merges top-level keys, so nested objects with the same key will be replaced, not merged.

Also, using jsonb_set incorrectly can overwrite entire sub-objects if the path is not specified properly.

sql

SELECT
  '{"a": {"x": 1}}'::jsonb || '{"a": {"y": 2}}'::jsonb AS shallow_merge,
  jsonb_set('{"a": {"x": 1}}'::jsonb, '{a,y}', '2'::jsonb, true) AS deep_update;

Output

{"a": {"y": 2}}
{"a": {"x": 1, "y": 2}}

Quick Reference

• || operator: Merges two JSONB objects, right overwrites left on key conflict.
• jsonb_set(jsonb, path, new_value): Updates or adds a value at the specified path.
• Deep merging nested objects requires custom functions or multiple jsonb_set calls.

Key Takeaways

Use the || operator to merge two jsonb objects with right-side keys overwriting left-side keys.

The || operator merges only top-level keys; nested objects are replaced, not merged deeply.

Use jsonb_set to update or add values at specific paths inside a jsonb object.

For deep merging of nested jsonb objects, you need custom functions or multiple jsonb_set calls.

Always test your merge queries to ensure the output matches your expected JSON structure.