Name: EVE AI Core
Author: Jamaurice Holt

MySQL JSON Data Type Usage

MySQL's native JSON data type enables efficient storage and querying of semi-structured data within your relational database. This powerful feature bridges the gap between traditional SQL and NoSQL document stores, allowing flexible schema design while maintaining ACID compliance.

🎯 Why Use JSON in MySQL?

Use Case	Description
Flexible Attributes	Store varying product attributes without schema changes
API Payloads	Store raw request/response data for debugging
User Preferences	Highly variable user settings and configurations
Event Data	Analytics events with varying properties
Audit Logs	Capture arbitrary state changes

💡 Key Insight: Use JSON for truly variable data that doesn't fit a fixed schema. For structured data that's frequently queried, traditional columns remain more efficient.

🔧 Creating JSON Columns

Basic Syntax

-- Create table with JSON column
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    attributes JSON,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB;

-- Insert JSON data
INSERT INTO products (name, price, attributes, metadata) VALUES
(
    'Gaming Laptop',
    1299.99,
    '{
        "brand": "TechBrand",
        "processor": "Intel i7",
        "ram_gb": 16,
        "storage": {"type": "SSD", "capacity_gb": 512},
        "display": {"size_inches": 15.6, "resolution": "1920x1080"},
        "features": ["backlit keyboard", "RGB lighting", "touchscreen"]
    }',
    '{"sku": "TECH-001", "warehouse": "A3", "weight_kg": 2.5}'
),
(
    'Wireless Mouse',
    49.99,
    '{
        "brand": "MouseCo",
        "dpi": 16000,
        "buttons": 6,
        "wireless": true,
        "battery_type": "rechargeable",
        "features": ["ergonomic", "programmable buttons"]
    }',
    '{"sku": "MOUSE-002", "warehouse": "B1", "weight_kg": 0.1}'
);

JSON Validation

-- MySQL validates JSON on insert
INSERT INTO products (name, price, attributes) VALUES
('Invalid', 99.99, '{invalid json}');
-- ERROR 3140: Invalid JSON text

-- Use JSON_VALID() to check before insert
SELECT JSON_VALID('{"valid": true}');  -- Returns 1
SELECT JSON_VALID('{invalid}');         -- Returns 0

📦 JSON Functions Reference

Creating JSON Values

-- JSON_OBJECT: Create object from key-value pairs
SELECT JSON_OBJECT(
    'name', 'John',
    'age', 30,
    'active', TRUE
);
-- {"age": 30, "name": "John", "active": true}

-- JSON_ARRAY: Create array
SELECT JSON_ARRAY('apple', 'banana', 'cherry');
-- ["apple", "banana", "cherry"]

-- JSON_QUOTE: Quote a string as JSON
SELECT JSON_QUOTE('Hello "World"');
-- "Hello \"World\""

-- Combining
SELECT JSON_OBJECT(
    'user', 'admin',
    'permissions', JSON_ARRAY('read', 'write', 'delete'),
    'settings', JSON_OBJECT('theme', 'dark', 'notifications', TRUE)
);

Extracting JSON Values

-- JSON_EXTRACT: Extract value at path
SELECT JSON_EXTRACT(attributes, '$.brand') FROM products;
-- "TechBrand" (with quotes)

-- Shorthand operator ->
SELECT attributes->'$.brand' FROM products;
-- "TechBrand" (with quotes)

-- Unquoted shorthand ->>
SELECT attributes->>'$.brand' FROM products;
-- TechBrand (without quotes)

-- Extract nested values
SELECT attributes->>'$.storage.type' FROM products;
-- SSD

-- Extract array element
SELECT attributes->>'$.features[0]' FROM products;
-- backlit keyboard

-- Extract multiple paths
SELECT JSON_EXTRACT(attributes, '$.brand', '$.processor') FROM products;
-- ["TechBrand", "Intel i7"]

Searching JSON

-- JSON_CONTAINS: Check if document contains value
SELECT * FROM products
WHERE JSON_CONTAINS(attributes, '"touchscreen"', '$.features');

-- JSON_CONTAINS_PATH: Check if path exists
SELECT * FROM products
WHERE JSON_CONTAINS_PATH(attributes, 'one', '$.wireless');

