Merging and Modifying JSON Objects with the || Operator and the jsonb_concat() Function

If JSONB is like a magic chest where we stash our data, then || and jsonb_concat() are the tools that let us merge those chests or mess with what's inside. In real life, you might need to combine a couple of JSON objects, add stuff from one to another, or merge arrays into a single list.

For example, imagine you have two JSONB objects:

{"name": "Alice", "age": 25}

and

{"city": "Wonderland", "hobbies": ["reading", "chess"]}

You want to get:

{"name": "Alice", "age": 25, "city": "Wonderland", "hobbies": ["reading", "chess"]}

Or merge two JSONB arrays:

[1, 2, 3]

and

[4, 5, 6]

so the result looks like:

[1, 2, 3, 4, 5, 6]

You do all this with || or jsonb_concat(). Let's break down how it works.

The || Operator for Merging JSONB

The || operator lets you merge two JSONB objects or arrays in PostgreSQL. It's simple, fast, and super easy to use. Here are the main rules:

• If you merge two JSONB objects, the result will have keys and values from both objects.
• If keys overlap, the value from the right operand overwrites the one from the left.
• If you merge JSONB arrays, the elements from the left and right arrays get combined into one array.

Example 1: Merging two JSONB objects

SELECT '{"name": "Alice", "age": 25}'::jsonb || '{"city": "Wonderland", "hobbies": ["reading", "chess"]}'::jsonb AS merged_object;

Result:

{"name": "Alice", "age": 25, "city": "Wonderland", "hobbies": ["reading", "chess"]}

Example 2: Updating values when keys overlap

SELECT '{"name": "Alice", "age": 25}'::jsonb || '{"age": 30, "city": "Wonderland"}'::jsonb AS updated_object;

Result:

{"name": "Alice", "age": 30, "city": "Wonderland"}

Notice that the value for the "age" key from the right object replaced the one from the left.

Example 3: Merging arrays

SELECT '[1, 2, 3]'::jsonb || '[4, 5, 6]'::jsonb AS merged_array;

Result:

[1, 2, 3, 4, 5, 6]

The jsonb_concat() Function for Merging JSONB

The jsonb_concat() function works just like the || operator, but gives you more flexibility if you need to use it inside functions, triggers, or dynamic queries. It takes two JSONB arguments and returns the merged result.

Example: using jsonb_concat()

SELECT jsonb_concat('{"a": 1, "b": 2}'::jsonb, '{"b": 3, "c": 4}'::jsonb) AS combined;

Result:

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

Merging Objects and Arrays: Details and Gotchas

When merging objects with overlapping keys, remember that the values from the right object win.

For example:

SELECT '{"key1": "value1"}'::jsonb || '{"key1": "value2"}'::jsonb AS result;

Result:

{"key1": "value2"}

If you want to avoid overwriting values, you should store such data under different keys or use another approach (like an array).

But arrays always get merged by adding elements from the right array to the left one. For example:

SELECT '["a", "b"]'::jsonb || '["c", "d"]'::jsonb AS result;

Result:

["a", "b", "c", "d"]

If there are objects inside the array, the order of objects is preserved:

SELECT '[{"id": 1}, {"id": 2}]'::jsonb || '[{"id": 3}]'::jsonb AS result;

Result:

[{"id": 1}, {"id": 2}, {"id": 3}]

Practical Examples

Updating a user profile. Let's say we have a users table where profiles are stored as JSONB:

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

INSERT INTO users (profile) VALUES ('{"name": "Alice", "age": 25}');

Now we want to add a city of residence:

UPDATE users 
SET profile = profile || '{"city": "Wonderland"}'
WHERE id = 1;

Query result:

{"name": "Alice", "age": 25, "city": "Wonderland"}

Merging order data. Suppose we have an orders table:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    details JSONB
);

INSERT INTO orders (details) VALUES ('{"items": [{"product": "laptop", "quantity": 1}]}');

Now we're adding another product to the order:

UPDATE orders
SET details = jsonb_set(
    details,
    '{items}',
    details->'items' || '[{"product": "mouse", "quantity": 2}]'::jsonb
)
WHERE id = 1;

Query result:

{"items": [{"product": "laptop", "quantity": 1}, {"product": "mouse", "quantity": 2}]}

Differences Between || and jsonb_concat()

Functionally, the || operator and the jsonb_concat() function are identical. Use || for simple queries since it's more concise. The jsonb_concat() function is handy when you need to call it explicitly inside a program or trigger.

Common Mistakes and How to Avoid Them

Mistake: trying to merge incompatible types.

SELECT '{"key": "value"}'::jsonb || '["value"]'::jsonb;

Result:

ERROR:  cannot concatenate jsonb objects and arrays

Here, the left side is an object and the right side is an array — PostgreSQL can't just mash them together. For this to work, both operands need to be the same type: either two objects or two arrays.

Gotcha: missing indexes when working with JSONB

If you often filter data by values inside JSONB fields and you don't have indexes — your queries can get really slow. It's not a classic "error," but the performance hit is real. Don't forget to use GIN indexes:

CREATE INDEX idx_profile_data ON employees USING gin(profile);