Using MySQL’s JSON Column Type Effectively

MySQL 5.7 added a native JSON column type, letting you store semi-structured data without a full schema migration for every new field — useful, though not a replacement for proper relational modeling of core data.

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    attributes JSON
);

INSERT INTO products (name, attributes)
VALUES ('T-Shirt', '{"color": "blue", "sizes": ["S", "M", "L"]}');

-- Query into the JSON directly
SELECT name FROM products
WHERE JSON_EXTRACT(attributes, '$.color') = 'blue';

-- Shorthand syntax
SELECT name FROM products WHERE attributes->>'$.color' = 'blue';

-- Index a specific JSON path for performance (via a generated column)
ALTER TABLE products ADD COLUMN color VARCHAR(50)
  GENERATED ALWAYS AS (attributes->>'$.color') STORED,
  ADD INDEX idx_color (color);

JSON columns are great for genuinely variable, sparse attributes (like product specs that differ wildly by category) — but core relational data (foreign keys, anything you filter or join on often) still belongs in real columns with real indexes.

Using MySQL’s JSON Column Type Effectively

Recommended

Categories