But there is one update pattern that can quietly become expensive:
UPDATE some_table
SET data = $1::jsonb
WHERE id = $2;
That statement replaces the entire JSONB document.
Even if your application only changed one key, the database receives a brand-new JSONB value. If that JSONB column feeds generated columns or secondary indexes, YugabyteDB may have extra work to do to figure out what actually changed.
A better pattern is to update only the JSON keys that changed.
- ●
jsonb_set() - ● the JSONB merge operator:
||
Let’s walk through both.
Demo Setup
Create a simple table with a JSONB column.
DROP TABLE IF EXISTS profile_demo;
CREATE TABLE profile_demo (
id UUID PRIMARY KEY,
data JSONB NOT NULL
);
INSERT INTO profile_demo (id, data)
VALUES (
'11111111-1111-1111-1111-111111111111',
'{
"name": "Lucy",
"status": "active",
"plan": "basic",
"settings": {
"theme": "dark",
"notifications": true
}
}'
);
Check the starting data:
SELECT jsonb_pretty(data)
FROM profile_demo
WHERE id = '11111111-1111-1111-1111-111111111111';
Result:
{
"name": "Lucy",
"plan": "basic",
"status": "active",
"settings": {
"theme": "dark",
"notifications": true
}
}
The Heavy-Handed Pattern: Replace the Whole JSONB Document
A common application pattern is to send the entire JSON document back to the database:
UPDATE profile_demo
SET data = '{
"name": "Alice",
"status": "inactive",
"plan": "basic",
"settings": {
"theme": "dark",
"notifications": true
}
}'::jsonb
WHERE id = '11111111-1111-1111-1111-111111111111';
That works.
But it replaces the entire JSONB value, even though only status changed.
On a simple table, that may not matter much. But on a table where JSONB feeds generated columns and indexes, this can become expensive.
Better Option 1: Use jsonb_set()
The jsonb_set() function lets you surgically update one path inside a JSONB document.
Syntax:
jsonb_set(target_jsonb, path, new_value, create_missing)
For example, to update only the top-level status key:
UPDATE profile_demo
SET data = jsonb_set(data, '{status}', '"inactive"'::jsonb, true)
WHERE id = '11111111-1111-1111-1111-111111111111';
Check the result:
SELECT jsonb_pretty(data)
FROM profile_demo
WHERE id = '11111111-1111-1111-1111-111111111111';
Result:
{
"name": "Lucy",
"plan": "basic",
"status": "inactive",
"settings": {
"theme": "dark",
"notifications": true
}
}
Only status changed.
Everything else stayed the same.
Updating a Nested JSONB Key
The path argument can point to nested keys too.
For example, to update settings.theme from dark to light:
UPDATE profile_demo
SET data = jsonb_set(data, '{settings,theme}', '"light"'::jsonb, true)
WHERE id = '11111111-1111-1111-1111-111111111111';
Check the result:
SELECT jsonb_pretty(data)
FROM profile_demo
WHERE id = '11111111-1111-1111-1111-111111111111';
Result:
{
"name": "Lucy",
"plan": "basic",
"status": "inactive",
"settings": {
"theme": "light",
"notifications": true
}
}
That is the big advantage of jsonb_set().
It can update a specific nested path without rebuilding the entire object in your application.
Parameterized jsonb_set() Example
If your application currently does something like this:
UPDATE profile_demo
SET data = $1::jsonb
WHERE id = $2::uuid;
You can change it to update a known JSON path:
UPDATE profile_demo
SET data = jsonb_set(data, '{status}', $1::jsonb, true)
WHERE id = $2::uuid;
The value passed for $1 must be valid JSONB.
For a string value, that means the parameter should look like this:
"inactive"
not just:
inactive
In SQL form:
UPDATE profile_demo
SET data = jsonb_set(data, '{status}', '"inactive"'::jsonb, true)
WHERE id = '11111111-1111-1111-1111-111111111111';
Better Option 2: Use the JSONB Merge Operator
For top-level keys, the JSONB merge operator is often easier for application code.
The operator is:
||
It merges one JSONB object into another.
Example:
UPDATE profile_demo
SET data = data || '{"plan": "premium"}'::jsonb
WHERE id = '11111111-1111-1111-1111-111111111111';
Check the result:
SELECT jsonb_pretty(data)
FROM profile_demo
WHERE id = '11111111-1111-1111-1111-111111111111';
Result:
{
"name": "Lucy",
"plan": "premium",
"status": "inactive",
"settings": {
"theme": "light",
"notifications": true
}
}
The merge operator updated plan and left everything else alone.
Updating Multiple Top-Level Keys at Once
The merge operator is especially nice when your application wants to update several top-level keys in one statement.
UPDATE profile_demo
SET data = data || '{
"status": "active",
"plan": "enterprise"
}'::jsonb
WHERE id = '11111111-1111-1111-1111-111111111111';
Result:
{
"name": "Alice",
"plan": "enterprise",
"status": "active",
"settings": {
"theme": "light",
"notifications": true
}
}
This makes parameterized application SQL simple:
UPDATE profile_demo
SET data = data || $1::jsonb
WHERE id = $2::uuid;
Then the application can pass a small partial JSON document:
{
"status": "active",
"plan": "enterprise"
}
…instead of sending the full JSONB payload.
Important Difference: jsonb_set() vs. ||
Use jsonb_set() when you need to update a specific nested path. Use the || merge operator when your application wants to merge one or more top-level JSON keys into the existing document.
Why This Matters in YugabyteDB
Replacing an entire JSONB document can make a small application change look like a much larger database change.
If your application only changed one key, but sends the full JSONB payload back to the database, YugabyteDB still has to evaluate the new JSONB value as a whole. On a simple table, that may not be a big deal.
But many real-world schemas use JSONB as the source for generated columns, indexes, or other query-friendly structures. In those cases, full-document replacement can create extra work during updates.
That is why jsonb_set() and the || merge operator are useful patterns. They let the application describe the change more precisely:
- ● Update this one JSON path.
- ● Or merge these few top-level keys.
We look at the generated column and index side of this in more detail in the follow-up tip, When Generated Columns and Indexes Turn a Simple JSONB Update into a Distributed Write Amplifier.
For now, the best practice is simple:
- When only part of a JSONB document changed, avoid replacing the whole thing.
jsonb_set can still be effective, especially when indexes are involved, but YugabyteDB does not currently perform surgical JSON updates in the DocDB storage layer. Even when updating a single JSON property, DocDB still reads the full JSON value, merges the change, and rewrites the full JSON value. In other words,
jsonb_set helps you express a targeted update at the YSQL/PostgreSQL layer, but the underlying storage write is still for the full JSON document today. An enhancement request has been submitted to track this optimization. Thanks to Srinivasa Vasu, Distinguished Engineer at Yugabyte, for pointing this out.
Final Takeaway
Targeted JSONB updates make your SQL clearer and help avoid unnecessary data churn.
Use jsonb_set() when you need to update a specific JSON path, and use the || merge operator when you only need to merge a few top-level keys.
This matters even more when JSONB values feed generated columns or secondary indexes, as shown in the follow-up tip: When Generated Columns and Indexes Turn a Simple JSONB Update into a Distributed Write Amplifier.
Have Fun!
