Mastering MySQL JSON: From Basics to Advanced Partial Updates

What Is JSON?

JSON (JavaScript Object Notation) is a lightweight, text‑based, language‑independent data‑exchange format that is easy to read and write.

JSON Data Type in MySQL

Starting with MySQL 5.7.8, MySQL supports a native JSON column type. Compared with storing JSON as CHAR, VARCHAR or TEXT, the native type validates format on insert, optimizes storage, and allows direct element access without loading the whole document.

import json
# JSON string
x = '{ "name":"John", "age":30, "city":"New York"}'
# Convert to Python object
y = json.loads(x)
# Access element
print(y["age"])

In MySQL you can create a JSON column and insert data directly:

CREATE TABLE t (c1 JSON);
INSERT INTO t VALUES ('{ "name":"John", "age":30, "city":"New York"}');
SELECT c1->"$.age" FROM t;

CRUD Operations on JSON Columns

Insert

You can insert raw JSON strings or use helper functions such as JSON_ARRAY() and JSON_OBJECT():

INSERT INTO t VALUES ('[1, "abc", null, true, "10:27:06.000000", {"id":1}]');
SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
SELECT JSON_OBJECT('id', 87, 'name', 'carrot');

Query

Retrieve elements with JSON_EXTRACT() (or the shorthand column->'$.path') and functions like JSON_CONTAINS() or JSON_SEARCH():

SELECT JSON_EXTRACT('[10,20,[30,40]]', '$[0]');
SELECT JSON_EXTRACT('[10,20,[30,40]]', '$[0]', '$[1]', '$[2][0]');
SELECT JSON_EXTRACT('[10,20,[30,40]]', '$[0 to 1]');
SELECT JSON_EXTRACT('[10,20,[30,40]]', '$[*]');
SELECT JSON_EXTRACT('{"a":1,"b":[2,3],"a c":4}', '$.a');
SELECT JSON_EXTRACT('{"a":1,"b":[2,3],"a c":4}', '$."a c"');
SELECT JSON_EXTRACT('{"a":1,"b":[2,3],"a c":4}', '$.b[1]');

Update

Three families of functions support in‑place modifications: JSON_INSERT(json_doc, path, val[, ...]) – inserts only when the target does not exist. JSON_SET(json_doc, path, val[, ...]) – inserts or replaces. JSON_REPLACE(json_doc, path, val[, ...]) – replaces only when the target exists.

SELECT JSON_INSERT('1', '$[0]', "10");
SELECT JSON_SET('1', '$[1]', "10");
SELECT JSON_REPLACE('{"a":1,"b":[2,3]}', '$.a', 10);
SELECT c2->>'$.ename', JSON_UNQUOTE(c2->'$.ename') FROM t;

Delete

Remove elements with JSON_REMOVE():

SELECT JSON_REMOVE('{"a":1,"b":[2,3]}', '$.a');
SELECT JSON_REMOVE('["a",["b","c"],"d","e"]', '$[1]');
SELECT JSON_REMOVE('["a",["b","c"],"d","e"]', '$[1]', '$[2]');

Indexing JSON Documents

Direct indexes on JSON columns are not allowed. Instead, create generated (virtual) columns that extract scalar values and index those columns:

CREATE TABLE t (
  c1 JSON,
  c2 VARCHAR(10) AS (JSON_UNQUOTE(c1->"$.name")),
  INDEX (c2)
);
INSERT INTO t (c1) VALUES ('{"id":1,"name":"a"}'), ('{"id":2,"name":"b"}');
EXPLAIN SELECT * FROM t WHERE c2='a';
EXPLAIN SELECT * FROM t WHERE c1->'$.name'='a';

Upgrading TEXT Columns to JSON

Before MySQL 5.7, JSON documents were stored in TEXT columns. To migrate:

Identify invalid JSON with JSON_VALID().

Fix the malformed rows.

Alter the column type to JSON.

SELECT * FROM t WHERE JSON_VALID(c1)=0;
UPDATE t SET c1='{"id":"4","name":"d"}' WHERE id=4;
ALTER TABLE t MODIFY c1 JSON;

Partial Updates of JSON Values

In MySQL 5.7, any UPDATE on a JSON column rewrites the whole document. MySQL 8.0 introduces Partial Updates, which modify JSON in‑place when the update uses JSON_SET, JSON_REPLACE or JSON_REMOVE on the same column.

