Updating One Key Inside a JSONB Column Without Losing Data

You have a JSONB column with a settings blob, a metadata object or a list of attributes, and you need to change one thing in it: set a language, flip a flag, add one to a counter. There is no doc.meta.lang = 'fr' in SQL, so the update is a function that returns a new document. This article shows the forms that work, with the exact outputs, the error you will hit first, how to apply a change safely when two writers are active, and what an update costs in storage, which is more than it looks like.

The basic form

jsonb_set(target, path, new_value) returns a copy of target with the value at path replaced. The path is a text array, and the new value is JSONB:

update upd
set doc = jsonb_set(doc, '{meta,lang}', to_jsonb('fr'::text))
where id = 2
returning doc #>> '{meta,lang}';

It returned fr. The most common first error comes from the third argument. Passing the plain string 'fr' fails:

ERROR:  invalid input syntax for type json
DETAIL:  Token "fr" is invalid.

The argument is parsed as JSON, and fr is not JSON. A JSON string needs its quotes, so '"fr"' also works, as does to_jsonb('fr'::text), and the latter is the one to use when the value comes from a column or a parameter, since it escapes whatever it is given. Numbers and booleans are valid JSON as they stand: 'false' and '12' can be passed directly, and jsonb_set(doc, '{meta,draft}', 'false') flipped the draft flag in my test on three rows at once.

The subscript form

PostgreSQL 14 added subscripting to JSONB, and it works in UPDATE:

update upd set doc['views'] = to_jsonb((doc['views'])::int + 1) where id = 1 returning doc;

The document came back with "views": 3 where it had been 2. The subscript form reads like the code you would write in an application, and it differs from jsonb_set in one useful way: it creates missing parents. The article on adding and removing keys with jsonb_set and minus goes through the case where jsonb_set silently does nothing when an intermediate key is missing. Subscripting does what you meant, and builds the whole path.

Note the cast: doc['views'] is jsonb, so adding one needs ::int (a JSONB number casts directly to a numeric type in PostgreSQL 11 and later), and the result goes back through to_jsonb. Writing (doc -> 'views')::numeric + 1 and wrapping it with ::text::jsonb also works, and is longer.

Incrementing and other computed changes

The point of an update statement is that the new value can be computed from the old one in the same statement:

update upd
set doc = jsonb_set(doc, '{views}', to_jsonb((doc ->> 'views')::int + 1))
where id = 1;

The statement reads doc, builds the new document and writes it, all against the same row version. That matters when two writers are active. The mistake to avoid is the read-modify-write cycle in application code:

Session A:  select doc from upd where id = 1;          -- sees {"views": 3, "meta": {"lang": "en"}}
Session B:  update upd set doc = jsonb_set(doc, '{meta,lang}', '"fr"') where id = 1;
Session A:  (changes views in memory)
            update upd set doc = '{"views": 4, "meta": {"lang": "en"}}' where id = 1;

Session B's change to lang is gone, because session A wrote the whole document back from its stale copy. Nothing raises an error. The fix is to send the change and let the database apply it: session A runs set doc = jsonb_set(doc, '{views}', ...), which is evaluated against the latest committed version of the row. Under the default READ COMMITTED isolation level, if B has the row locked, A's update waits for it to commit and then re-reads the row, so both changes survive. If you need to read a value before deciding, select ... for update takes the lock first, or use SERIALIZABLE and retry on a serialization failure.

Updating only the rows that need it

Writing a document costs the same whether or not the value changed. If you set lang to 'fr' on rows that already say fr, you still produce a new row version for each. Filter by what you are changing:

update upd
set doc = jsonb_set(doc, '{meta,draft}', 'false')
where doc @> '{"meta": {"draft": true}}';

The containment test selects only the draft rows and can use a GIN index, as covered in indexing JSONB with GIN and when it helps. For a bulk change over millions of rows, update in batches (by id range) and commit each, so that locks are short and vacuum can keep up.

When the change is to merge in a small object, || does it at the top level: doc || '{"reviewed": true}'. Remember that || replaces nested objects whole, which the hub guide on JSONB shows with the specs example.

What an update costs

PostgreSQL never changes a row in place. An update writes a complete new row version, and the old one stays until vacuum removes it. For a JSONB column that means the whole document is written for a change to one key. A 300 byte document makes this unnoticeable. A 50 KB document turns a one-byte change into 50 KB of writes plus the compression work, plus WAL for all of it.

There is a second cost that is easy to miss: HOT updates. A heap-only tuple update avoids touching indexes when no indexed column changed and the new row fits on the same page. A GIN index on the JSONB column counts as indexing the column, so any change to the document disqualifies the update from being HOT. I measured it on 3,000 updates of the same shape, with the table at a fill factor of 70 to leave room on the page:

setup     n_tup_upd   n_tup_hot_upd
no gin    3000        1261
with gin  3000        0

Without the GIN index, 1,261 of the updates were HOT; with it, none were. Each non-HOT update has to add entries to every index, and a GIN index defers some of that work to a pending list and a vacuum cycle. If a table takes frequent updates, think hard before indexing the whole JSONB column; an index on one extracted field costs only when that field changes. The same measurement without the fill factor change showed zero HOT updates on both, since a table loaded to the brim has no room on the page: if updates matter, set fillfactor below 100.

Large documents and TOAST

Past roughly 2 KB a document is stored out of line in a TOAST table, compressed if possible. An update that changes only a small key still writes a new TOAST copy of the whole value. Reading one key out of a large document has to detoast it too: in the hub guide a filter over 4,000 rows with 6 KB documents took 7 ms against 0.2 ms for a key lookup in a column. When a document is large, the structure is usually a hint: split it into a small frequently changed part and a large rarely changed part, in two columns or two tables.

Related patterns

If the key might not exist, the subscript form creates it, and jsonb_set with the last key missing creates that key but not missing parents. If you need "set it only if it is absent", write the condition: where not (doc ? 'migrated'). If the new value might be SQL NULL, jsonb_set returns NULL for the whole document, so wrap it with coalesce(to_jsonb(value), 'null'::jsonb) or use jsonb_set_lax, which lets you choose the behavior.

To audit changes, a trigger can store the old and new documents in a history table, or a plain jsonb_diff-style query can compare them, though PostgreSQL has no built-in JSON diff. The JSON diff tool on this site compares two documents in the browser, useful when you want to see exactly what an update changed.

When a counter or hot field belongs in a column

A document is the wrong container for a value that changes constantly or that you filter on constantly.

Counters. A view counter inside a JSONB blob means each increment writes the whole document. At a hundred increments a second on a 2 KB document, that is 200 KB a second of heap writes, WAL, dead tuples and vacuum work for something an integer column does in 8 bytes with a HOT update. In my test the incremented value was views; I would move it to a column the day it mattered.

Status fields in a queue. Anything with where doc ->> 'status' = 'pending' in the hot path wants a real column with an index, so the planner has statistics and the index stays small. The route is a generated column, then, if you need to write to it directly, a migration.

Values with constraints. A column can be NOT NULL, CHECK (views >= 0) or a foreign key. A key in a document can only get a CHECK on the expression.

Anything that two services update independently. Separate columns, or separate rows, make concurrent updates safe without relying on everyone using jsonb_set correctly.

Update statements for JSON are documented in the manual under JSON functions, and the subscripting syntax in JSONB subscripting. For the visibility rules that make the concurrent-update behavior work, read the manual's chapter on concurrency control.