Mastering MySQL JSON: Storage, Queries, and Best Practices

What is MySQL JSON?

Since MySQL 5.7.8, the database supports a native JSON data type that complies with RFC 7159. It handles four scalar types—strings, numbers, booleans, null—and two structured types—objects and arrays.

Storing JSON in a native column offers automatic validation, an optimized binary storage format, and direct element access without parsing the entire text.

JSON Storage Structure

MySQL converts a JSON string into a binary doc object stored on disk using the utf8mb4 character set (a superset of utf8 and ascii). The doc consists of a 1‑byte type field (16 possible types) and a value part that holds the actual data.

Why Use the JSON Data Type?

Strong validation: the column rejects malformed JSON.

Built‑in functions for manipulating JSON values.

Optimized storage that enables fast element retrieval.

Ability to modify specific keys directly via SQL.

Working with JSON Objects and Arrays

Object Operations

Object‑style query: field->'$.jsonProperty' Function query: json_extract(field, '$.jsonProperty') Get length:

JSON_LENGTH()

Array Operations

Array query: field->'$[0].property' Function query: JSON_CONTAINS(field, JSON_OBJECT('jsonProperty','value')) Get length:

JSON_LENGTH()

Creating a Table with JSON Columns

CREATE TABLE `tinywan_json` (
  id INT(11) NOT NULL AUTO_INCREMENT,
  tag JSON NOT NULL COMMENT '标签列表',
  catagory JSON NOT NULL COMMENT '分类列表',
  create_time INT(11) DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

Describe the table to see the JSON columns:

DESCRIBE tinywan_json;

Inserting JSON Data

Insert literal JSON strings:

INSERT INTO tinywan_json (catagory, tag) VALUES
  ('{"0":"厨卫","1":"童装","2":"休闲"}', '["Good","Fine","Bad"]');

Or generate JSON with built‑in functions:

INSERT INTO tinywan_json (catagory, tag) VALUES
  (JSON_OBJECT('name','John','age',23), JSON_ARRAY('Low','Middle','High'));

Querying JSON Values

Use the column->path syntax where $.path addresses object keys and $[index] addresses array elements.

SELECT tag, JSON_TYPE(tag), catagory, JSON_TYPE(catagory) FROM tinywan_json;

Filter rows by a key value:

SELECT tag FROM tinywan_json WHERE catagory->'$.name' = 'John';

Retrieve a specific array element and handle missing values (returns NULL):

SELECT tag->'$[0]', tag->'$[2024]', tag->'$.notexist' FROM tinywan_json WHERE catagory->'$.name' = 'John';

Remove surrounding quotes with JSON_UNQUOTE:

SELECT JSON_UNQUOTE(tag->'$[0]') FROM tinywan_json WHERE catagory->'$.name' = 'John';

Conditional Queries with CAST and JSON_CONTAINS

String literals must be cast to JSON for equality comparison:

SELECT * FROM tinywan_json WHERE catagory = CAST('{"age":23,"name":"John"}' AS JSON);

Both column->path and JSON_CONTAINS can be used in WHERE clauses, but JSON_CONTAINS requires the second argument to be a valid JSON value (strings must be quoted, integers must be passed as JSON numbers).

-- Correct usage
SELECT * FROM tinywan_json WHERE JSON_CONTAINS(catagory, '23', '$.age');

-- Incorrect usage (integer not quoted as JSON)
SELECT * FROM tinywan_json WHERE JSON_CONTAINS(catagory, 23, '$.age');

Key Takeaways

JSON columns need no predefined schema, making them ideal for flexible data.

Maximum size per JSON document is 4 GB; each key may be up to 64 KB.

Best suited for relatively static data; for frequently searched data, add virtual columns and indexes.