Conditions for Partial Updates:

The target column is of type JSON.

The statement uses one of the three functions above.

The input and target columns are the same.

Space usage does not increase after the change.

Example showing storage size before and after a partial update:

SELECT *, JSON_STORAGE_SIZE(c1), JSON_STORAGE_FREE(c1) FROM t WHERE id=1;
UPDATE t SET c1=JSON_REMOVE(c1,'$.id') WHERE id=1;
SELECT *, JSON_STORAGE_SIZE(c1), JSON_STORAGE_FREE(c1) FROM t WHERE id=1;
UPDATE t SET c1=JSON_SET(c1,'$.id',3306) WHERE id=1;
SELECT *, JSON_STORAGE_SIZE(c1), JSON_STORAGE_FREE(c1) FROM t WHERE id=1;

To enable Partial Updates in binary logs, set the server variable binlog_row_value_options='PARTIAL_JSON'. When enabled, the binlog records only the JSON function call instead of the full document, reducing log size dramatically.

Performance Test

A benchmark updates a 10 MB JSON document in a table of 16 rows under four scenarios (MySQL 5.7, MySQL 8.0, MySQL 8.0 with Partial Updates, and MySQL 8.0 with Partial Updates + binlog_row_image=MINIMAL). The results show:

MySQL 8.0 with storage‑engine Partial Updates is ~1.94× faster than 5.7.

Enabling Partial Updates in the binlog yields ~4.87× speedup.

Adding MINIMAL binlog image reaches >100× improvement.

Other Useful JSON Functions

MySQL provides a rich set of functions for searching, extracting, aggregating, and manipulating JSON data. Highlights include: JSON_CONTAINS(target, candidate[, path]) – tests containment. JSON_CONTAINS_PATH(json_doc, one_or_all, path[, ...]) – checks path existence.

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

– returns paths of matching strings. JSON_KEYS(json_doc[, path]) – returns top‑level keys. JSON_VALUE(json_doc, path) – extracts a scalar value (8.0.21+). value MEMBER OF(json_array) – tests array membership. JSON_OVERLAPS(json_doc1, json_doc2) – checks overlapping keys or array elements (8.0.17+). JSON_ARRAY_APPEND() and JSON_ARRAY_INSERT() – modify arrays. JSON_MERGE_PATCH() and JSON_MERGE_PRESERVE() – merge documents with different rules. JSON_QUOTE(), JSON_UNQUOTE() – handle escaping. JSON_OBJECTAGG(), JSON_ARRAYAGG() – aggregate rows into JSON objects or arrays. JSON_PRETTY() – pretty‑print JSON. JSON_STORAGE_SIZE() and JSON_STORAGE_FREE() – report storage usage (8.0+). JSON_DEPTH(), JSON_LENGTH(), JSON_TYPE() – introspection utilities. JSON_VALID() – validates JSON syntax. JSON_TABLE() – projects JSON data as a relational table. JSON_SCHEMA_VALID() and JSON_SCHEMA_VALIDATION_REPORT() – validate against a JSON Schema.

Conclusion

For production workloads that store JSON, MySQL 8.0 is the recommended version. Its Partial Update feature delivers substantial performance gains, and the ability to index extracted values via virtual columns makes JSON queries efficient. Enabling binlog_row_value_options='PARTIAL_JSON' further reduces replication overhead.

References

JSON – https://zh.wikipedia.org/wiki/JSON

The JSON Data Type – https://dev.mysql.com/doc/refman/8.0/en/json.html

JSON Functions – https://dev.mysql.com/doc/refman/8.0/en/json-functions.html

Upgrading JSON data stored in TEXT columns – https://dev.mysql.com/blog-archive/upgrading-json-data-stored-in-text-columns/

Indexing JSON documents via Virtual Columns – https://dev.mysql.com/blog-archive/indexing-json-documents-via-virtual-columns/

Partial update of JSON values – https://dev.mysql.com/blog-archive/partial-update-of-json-values/

MySQL 8.0: InnoDB Introduces LOB Index For Faster Updates – https://dev.mysql.com/blog-archive/mysql-8-0-innodb-introduces-lob-index-for-faster-updates/