An online store sells kurtas with sizes, shoes with a waterproof flag and bottles with a capacity in ml. Giving every possible attribute its own column leaves most cells NULL. The JSON column type, available since MySQL 5.7.8, lets each row carry its own set of attributes. MySQL still validates the JSON, lets you query inside it and can index parts of it. This tutorial covers storing, reading, filtering, updating and indexing JSON, and when not to use it.
Store JSON
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(40),
attrs JSON
);
INSERT INTO products VALUES
(1,'Cotton kurta', '{"brand":"Weavers","sizes":["S","M","L"],"price":799,"care":{"wash":"cold"}}'),
(2,'Running shoes','{"brand":"Stride","sizes":["8","9","10"],"price":2499,"waterproof":false}'),
(3,'Steel bottle', '{"brand":"Hydra","capacity_ml":750,"price":549}');
Invalid JSON is rejected, which a plain TEXT column would never do:
INSERT INTO products VALUES (4,'Broken','{"brand": "X", }');
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 15 in value for column 'products.attrs'.
Read values: -> versus ->>
SELECT name,
attrs->'$.brand' AS brand_json,
attrs->>'$.brand' AS brand_text,
attrs->>'$.care.wash' AS wash
FROM products;
| name | brand_json | brand_text | wash |
|---|---|---|---|
| Cotton kurta | "Weavers" | Weavers | cold |
| Running shoes | "Stride" | Stride | NULL |
| Steel bottle | "Hydra" | Hydra | NULL |
-> is short for JSON_EXTRACT() and returns a JSON value, with its quotes. ->> also unquotes it (JSON_UNQUOTE(JSON_EXTRACT(...))), which is what you want for display and for comparing with plain strings. The path starts with $, and dots step into nested objects. A path that does not exist returns NULL rather than an error.
Filter on JSON contents
SELECT name, attrs->>'$.price' AS price
FROM products
WHERE CAST(attrs->>'$.price' AS UNSIGNED) < 1000
ORDER BY CAST(attrs->>'$.price' AS UNSIGNED);
| name | price |
|---|---|
| Steel bottle | 549 |
| Cotton kurta | 799 |
Cast before comparing numbers. Otherwise you compare text, and '2499' sorts before '549'. To test whether an array holds a value, either form works:
SELECT name FROM products WHERE JSON_CONTAINS(attrs->'$.sizes', '"M"');
SELECT name FROM products WHERE 'M' MEMBER OF (attrs->'$.sizes'); -- 8.0.17+
| name |
|---|
| Cotton kurta |
Note the inner quotes in '"M"': JSON_CONTAINS expects a JSON value, and a JSON string needs quotes.
Inspect structure
SELECT name,
JSON_LENGTH(attrs->'$.sizes') AS n_sizes,
JSON_KEYS(attrs) AS keys_list,
JSON_CONTAINS_PATH(attrs, 'one', '$.waterproof') AS has_wp
FROM products;
| name | n_sizes | keys_list | has_wp |
|---|---|---|---|
| Cotton kurta | 3 | ["care", "brand", "price", "sizes"] | 0 |
| Running shoes | 3 | ["brand", "price", "sizes", "waterproof"] | 1 |
| Steel bottle | NULL | ["brand", "price", "capacity_ml"] | 0 |
The key order is not the order you inserted. MySQL normalises JSON when storing it (keys sorted by length, then alphabetically), so never rely on key order.
Turn a JSON array into rows with JSON_TABLE
SELECT p.name, s.size
FROM products p,
JSON_TABLE(p.attrs, '$.sizes[*]' COLUMNS (size VARCHAR(5) PATH '$')) AS s
ORDER BY p.id, s.size;
| name | size |
|---|---|
| Cotton kurta | L |
| Cotton kurta | M |
| Cotton kurta | S |
| Running shoes | 10 |
| Running shoes | 8 |
| Running shoes | 9 |
JSON_TABLE (MySQL 8.0) turns a JSON array into relational rows that you can join, group or count. The bottle has no sizes, so it produces no rows. The sizes sort as text ('10' before '8') because the column was declared VARCHAR.
Update part of a document
UPDATE products SET attrs = JSON_SET(attrs, '$.price', 749, '$.on_sale', true) WHERE id = 1;
UPDATE products SET attrs = JSON_REMOVE(attrs, '$.care') WHERE id = 1;
UPDATE products SET attrs = JSON_ARRAY_APPEND(attrs, '$.sizes', 'XL') WHERE id = 1;
SELECT attrs FROM products WHERE id = 1;
| attrs |
|---|
| {"brand": "Weavers", "price": 749, "sizes": ["S", "M", "L", "XL"], "on_sale": true} |
JSON_SET replaces existing keys and adds missing ones. JSON_INSERT only adds, and JSON_REPLACE only replaces. Because you do not rewrite the whole document from the application, two programs editing different keys are less likely to overwrite each other's changes.
Index a JSON field
A JSON column cannot be indexed directly. Expose the field as a generated column and index that:
ALTER TABLE products
ADD COLUMN brand VARCHAR(30) GENERATED ALWAYS AS (attrs->>'$.brand') VIRTUAL,
ADD INDEX idx_brand (brand);
EXPLAIN SELECT name FROM products WHERE brand = 'Stride';
| type | possible_keys | key | ref | rows |
|---|---|---|---|---|
| ref | idx_brand | idx_brand | const | 1 |
The plan now uses idx_brand. MySQL 8.0.17 also added multi-valued indexes for arrays (INDEX ((CAST(attrs->'$.sizes' AS CHAR(5) ARRAY)))), which speed up MEMBER OF and JSON_CONTAINS. For more on how indexes work, see MySQL indexes explained.
Build JSON from rows
SELECT JSON_OBJECT('id', id, 'name', name) AS obj FROM products WHERE id = 2;
SELECT JSON_ARRAYAGG(name) AS all_names FROM products;
| obj |
|---|
| {"id": 2, "name": "Running shoes"} |
| all_names |
|---|
| ["Cotton kurta", "Running shoes", "Steel bottle"] |
This is handy when an API wants JSON straight from the database.
When JSON is the wrong choice
- Fields every row has (price, brand) and that you filter, join or total on belong in normal columns, with proper types and constraints.
- Relationships, such as a list of order IDs inside a customer document, belong in a separate table with a foreign key. See normalization.
- Mostly document-shaped data may fit a document database better. MySQL vs MongoDB compares the two approaches.
A common middle ground is regular columns for the core fields plus one JSON column for optional, varying attributes, as in the products table above. For choosing the other column types, see the MySQL data types guide.