Right, so you’ve got your JSONB document. It’s a beautiful, nested snowflake, perfect and pristine. And now you need to change it. Of course you do. Data isn’t a museum exhibit; it’s a living, breathing, frequently-misconfigured mess that needs constant tweaking. Let’s talk about how to perform surgery on these JSONB structures without leaving a bloody mess everywhere.

The workhorse here is jsonb_set(). Don’t let the simple name fool you; it’s deceptively powerful and, like most powerful things, easy to misuse. Its job is to replace or set the value at a specified path.

Here’s its signature. Don’t worry, I’ll explain it:

jsonb_set(target jsonb, path text[], new_value jsonb, [create_missing boolean])
  • target: Your original JSONB document.
  • path: An array of text keys that defines the navigation path to the value you want to change (e.g., '{owner, address, city}').
  • new_value: The new JSONB value you want to plop in there.
  • create_missing (optional): This is the crucial one. If true, it will create the parent objects if they don’t exist. Defaults to false, meaning if the path isn’t fully there, the function gives up and returns the original target unchanged. This is a classic foot-gun, which we’ll cover in a moment.

The Basics: Simple Replacement

Let’s start with a straightforward update. You have a user record and their apartment number changed. Tough break.

SELECT jsonb_set(
    '{"name": "Alice", "address": {"street": "123 Main St", "city": "Portland", "apartment": "1A"}}'::jsonb,
    '{address, apartment}',
    '"2B"'::jsonb
) AS updated_data;

Result:

{"name": "Alice", "address": {"city": "Portland", "street": "123 Main St", "apartment": "2B"}}

Notice the new value '"2B"' is cast to JSONB. You must do this. Forgetting the quotes and trying to use just 2B will result in a syntax error, because 2B is not valid JSON; it’s a string, and strings must be quoted. This is the most common rookie mistake with this function.

The create_missing Flag: Your Best Friend and Worst Enemy

This is where the designers gave us a choice, and the default choice is, frankly, a bit passive-aggressive. Let’s see what happens when you try to set a path that doesn’t exist.

-- We want to add a "country" field under "address", but it doesn't exist yet.
SELECT jsonb_set(
    '{"name": "Alice", "address": {"street": "123 Main St"}}'::jsonb,
    '{address, country}',
    '"USA"'::jsonb,
    false -- Default behavior: don't create missing parents
) AS no_create;

-- Now try it with the flag set to true
SELECT jsonb_set(
    '{"name": "Alice", "address": {"street": "123 Main St"}}'::jsonb,
    '{address, country}',
    '"USA"'::jsonb,
    true -- The useful behavior: build the path for me!
) AS with_create;

Result for no_create: The original document is returned untouched. No error, no change. Just silent, crushing disappointment. Result for with_create: {"name": "Alice", "address": {"street": "123 Main St", "country": "USA"}}

See what I mean? The default behavior is to do nothing if it can’t find the exact path. Always ask yourself: “If this path doesn’t exist, do I want to create it or fail?” 99% of the time, you want create_missing := true. I genuinely don’t know why this isn’t the default; it seems like a triumph of pedantry over practicality.

The Concatenation Operator: Smashing Objects Together

Sometimes you don’t want to surgically replace one field; you want to merge a whole bunch of new fields into an existing object. For that, we have the concatenation operator: ||.

-- Let's add a bunch of new metadata to Alice's record in one go
SELECT '{"name": "Alice", "preferences": {}}'::jsonb
       ||
       '{"preferences": {"theme": "dark", "email_opt_in": true}, "last_login": "2023-10-27"}'::jsonb
       AS merged_data;

Result:

{"name": "Alice", "last_login": "2023-10-27", "preferences": {"email_opt_in": true, "theme": "dark"}}

Crucially, notice what happened to the preferences object. The operator didn’t just add it; it recursively merged it. The empty preferences object from the left was merged with the {"theme": "dark", ...} object on the right. This is incredibly useful for applying default settings or adding a batch of properties.

But be warned: it’s a merge, not an insert. If the same key exists in both objects, the value from the right-hand operand wins. It’s a straight overwrite.

A Powerful Combination: Setting and Concatenating

The real magic happens when you combine these tools. Need to add an object to an array deep within a document? You’ll use jsonb_set to target the array and the concatenation operator to append to it.

-- Let's add a new phone number to Alice's array of phones.
-- First, we extract the current array, then we concatenate the new number onto it,
-- and then we use jsonb_set to put the new, larger array back.

SELECT jsonb_set(
    '{"name": "Alice", "phones": ["555-1234"]}'::jsonb,
    '{phones}',
    ('{"phones": []}'::jsonb -> 'phones') || '"555-5678"'::jsonb, -- This extracts the array and concats the new value
    true
) AS updated_phones;

Result:

{"name": "Alice", "phones": ["555-1234", "555-5678"]}

This pattern—extract, modify, set—is the cornerstone of all complex JSONB manipulation. You’re treating the path like a variable, changing it, and then assigning it back. It feels a bit like assembly language for JSON, but it works and it’s rock-solid.

The key takeaway? jsonb_set is for precise, path-based edits. The || operator is for bulk merges and appends. Use them together, always remember to cast your new values to JSONB, and for the love of all that is holy, never forget the create_missing flag. Set it to true and never look back.