Production checklist: indexes and true partial updates
Calling JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() does not guarantee an in-place storage update. MySQL applies that optimization only when all documented conditions are satisfied. JSON_ARRAY_APPEND() is not one of the three functions eligible for this optimization. These functions perform logical document updates; they do not promise that MySQL writes only the changed bytes.
Make the indexed scalar explicit
A JSON document itself is not a conventional B-tree key. For a path used in filters, joins, or ordering, extract a scalar into a generated column with an intentional SQL type, length, character set, and collation, then index that column. Querying the generated column by name is the least ambiguous contract between the schema and application:
ALTER TABLE products
ADD COLUMN sku_key VARCHAR(50)
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.sku'))) STORED,
ADD INDEX idx_products_sku_key (sku_key);
EXPLAIN SELECT id, name
FROM products
WHERE sku_key = 'WP-001';If a query repeats the JSON expression instead, MySQL can substitute an indexed generated column only when the expression is identical and has the same result type. Operand order, quoting, casts, and collation can prevent a match. Oracle's generated-column optimizer documentation also explains why JSON_UNQUOTE() matters for string comparisons. Confirm that EXPLAIN reports the intended key after realistic statistics are loaded; do not infer index use merely because the index exists.
Before adding the column, profile existing documents. Decide how a missing path, JSON null, numeric values, arrays, objects, and strings longer than the chosen key length should behave. Test the DDL against a production-shaped copy so conversion errors, truncation, duplicate values for a proposed unique index, and build time appear before deployment. Index only paths that support measured access patterns: every extra generated index consumes space and adds work to inserts and updates. MySQL also supports multi-valued indexes for selected JSON-array use cases, but their syntax and operator restrictions differ from a scalar generated-column index; consult the current multi-valued index reference before choosing one.
When an on-disk partial update is possible
For MySQL 8.4, an eligible update must target a column declared as JSON, use JSON_SET(), JSON_REPLACE(), or JSON_REMOVE(), and use the same JSON column as both input and target. Replacements must address existing object or array values rather than add new elements, and a replacement normally cannot be larger than the value it replaces unless a previous partial update left enough free space. Nested calls can qualify when they preserve the same input and target column. Use JSON_STORAGE_FREE() to inspect space released by prior partial updates and JSON_STORAGE_SIZE() as an observation aid, not as a correctness test.
Storage optimization and replication encoding are separate decisions. With row-based or mixed binary logging, compact partial JSON after-images are disabled by default. Setting binlog_row_value_options=PARTIAL_JSON can reduce after-image size for eligible updates, while binlog_row_image still affects the before-image. Oracle's binary-log variable reference warns that a replica errors if it cannot apply a partial modification and that divergent source and replica documents can still produce an unexpected valid result. Test failover, point-in-time recovery, CDC consumers, and mysqlbinlog --verbose workflows before enabling it.
Validation before rollout
- Run representative equality, range, join, and ordering queries through
EXPLAIN; check the selected key and estimated rows. - Compare query results from the generated column with the original JSON expression, including malformed business values and missing paths.
- Measure DDL duration, write amplification, redo and binary-log volume, and replica lag on a production-shaped copy.
- Exercise backup restore and replication with both qualifying and non-qualifying JSON updates. MySQL can fall back to a full-document update, and correctness must not depend on the optimization firing.
For broader schema tradeoffs, see the MySQL JSON performance and schema guide.