MySQL JSON Column Type

Also Known As

TL;DR

Explanation

JSON columns store validated JSON and support path-based extraction with JSON_EXTRACT() or the -> shorthand operator. Generated columns can index specific JSON paths: ALTER TABLE ADD COLUMN email VARCHAR(255) GENERATED ALWAYS AS (data->>'$.email'). JSON is not a replacement for normalised columns — it's for genuinely variable-structure data. JSON queries cannot use standard B-tree indexes without generated column tricks. MySQL 8 added JSON_TABLE() to pivot JSON arrays into relational rows.

Common Misconception

Why It Matters

Common Mistakes

• Storing normalised data (user IDs, fixed attributes) as JSON — losing referential integrity and index efficiency.
• Filtering by JSON path without a generated column index — causes full table scans.
• Not validating JSON structure in PHP before inserting — MySQL validates only that it's valid JSON, not that required fields exist.

Avoid When

• Do not store normalised relational data (user IDs, fixed attributes) in JSON — you lose indexes, foreign keys, and type safety.
• Do not use JSON as a substitute for proper schema design — it should be the exception, not the default.

When To Use

• Use JSON columns for genuinely variable-structure data like product attributes, user preferences, or event payloads.
• Add a generated column index on any JSON path you regularly filter or sort by.

Code Examples

-- Storing normalised data as JSON — loses FK, indexes, and type safety
INSERT INTO orders SET data = '{"user_id": 42, "total": 99.99, "items": [...]}';

-- JSON column for variable attributes
CREATE TABLE products (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    attributes JSON
) ENGINE=InnoDB;

-- Generated column index for common JSON path
ALTER TABLE products
    ADD COLUMN brand VARCHAR(100) GENERATED ALWAYS AS (attributes->>'$.brand') STORED,
    ADD INDEX idx_brand (brand);

-- Query JSON path
SELECT name, attributes->>'$.colour' FROM products WHERE brand = 'Acme';

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/json.html

Tags

Related Terms