Master MySQL JSON: From Basics to Advanced Queries and Indexing

1. Introduction

MySQL 5.7.8 introduced the JSON data type, which stores JSON documents efficiently. Compared with storing JSON as VARCHAR, JSON offers automatic validation, optimized binary storage, and fast element access.

Advantages

Automatic validation of JSON documents; invalid documents cause errors.

Optimized storage format that converts JSON to an internal binary representation, allowing rapid reads without parsing the text each time.

JSON storage size is similar to LONGBLOB or LONGTEXT, and the size is limited by max_allowed_packet.

JSON columns cannot have non‑NULL default values.

Use Cases

Storing semi‑structured data to avoid costly table joins and improve query performance.

Flexibly adding or changing fields without altering the table schema.

Below is a practical demonstration of the JSON data type.

2. Practical Example

2.1 Create Table & Insert Data

<code>CREATE TABLE json_t1 (
  id int auto_increment PRIMARY KEY,
  data JSON
);
INSERT INTO json_t1 (data) VALUES ('{"id": 1, "name": "张三", "sex": 1, "age": 24, "address": [{"id":1,"addr":"新疆乌鲁木齐"},{"id":2,"addr":"四川成都"}], "profiles": {"hobby":"篮球","food":"馍馍"}}');
INSERT INTO json_t1 (data) VALUES ('{"id": 2, "name": "李四", "sex": 0, "age": 22, "address": [{"id":3,"addr":"北京"},{"id":4,"addr":"石家庄"}], "profiles": {"hobby":"足球","food":"胡辣汤"}}');
INSERT INTO json_t1 (data) VALUES ('{"id": 3, "name": "王五", "sex": 1, "age": 23, "address": [{"id":5,"addr":"重庆"},{"id":6,"addr":"西安"}], "profiles": {"hobby":"乒乓球","food":"肉夹馍"}}');
</code>

2.2 Basic Queries

Query all rows:

<code>SELECT * FROM json_t1;</code>

Conditional query using the -> operator:

<code>SELECT * FROM json_t1 WHERE data->'$.name' = '王五';</code>

Select specific attributes:

<code>SELECT
  data->'$.id'   AS id,
  data->'$.name' AS name,
  data->'$.profiles.hobby' AS hobby
FROM json_t1;</code>

Remove quotes with JSON_UNQUOTE:

<code>SELECT
  JSON_UNQUOTE(data->'$.id')   AS id,
  JSON_UNQUOTE(data->'$.name') AS name,
  JSON_UNQUOTE(data->'$.profiles.hobby') AS hobby
FROM json_t1;</code>

Nested Condition Queries

<code>SELECT * FROM json_t1 WHERE data->'$.profiles.food' = '馍馍';
SELECT * FROM json_t1 WHERE data->'$.address[0].addr' = '重庆';</code>

Sorting

<code>SELECT * FROM json_t1 ORDER BY JSON_EXTRACT(data, '$.age') DESC;
SELECT * FROM json_t1 ORDER BY JSON_EXTRACT(data, '$.profiles.food') DESC;</code>

2.3 Modify Data

Update a field with JSON_SET:

<code>UPDATE json_t1 SET data = JSON_SET(data, '$.age', 28) WHERE data->'$.id' = 1;</code>

Update multiple fields:

<code>UPDATE json_t1 SET data = JSON_SET(data, '$.age', 28, '$.profiles.food', '馍馍01') WHERE data->'$.id' = 1;</code>

Delete an attribute:

<code>UPDATE json_t1 SET data = JSON_REMOVE(data, '$.profiles.food');</code>

Replace a value (only if the attribute exists):

<code>UPDATE json_t1 SET data = JSON_REPLACE(data, '$.name', '王五01') WHERE data->'$.id' = 1;</code>

2.4 Common JSON Functions

JSON_TYPE('["a","b",1]') returns the type.

JSON_ARRAY('a', 1, NOW()) creates a JSON array.

JSON_OBJECT('key1',1,'key2','abc') creates a JSON object.

JSON_EXTRACT('{"id":14,"name":"Aztalan"}', '$.name') extracts a property.

2.5 Indexing JSON Columns

Generate a virtual column for the name field and create an index:

<code>CREATE TABLE json_t1 (
  id int auto_increment PRIMARY KEY,
  data JSON,
  name VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data,'$.name'))) STORED
);
CREATE INDEX idx_name ON json_t1(name);
</code>

After inserting data, queries on the name column use the index, improving performance.