Updating Parts of a MySQL JSON Document in Place Safely

Changing one value inside a JSON column is a common job and an easy one to do wastefully. The naive approach reads the document in the application, changes it and writes it back. MySQL has functions that make the change in the database, and, under the right conditions, rewrite only a part of the stored value. It builds on the MySQL JSON guide and on JSON_EXTRACT and the arrow operators, which cover reading. This article goes through the update functions, the conditions for a partial update, what it does to the binary log, the integer-to-double trap in counters, and how to merge sets of changes.

The functions

Every update function takes the document and returns a new one, and you assign the result back to the column:

update products set attrs = json_set(attrs, '$.price', 9.9) where id = 1;

The family, with the behavior for a path that exists and one that does not:

JSON_SET is the general-purpose one. JSON_INSERT is useful for defaults ("add this key only if absent") and JSON_REPLACE for guarded changes ("change this key only if it exists"). All of them accept several path and value pairs in one call and apply them left to right: json_set(doc, '$.a', 1, '$.b', 2).

Rules that catch people

• Only the last step of a path is created. json_set('{"a":1}', '$.b.c', 2) returned {"a": 1} unchanged, because b does not exist. json_set('{"a":{"b":1}}', '$.a.c', 2) worked, since a exists. To create a nested key, set the parent as an object first: json_set(doc, '$.b', json_object('c', 2)).

• A string argument is a string. json_set('{"a":1}', '$.s', '{"x":1}') stored the text "{\"x\":1}", with escaped quotes. To store an object, pass json_object('x', 1) or cast('{"x":1}' as json).

• SQL NULL becomes JSON null. json_set('{"a":1}', '$.b', null) gave {"a": 1, "b": null}, and json_set('{"a":1}', '$.a', null) gave {"a": null}. If you meant to remove the key, use JSON_REMOVE.

• Array positions beyond the end append. json_set('{"a":[1,2]}', '$.a[5]', 9) returned {"a": [1, 2, 9]}, the element added at the end, not at index 5.

• **JSON_REMOVE renumbers.** Removing $.b[1] from [1,2,3] leaves [1, 3], so removing several elements by index in one call can remove the wrong ones. Remove from the highest index down, or rebuild the array.

For arrays, json_array_append(doc, '$.tags', 'c') adds a string element. When the value is itself an array it is appended as one nested element: after appending json_array('x','y'), the tags were ["a", "b", "c", ["x", "y"]]. To concatenate arrays, use JSON_MERGE_PRESERVE(doc -> '$.tags', '["x","y"]') and set the result back.

Partial updates in place

A JSON column in InnoDB is stored as a binary document. When an UPDATE uses JSON_SET, JSON_REPLACE or JSON_REMOVE on the column itself (set doc = json_set(doc, ...)), MySQL can modify the stored bytes directly instead of writing a new copy of the whole value. Conditions: the column is the only thing being assigned in that form, the function wraps the column directly (a deeper nesting of functions can defeat it), and the new value is no larger than the one it replaces.

You can watch it with JSON_STORAGE_SIZE and JSON_STORAGE_FREE, which report the document's size and the unused space inside it:

step                                         storage_size   storage_free
insert 20 KB document                        20058          0
set counter 7 to 123456789                   20062          0     (grew by 4 bytes: rewritten)
set blob to 'short'                          20062          19997 (smaller: done in place, space kept)
set blob to 30000 characters                 30062          0     (grew: rewritten)

When the new value fits, the document keeps its old size and the difference shows as free space. The update touched a few bytes, not twenty thousand. When it does not fit, the whole value is rewritten and the free count drops back to zero. If you shrink a value and then grow it back, the in-place space is reused, and an ordinary OPTIMIZE TABLE eventually reclaims it.

This matters for large documents with small, frequent changes: a counter or a flag in a 20 KB blob. It matters little for small documents, where the whole value is a few hundred bytes anyway.

What it does to the binary log

With row-based logging, MySQL logs the row image. By default, the whole JSON value goes into the log, before and after. I measured the log position before and after a one-key update on the 20 KB document:

update                                              binary log growth
default (binlog_row_value_options empty)            40,426 bytes
SET SESSION binlog_row_value_options='PARTIAL_JSON'  20,385 bytes
back to default                                      40,426 bytes