-- Check multiple paths (all must exist)
SELECT * FROM products
WHERE JSON_CONTAINS_PATH(attributes, 'all', '$.brand', '$.processor');

-- Check multiple paths (any must exist)
SELECT * FROM products
WHERE JSON_CONTAINS_PATH(attributes, 'one', '$.wireless', '$.wired');

-- JSON_SEARCH: Find path to value
SELECT JSON_SEARCH(attributes, 'one', 'TechBrand') FROM products;
-- "$.brand"

-- Search with wildcards
SELECT JSON_SEARCH(attributes, 'all', '%keyboard%') FROM products;

Modifying JSON

-- JSON_SET: Insert or update values
UPDATE products
SET attributes = JSON_SET(
    attributes,
    '$.price_updated', NOW(),
    '$.ram_gb', 32  -- Update existing
)
WHERE id = 1;

-- JSON_INSERT: Only insert if path doesn't exist
UPDATE products
SET attributes = JSON_INSERT(
    attributes,
    '$.brand', 'NewBrand',     -- Won't change (exists)
    '$.warranty_years', 2       -- Will be added
)
WHERE id = 1;

-- JSON_REPLACE: Only update if path exists
UPDATE products
SET attributes = JSON_REPLACE(
    attributes,
    '$.brand', 'UpdatedBrand',  -- Will change
    '$.new_field', 'value'      -- Won't be added
)
WHERE id = 1;

-- JSON_REMOVE: Remove keys
UPDATE products
SET attributes = JSON_REMOVE(
    attributes,
    '$.temporary_field',
    '$.features[0]'  -- Remove first feature
)
WHERE id = 1;

-- JSON_ARRAY_APPEND: Add to array
UPDATE products
SET attributes = JSON_ARRAY_APPEND(
    attributes,
    '$.features', 'new feature'
)
WHERE id = 1;

-- JSON_ARRAY_INSERT: Insert at position
UPDATE products
SET attributes = JSON_ARRAY_INSERT(
    attributes,
    '$.features[0]', 'first feature'
)
WHERE id = 1;

Aggregating JSON

-- JSON_ARRAYAGG: Aggregate rows into JSON array
SELECT JSON_ARRAYAGG(name) AS product_names
FROM products;
-- ["Gaming Laptop", "Wireless Mouse"]

-- JSON_OBJECTAGG: Aggregate into JSON object
SELECT JSON_OBJECTAGG(name, price) AS price_list
FROM products;
-- {"Gaming Laptop": 1299.99, "Wireless Mouse": 49.99}

-- Complex aggregation
SELECT
    category,
    JSON_ARRAYAGG(
        JSON_OBJECT('name', name, 'price', price)
    ) AS products
FROM products
GROUP BY category;

🚀 Indexing JSON Data

Generated Columns for Indexing

-- Create table with generated columns for indexable JSON paths
CREATE TABLE products_indexed (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    attributes JSON,

    -- Generated columns from JSON
    brand VARCHAR(100) GENERATED ALWAYS AS (attributes->>'$.brand') STORED,
    ram_gb INT GENERATED ALWAYS AS (attributes->>'$.ram_gb') STORED,
    is_wireless BOOLEAN GENERATED ALWAYS AS (
        CAST(attributes->>'$.wireless' AS UNSIGNED)
    ) STORED,

    -- Indexes on generated columns
    INDEX idx_brand (brand),
    INDEX idx_ram (ram_gb),
    INDEX idx_wireless (is_wireless)
) ENGINE=InnoDB;

-- Now queries can use indexes
EXPLAIN SELECT * FROM products_indexed WHERE brand = 'TechBrand';
-- Uses idx_brand index

Multi-Valued Indexes (MySQL 8.0.17+)

-- Create multi-valued index on JSON array
CREATE TABLE products_with_tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    tags JSON,

    -- Multi-valued index on array elements
    INDEX idx_tags ((CAST(tags AS CHAR(50) ARRAY)))
) ENGINE=InnoDB;

INSERT INTO products_with_tags (name, tags) VALUES
('Laptop', '["electronics", "computers", "portable"]'),
('Phone', '["electronics", "mobile", "communication"]');

