MySQL JSON Support

MySQL JSON Column Type

-- Create table with JSON column
CREATE TABLE products (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(255),
    metadata JSON
);

-- Insert JSON
INSERT INTO products (name, metadata) VALUES
('Widget', '{"color": "red", "weight": 1.5, "tags": ["sale", "new"]}');

-- Query JSON fields
SELECT name, metadata->>'$.color' AS color
FROM products;

SELECT name, JSON_EXTRACT(metadata, '$.weight') AS weight
FROM products;

-- Filter on JSON value
SELECT * FROM products
WHERE metadata->>'$.color' = 'red';

-- JSON functions
SELECT JSON_KEYS(metadata) FROM products;
SELECT JSON_ARRAY_LENGTH(metadata->'$.tags') FROM products;

-- Index JSON field
ALTER TABLE products
ADD COLUMN color VARCHAR(50) AS (metadata->>'$.color') STORED;
CREATE INDEX idx_color ON products(color);