With PARTIAL_JSON, the after image carries only the change, in a JSON diff form, and the growth halved. It did not shrink further because binlog_row_image=FULL still writes the whole before image. Setting binlog_row_image=MINIMAL reduces that too, and both settings must be understood by every replica, which is a reason to roll them out together and test. The setting is empty by default, which means the savings are opt-in. For a workload that updates a small piece of a large document many times a second, it is among the largest levers on replication traffic. For small documents, it is not worth the operational risk.

Counters and the double trap

Incrementing a number inside a document looks easy:

update big_docs set doc = json_set(doc, '$.counter', doc -> '$.counter' + 1) where id = 1;

It works and changes the type. The counter was the integer 7. After this statement it was 8.0. The extraction doc -> '$.counter' is a JSON value, and adding 1 to a JSON number yields a double in MySQL arithmetic; json_set then stores a JSON double. After many increments, you will see a document full of .0 values and, past 2^53, lose exactness. To keep integers, convert explicitly:

update big_docs set doc = json_set(doc, '$.counter',
  json_value(doc, '$.counter' returning signed) + 1) where id = 1;

json_value(... returning signed) extracts an integer and the sum is an integer, and json_set stores it as one. A version using cast(doc ->> '$.counter' as signed) failed in my run with Truncated incorrect INTEGER value: '8.0' once the stored value had already become 8.0, which shows the damage persists: fix the data as well as the statement.

For a key that may not exist yet, wrap the read: json_set(doc, '$.hits', ifnull(json_value(doc, '$.hits' returning signed), 0) + 1) created $.hits as the integer 1, where the version with ifnull(doc -> '$.hits', 0) + 1 stored 1.0.

And think about whether the counter belongs in the document at all. Updates to the same row serialize on the row lock, and a hot counter is better in its own integer column or a table of counters that is aggregated later.

Merging a set of changes

JSON_MERGE_PATCH(doc, patch) follows RFC 7396: keys in the patch override, nested objects merge recursively, a null value removes the key, and arrays are replaced whole.

select json_merge_patch('{"a":1,"b":{"x":1,"y":2}}', '{"b":{"y":null,"z":3},"c":4}');
-- {"a": 1, "b": {"x": 1, "z": 3}, "c": 4}

This is the cleanest way to apply "these settings changed" from a form: the application sends only what changed, and null means delete. The limit is that you cannot set a key to JSON null with a patch, because null means removal; use JSON_SET for that. Compare JSON_MERGE_PRESERVE, which keeps both sides and builds arrays out of conflicts: {"a":1} merged with {"a":2} becomes {"a": [1, 2]}, which is almost never what you want for configuration. A patch update is not a partial update in the storage sense: it produces a whole new document.

Concurrency

Two sessions updating different keys of the same row with json_set(doc, ...) expressions are both safe. The second waits for the row lock, then applies its function to the committed value of the first, so neither change is lost. The unsafe pattern is read-modify-write in the application: read the document, edit a copy and write the whole thing, which overwrites a concurrent change to another key. Send the change, not the document. If the application must read first, use select ... for update inside the transaction. Parts of the document that you filter on usually want an index, as in indexing a MySQL JSON field with a generated column. The same reasoning is in updating one key inside a JSONB column for PostgreSQL.

Checking your update

An UPDATE that matches no path still reports the row as matched, and MySQL tells you whether data changed: Rows matched: 1 Changed: 0 appears for an update that left the document unchanged, such as json_replace on a missing key. Read that line in the client, or row_count() after the statement, and treat 0 changed rows as a signal in scripts. A before-and-after look at one document is easy with a JSON diff; the JSON diff tool on this site compares two documents in the browser.

When a partial update does not help

Small documents. Under about 1 KB the whole value is cheap to write, and the complexity of tuning the log gains nothing.

Updates that grow the value. Appending to an array, adding keys or lengthening a string rewrites the document each time, which is the common shape of event lists and logs. Use rows for those.

Indexed or generated fields. If a generated column or functional index depends on the key you change, MySQL must recompute and update the index entry, and the change cannot be limited to the document's bytes. An update to a high-churn indexed key costs as much as a column update plus the extraction.

Replicas or tools that do not understand the format. Partial logging has to be supported across your replication chain, change data capture tools and backup readers. Check each before turning it on.

The partial-update rules and the binary log option are documented in the MySQL manual under partial updates of JSON values and binlog_row_value_options. The merge semantics come from RFC 7396.