Master MySQL JSON: From Basics to Advanced CRUD Operations

Introduction to MySQL JSON Data Type

MySQL added native support for the JSON data type in version 5.7.8. The data is stored in a binary format called BIN_JSON, which is more efficient than plain text because it allows direct access to nested attributes without loading the whole document into memory.

Key JSON Functions

JSON_EXTRACT()

: Extracts a specific field or path from a JSON document. JSON_SET(): Updates or inserts a value at a given path. JSON_ARRAY(): Creates a JSON array. JSON_OBJECT(): Creates a JSON object. JSON_CONTAINS(): Checks whether a key or value exists. JSON_ARRAYAGG() and JSON_OBJECTAGG(): Aggregate results into JSON in GROUP BY queries. JSON_TABLE() (MySQL 8.0): Dynamically maps JSON data to a relational table.

When inserting into a JSON column, MySQL validates that the value is well‑formed JSON and rejects invalid text.

JSON columns also support generated columns, which can index values extracted from the JSON document. See the related article “How to index JSON fields in MySQL” for details.

Before vs. After Using JSON Type

Without JSON Type

Prior to MySQL 5.7, JSON data had to be stored in CHAR, VARCHAR or TEXT. Applications retrieved the string and parsed it with a language‑specific library, e.g. Python’s json module:

import json

jsonStr = '{"name":"John","age":30,"city":"New York"}'
y = json.loads(jsonStr)
print(y['age'])  # 30

This approach incurs extra disk I/O and network bandwidth because the whole document must be transferred and parsed.

With JSON Type

MySQL can query JSON fields directly, eliminating the need to transfer the full document. Example table creation and basic queries:

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);

INSERT INTO example (data) VALUES ('{"name":"John","age":30,"city":"New York"}');

SELECT JSON_EXTRACT(data, '$.age') AS age FROM example;
-- or using the shortcut operator
SELECT data->'$.age' AS age FROM example;

Both queries return only the requested value, reducing I/O and bandwidth.

CRUD Operations on JSON Columns

Insert

MySQL follows the RFC 7159 JSON standard, allowing insertion of objects or arrays:

INSERT INTO example (data) VALUES ('{"name":"John","age":30,"city":"New York"}');
INSERT INTO example (data) VALUES ('["John",30,"New York"]');

Attempting to insert non‑JSON text results in an error:

ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 'example.data'.

Duplicate keys are handled differently across versions: before 8.0.3 the first key wins; from 8.0.3 the last key wins.

Constructing JSON

Use JSON_ARRAY() and JSON_OBJECT() to build values:

SELECT JSON_ARRAY(1, "John", 30, "New York");
SELECT JSON_OBJECT('name','John','age','30','city','New York');

Read

Core extraction is performed with JSON_EXTRACT() (or the -> operator). Examples:

SELECT JSON_EXTRACT(data, '$.name') AS name FROM example;
SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) AS name FROM example;
SELECT data->>'$.name' AS name_raw FROM example;

Path syntax rules: $ denotes the root. . accesses child members (e.g., $.name). [index] accesses array elements (e.g., $[0]). [M to N] selects a range; [*] selects all elements.

Mixed object/array queries can produce unexpected results, so avoid mixing types in a single column.

Update

JSON_SET()

inserts or updates a value, while JSON_REPLACE() only updates existing paths:

SELECT JSON_SET(data, '$.age', 35) AS new_data FROM example;
SELECT JSON_REPLACE(data, '$.age', 35) AS new_data FROM example;

Delete

JSON_REMOVE()

deletes one or more paths:

SELECT JSON_REMOVE(data, '$.gender') AS new_data FROM example;
SELECT JSON_REMOVE(data, '$.age', '$.city') AS new_data FROM example;

Conclusion

MySQL’s native JSON type provides a compact binary representation, built‑in functions for manipulation, and the ability to index JSON values, delivering significant performance and developer‑experience improvements over traditional string‑based storage.