PostgreSQL: Merge JSON Objects

When you’re working with configuration data, user profiles, or any system that builds JSON objects from multiple sources, you’ll often need to combine them. Maybe you’re merging user metadata with profile data, or combining API response objects, or building a configuration by layering defaults with overrides. Merging JSON objects is a common pattern, and PostgreSQL makes it dead simple with the concatenation operator.

The elegance of PostgreSQL’s approach is that you don’t need complex functions—just a simple operator that says “combine these objects, with the right side overwriting any overlapping keys.”

Quick Answer: Use || Operator

To merge JSON objects:

SELECT
    obj1,
    obj2,
    obj1 || obj2 as merged
FROM (
    SELECT
        '{"name":"John","age":30}'::JSONB as obj1,
        '{"city":"NYC","country":"USA"}'::JSONB as obj2
) sub;

Returns:

{"name":"John","age":30,"city":"NYC","country":"USA"}

The || operator merges objects, with right side overwriting duplicate keys.

How It Works

The || operator concatenates JSONB objects. If keys overlap, the right operand wins. It’s a left-to-right merge.

Different Approaches & When to Use Them

Approach 1: Simple Merge

SELECT obj1 || obj2 as merged FROM table_name;

Approach 2: Multi-Object Merge

SELECT obj1 || obj2 || obj3 as all_merged FROM table_name;

Chain as many as needed.

Approach 3: Conditional Merge

SELECT
    CASE
        WHEN condition THEN obj1 || new_obj
        ELSE obj1
    END as conditional_merge
FROM table_name;

Approach 4: Update with Merge

UPDATE users
SET metadata = metadata || '{"last_login":"'||NOW()||'"}'::JSONB
WHERE user_id = 123;

Add or update properties.

Real-World Example: Merge User Profiles

Combine default settings with user-specific preferences:

SELECT
    user_id,
    '{"theme":"light","notifications":true}'::JSONB as defaults,
    user_preferences,
    '{"theme":"light","notifications":true}'::JSONB || user_preferences as final_settings
FROM users
WHERE user_preferences IS NOT NULL;

User preferences override defaults.

FAQ

Q: What if keys overlap? A: Right operand overwrites: {"a":1} || {"a":2} = {"a":2}.

Q: Can I control merge behavior? A: No direct control, but left side wins if you reverse order.

Q: Does merge preserve array order? A: Object merge is straightforward. Array behavior within objects depends on structure.

Q: Performance for large objects? A: Very fast, even for thousands of keys.

Wrapping Up

Merge JSON objects with the || operator. Right-side values override left-side duplicates. Simple, fast, and powerful.