Why JSON in MySQL?
The conventional wisdom used to be: use MySQL for structured data, use MongoDB for JSON. That was reasonable in 2012. Today, MySQL's JSON support is so mature that storing metadata, configuration, and loosely-structured data in JSON columns alongside your relational foreign keys is entirely production-appropriate.
Storing and Retrieving JSON
-- Insert JSON
INSERT INTO products (title, metadata)
VALUES ('MacBook Pro', '{"ram": 16, "storage": 512, "colors": ["silver", "space-gray"]}');
-- Extract with path notation
SELECT title, metadata->>'$.ram' AS ram_gb FROM products;
-- Using JSON_EXTRACT
SELECT title, JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.colors[0]')) AS primary_color
FROM products;Modifying JSON In-Place
-- Update a single key without replacing the whole object
UPDATE products
SET metadata = JSON_SET(metadata, '$.ram', 32, '$.storage', 1024)
WHERE id = 1;
-- Append to a JSON array
UPDATE products
SET metadata = JSON_ARRAY_APPEND(metadata, '$.colors', 'midnight')
WHERE id = 1;
-- Remove a key
UPDATE products
SET metadata = JSON_REMOVE(metadata, '$.legacy_field')
WHERE title LIKE '%MacBook%';Indexing JSON with Virtual Columns
You cannot index a JSON column directly, but you can create a virtual column that extracts a specific JSON path and then index that. This gives you full index performance on JSON attributes you query frequently:
ALTER TABLE products
ADD COLUMN ram_gb INT GENERATED ALWAYS AS (metadata->>'$.ram') VIRTUAL,
ADD INDEX idx_ram_gb (ram_gb);
SELECT * FROM products WHERE ram_gb >= 16; -- Uses the indexSearching Within JSON Arrays
-- Does the colors array contain 'silver'?
SELECT * FROM products
WHERE JSON_CONTAINS(metadata->'$.colors', '"silver"');
-- Find the path to a value
SELECT JSON_SEARCH(metadata, 'one', 'silver') AS path FROM products;When Not to Use JSON Columns
JSON columns are excellent for metadata and attributes that vary by row. They are a bad fit for data you need to join on, filter heavily, or aggregate. If you find yourself writing complex JSON_EXTRACT expressions in WHERE clauses constantly, that data probably belongs in its own normalized table. Use JSON to add flexibility at the edges of your schema, not as a substitute for proper relational design.