-- Query uses multi-valued index
SELECT * FROM products_with_tags
WHERE 'electronics' MEMBER OF (tags);

-- Also supports JSON_CONTAINS with index
SELECT * FROM products_with_tags
WHERE JSON_CONTAINS(tags, '"mobile"');

Functional Indexes

-- Create functional index directly on JSON expression
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_data JSON,

    -- Functional index on JSON path
    INDEX idx_event_type ((CAST(event_data->>'$.type' AS CHAR(50))))
) ENGINE=InnoDB;

-- Query uses functional index
SELECT * FROM events WHERE event_data->>'$.type' = 'click';

📊 Query Patterns and Examples

Filtering on JSON Values

-- Filter by JSON path value
SELECT * FROM products
WHERE attributes->>'$.brand' = 'TechBrand';

-- Filter with comparison
SELECT * FROM products
WHERE CAST(attributes->>'$.ram_gb' AS UNSIGNED) >= 16;

-- Filter on nested value
SELECT * FROM products
WHERE attributes->>'$.storage.type' = 'SSD';

-- Filter on array contains
SELECT * FROM products
WHERE JSON_CONTAINS(attributes, '"touchscreen"', '$.features');

-- Filter with JSON_OVERLAPS (array intersection)
SELECT * FROM products
WHERE JSON_OVERLAPS(
    attributes->'$.features',
    '["touchscreen", "RGB lighting"]'
);

Sorting by JSON Values

-- Sort by JSON value
SELECT name, attributes->>'$.brand' AS brand
FROM products
ORDER BY attributes->>'$.brand';

-- Sort by numeric JSON value
SELECT name, CAST(attributes->>'$.ram_gb' AS UNSIGNED) AS ram
FROM products
ORDER BY CAST(attributes->>'$.ram_gb' AS UNSIGNED) DESC;

Joining with JSON

-- Extract value for join
SELECT p.name, c.category_name
FROM products p
JOIN categories c
    ON c.id = CAST(p.attributes->>'$.category_id' AS UNSIGNED);

-- Use JSON in subquery
SELECT *
FROM products p
WHERE EXISTS (
    SELECT 1 FROM allowed_brands ab
    WHERE ab.brand_name = p.attributes->>'$.brand'
);

Transforming JSON

-- Flatten JSON array to rows
SELECT
    p.id,
    p.name,
    feature.value AS feature
FROM products p,
     JSON_TABLE(
         p.attributes,
         '$.features[*]' COLUMNS (
             value VARCHAR(100) PATH '$'
         )
     ) AS feature;

-- Complex JSON_TABLE extraction
SELECT
    p.id,
    p.name,
    specs.*
FROM products p,
     JSON_TABLE(
         p.attributes,
         '$' COLUMNS (
             brand VARCHAR(100) PATH '$.brand',
             processor VARCHAR(100) PATH '$.processor',
             ram_gb INT PATH '$.ram_gb',
             storage_type VARCHAR(50) PATH '$.storage.type',
             storage_gb INT PATH '$.storage.capacity_gb',
             NESTED PATH '$.features[*]' COLUMNS (
                 feature VARCHAR(100) PATH '$'
             )
         )
     ) AS specs;

⚠️ Performance Considerations

When JSON is Efficient

-- ✅ Good: Store and retrieve entire JSON document
SELECT attributes FROM products WHERE id = 1;

-- ✅ Good: Filter on indexed generated column
SELECT * FROM products_indexed WHERE brand = 'TechBrand';

-- ✅ Good: Occasional flexible queries
SELECT * FROM products WHERE attributes->>'$.color' = 'black';

When JSON is Slow

-- ❌ Avoid: Aggregate operations on JSON paths
SELECT AVG(CAST(attributes->>'$.ram_gb' AS UNSIGNED))
FROM products;
-- Full table scan required

-- ❌ Avoid: Sorting by non-indexed JSON path
SELECT * FROM products
ORDER BY attributes->>'$.brand';
-- No index can help

-- ❌ Avoid: Join on JSON path without index
SELECT * FROM products p
JOIN brands b ON b.name = p.attributes->>'$.brand';
-- Full scan of products table

Optimization Strategies

