Why JSON in a relational database
Tables suit data that has the same shape in every row. But sometimes each row has different extra properties: a laptop has RAM and a screen size, a book has page count and publisher, a mug has a colour. Adding a column for every possible property would create dozens of mostly empty columns. MySQL's JSON type stores a flexible document in one column. It validates the JSON and provides functions to read and change parts of it.
Create a table and insert JSON
CREATE DATABASE IF NOT EXISTS practice;
USE practice;
DROP TABLE IF EXISTS product_specs;
CREATE TABLE product_specs (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(60) NOT NULL,
specs JSON NOT NULL
);
INSERT INTO product_specs (name, specs) VALUES
('Laptop', '{"brand": "Acme", "ram_gb": 16, "ports": ["usb-c", "hdmi"], "screen": {"size": 14, "type": "IPS"}}'),
('Phone', '{"brand": "Zeta", "ram_gb": 8, "ports": ["usb-c"], "screen": {"size": 6.5, "type": "OLED"}}'),
('Mug', '{"colour": "blue", "capacity_ml": 350}');The value is a string that must be valid JSON. If it is not, MySQL refuses:
INSERT INTO product_specs (name, specs) VALUES ('Broken', '{brand: Acme}');ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'product_specs.specs'.You can also build JSON with functions: JSON_OBJECT('brand', 'Acme', 'ram_gb', 16) and JSON_ARRAY('a', 'b').
Reading values with paths
A JSON path starts with $. Use .key for object members and [n] for array elements (counting from 0).
SELECT name,
specs->'$.brand' AS brand_json,
specs->>'$.brand' AS brand_text,
specs->>'$.screen.size' AS screen_size,
specs->>'$.ports[0]' AS first_port
FROM product_specs;+--------+------------+------------+-------------+------------+
| name | brand_json | brand_text | screen_size | first_port |
+--------+------------+------------+-------------+------------+
| Laptop | "Acme" | Acme | 14 | usb-c |
| Phone | "Zeta" | Zeta | 6.5 | usb-c |
| Mug | NULL | NULL | NULL | NULL |
+--------+------------+------------+-------------+------------+-> returns a JSON value, so strings keep their double quotes. ->> removes the quotes and returns plain text, which is what you usually want to display or compare. A path that does not exist gives NULL, as for the mug above, which makes it easy to work with data whose shape varies. JSON_EXTRACT(specs, '$.brand') is the longer function form of ->.
Filtering on JSON
Compare extracted values in WHERE. Use ->> for strings and cast for numbers:
SELECT name FROM product_specs WHERE specs->>'$.brand' = 'Acme';+--------+
| name |
+--------+
| Laptop |
+--------+SELECT name, specs->>'$.ram_gb' AS ram FROM product_specs WHERE CAST(specs->>'$.ram_gb' AS UNSIGNED) >= 8 ORDER BY name;+--------+------+
| name | ram |
+--------+------+
| Laptop | 16 |
| Phone | 8 |
+--------+------+To test whether an array or object contains something, use JSON_CONTAINS or MEMBER OF:
SELECT name FROM product_specs WHERE 'hdmi' MEMBER OF (specs->'$.ports');+--------+
| name |
+--------+
| Laptop |
+--------+Changing JSON
Do not replace the whole document to change one value. Use the modifying functions:
| Function | Effect |
|---|---|
JSON_SET(doc, path, value) |
sets the value, creating it if missing |
JSON_INSERT(doc, path, value) |
adds only if the path does not exist |
JSON_REPLACE(doc, path, value) |
changes only if the path exists |
JSON_REMOVE(doc, path) |
deletes the path |
JSON_ARRAY_APPEND(doc, path, value) |
adds an element to an array |
UPDATE product_specs SET specs = JSON_SET(specs, '$.ram_gb', 32, '$.warranty_years', 2) WHERE name = 'Laptop';
UPDATE product_specs SET specs = JSON_ARRAY_APPEND(specs, '$.ports', 'headphone') WHERE name = 'Phone';
UPDATE product_specs SET specs = JSON_REMOVE(specs, '$.capacity_ml') WHERE name = 'Mug';
SELECT name, specs FROM product_specs ORDER BY id;+--------+-------------------------------------------------------------------------------------------------------------------------+
| name | specs |
+--------+-------------------------------------------------------------------------------------------------------------------------+
| Laptop | {"brand": "Acme", "ports": ["usb-c", "hdmi"], "ram_gb": 32, "screen": {"size": 14, "type": "IPS"}, "warranty_years": 2} |
| Phone | {"brand": "Zeta", "ports": ["usb-c", "headphone"], "ram_gb": 8, "screen": {"size": 6.5, "type": "OLED"}} |
| Mug | {"colour": "blue"} |
+--------+-------------------------------------------------------------------------------------------------------------------------+MySQL normalises the stored JSON: it reorders the keys (shorter names first, then alphabetically) and standardises the spacing, so do not rely on key order.
Building JSON from rows
JSON functions work the other way too, turning table rows into JSON for an API response:
SELECT JSON_OBJECT('id', id, 'name', name, 'price', price) AS product
FROM shop.products
WHERE category_id = 5;+-----------------------------------------------------------+
| product |
+-----------------------------------------------------------+
| {"id": 14, "name": "Building Blocks Set", "price": 49.99} |
| {"id": 15, "name": "Puzzle 1000 Pieces", "price": 19.99} |
+-----------------------------------------------------------+SELECT c.name AS category, JSON_ARRAYAGG(p.name) AS products
FROM shop.categories c
JOIN shop.products p ON p.category_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;+-------------+---------------------------------------------------------------------------------------+
| category | products |
+-------------+---------------------------------------------------------------------------------------+
| Electronics | ["Wireless Mouse", "Mechanical Keyboard", "USB-C Hub", "Noise-Cancelling Headphones"] |
| Books | ["SQL Basics Handbook", "Web Design Patterns", "Data Structures Illustrated"] |
| Home | ["Desk Lamp", "Ceramic Mug Set", "Standing Desk Mat"] |
| Sports | ["Yoga Mat", "Resistance Bands", "Water Bottle"] |
| Toys | ["Building Blocks Set", "Puzzle 1000 Pieces"] |
+-------------+---------------------------------------------------------------------------------------+Turning JSON into rows: JSON_TABLE
JSON_TABLE expands a JSON array into relational rows so you can join and aggregate it:
SELECT p.name, port.port_name
FROM product_specs p,
JSON_TABLE(p.specs, '$.ports[*]' COLUMNS (port_name VARCHAR(20) PATH '$')) AS port
ORDER BY p.name, port.port_name;+--------+-----------+
| name | port_name |
+--------+-----------+
| Laptop | hdmi |
| Laptop | usb-c |
| Phone | headphone |
| Phone | usb-c |
+--------+-----------+Indexing a JSON field
You cannot put an index on a JSON column itself. To search quickly by one field, create a generated column that extracts it, and index that:
ALTER TABLE product_specs
ADD COLUMN brand VARCHAR(40) GENERATED ALWAYS AS (specs->>'$.brand') STORED,
ADD INDEX idx_specs_brand (brand);
SELECT name, brand FROM product_specs WHERE brand = 'Zeta';+-------+-------+
| name | brand |
+-------+-------+
| Phone | Zeta |
+-------+-------+The generated column is maintained by MySQL, never written by you. (MySQL 8.0.13 and later also supports functional indexes, written as INDEX ((CAST(specs->>'$.brand' AS CHAR(40)))).)
When to use JSON, and when not
JSON is a good fit for:
- optional, varying attributes ("extras") of an entity,
- storing a response or configuration object as it arrived,
- settings and preferences per user.
It is a poor fit for the core fields you filter, join or constrain on. A JSON blob has no data types per field, no foreign keys, no NOT NULL, and takes more work to query. If every row has the same fields, use columns. A good hybrid is normal columns for the stable, important data and one JSON column for the rest.
Common mistakes
- Comparing
->output with a plain string, forgetting that it still has quotes. Use->>. - Putting everything in JSON and losing the benefits of the relational model.
- Invalid JSON (single quotes inside, missing quotes on keys) in inserts.
- Filtering on a JSON field of a large table without an index on a generated column.
- Updating the full document from the application instead of using
JSON_SET, which risks overwriting parallel changes.
Exercise
Add a fourth product Notebook with specs {"pages": 120, "tags": ["paper", "ruled"]}. Then write queries to list products that have a pages value above 100, to add the tag a5 to the notebook, and to list all tags with their product using JSON_TABLE.
Show one possible answer
INSERT INTO product_specs (name, specs) VALUES ('Notebook', '{"pages": 120, "tags": ["paper", "ruled"]}');
SELECT name FROM product_specs WHERE CAST(specs->>'$.pages' AS UNSIGNED) > 100;
UPDATE product_specs SET specs = JSON_ARRAY_APPEND(specs, '$.tags', 'a5') WHERE name = 'Notebook';
SELECT p.name, t.tag
FROM product_specs p, JSON_TABLE(p.specs, '$.tags[*]' COLUMNS (tag VARCHAR(20) PATH '$')) AS t
ORDER BY t.tag;+----------+
| name |
+----------+
| Notebook |
+----------+
+----------+-------+
| name | tag |
+----------+-------+
| Notebook | a5 |
| Notebook | paper |
| Notebook | ruled |
+----------+-------+Key points
- The JSON type stores a validated JSON document. Invalid JSON is rejected at insert time.
- column->'$.path' extracts a value as JSON and column->>'$.path' extracts it as plain text. JSON paths start at $.
- JSON_SET, JSON_INSERT, JSON_REPLACE and JSON_REMOVE modify a document, and JSON_ARRAYAGG and JSON_OBJECT build JSON from rows.
- You cannot index a JSON column directly. Index a generated column that extracts the field you search on. Use JSON for flexible extras, not for core relational data.