-- Strategy 1: Extract frequently queried fields
ALTER TABLE products
    ADD COLUMN brand VARCHAR(100) GENERATED ALWAYS AS (
        attributes->>'$.brand'
    ) STORED,
    ADD INDEX idx_brand (brand);

-- Strategy 2: Partial JSON storage
CREATE TABLE products_optimized (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    brand VARCHAR(100) NOT NULL,  -- Extracted for indexing
    category_id INT NOT NULL,      -- Extracted for joins
    price DECIMAL(10,2) NOT NULL,
    extra_attributes JSON,         -- Variable/less-queried data
    INDEX idx_brand (brand),
    INDEX idx_category (category_id)
);

-- Strategy 3: Use appropriate column types
-- Instead of: {"active": true, "count": 42}
-- Use: active BOOLEAN, count INT
-- Reserve JSON for truly variable data

📋 JSON Schema Validation (MySQL 8.0.17+)

-- Create table with JSON schema validation
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile JSON,

    -- Validate JSON structure
    CONSTRAINT chk_profile CHECK (
        JSON_SCHEMA_VALID('{
            "type": "object",
            "required": ["name", "email"],
            "properties": {
                "name": {"type": "string", "minLength": 1},
                "email": {"type": "string", "format": "email"},
                "age": {"type": "integer", "minimum": 0, "maximum": 150},
                "preferences": {
                    "type": "object",
                    "properties": {
                        "theme": {"enum": ["light", "dark"]},
                        "notifications": {"type": "boolean"}
                    }
                }
            }
        }', profile)
    )
);

-- Valid insert
INSERT INTO user_profiles (profile) VALUES ('{
    "name": "John Doe",
    "email": "[email protected]",
    "age": 30,
    "preferences": {"theme": "dark", "notifications": true}
}');

-- Invalid insert (missing required field)
INSERT INTO user_profiles (profile) VALUES ('{
    "name": "Jane Doe"
}');
-- ERROR: Check constraint 'chk_profile' is violated

🔐 Security Best Practices

Preventing JSON Injection

-- ❌ Dangerous: String concatenation
SET @user_input = '"; DROP TABLE users; --';
SET @json = CONCAT('{"name": "', @user_input, '"}');

-- ✅ Safe: Use JSON_OBJECT
SET @user_input = '"; DROP TABLE users; --';
SELECT JSON_OBJECT('name', @user_input);
-- Properly escaped: {"name": "\"; DROP TABLE users; --"}

-- ✅ Safe: Use JSON_QUOTE for values
SET @safe_value = JSON_QUOTE(@user_input);

Access Control

-- Grant limited JSON function access
GRANT SELECT, INSERT, UPDATE ON database.products TO 'app_user'@'%';

-- User can only work with allowed JSON paths through views
CREATE VIEW products_public AS
SELECT
    id,
    name,
    price,
    JSON_OBJECT(
        'brand', attributes->>'$.brand',
        'features', attributes->'$.features'
    ) AS public_attributes
FROM products;

GRANT SELECT ON database.products_public TO 'public_user'@'%';

✅ Best Practices Checklist

Design

[ ] Use JSON for truly variable/schemaless data
[ ] Extract frequently queried fields to regular columns
[ ] Create generated columns + indexes for common queries
[ ] Validate JSON structure with constraints
[ ] Document expected JSON structure

Performance

[ ] Avoid full-table scans on JSON paths
[ ] Use multi-valued indexes for array queries
[ ] Monitor query performance with EXPLAIN
[ ] Consider partial JSON indexing
[ ] Keep JSON documents reasonably sized

Operations

[ ] Backup JSON data regularly
[ ] Test JSON migrations carefully
[ ] Monitor JSON column sizes
[ ] Use proper error handling for JSON operations

🎓 Conclusion

MySQL's JSON data type provides powerful capabilities for storing and querying semi-structured data within a relational database. By combining JSON flexibility with proper indexing strategies and generated columns, you can build applications that leverage the best of both SQL and NoSQL paradigms.

💡 Pro Tip: Start with traditional columns for well-known, frequently-queried fields. Use JSON for truly variable attributes that don't fit a fixed schema. As patterns emerge, extract common JSON paths into indexed generated columns.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in MySQL, SQL optimization, and enterprise database solutions.

Need help with MySQL JSON features? Contact us for expert database consulting.