JSON Document Storage

MySQL JSON Document Storage

MySQL 5.7.8+ provides a native JSON data type with binary storage, schema validation, and rich path-based query functions — bringing document-store flexibility to relational MySQL.

Path Expressions

-- -> returns quoted JSON, ->> returns unquoted string
SELECT preferences -> '$.theme',    -- "dark"
       preferences ->> '$.theme'    -- dark
FROM user_profiles WHERE user_id = 1;

Key Functions

• JSON_SET / JSON_INSERT / JSON_REPLACE / JSON_REMOVE — modify
• JSON_CONTAINS / JSON_CONTAINS_PATH — existence checks
• JSON_OVERLAPS — array intersection check (8.0.17+)
• JSON_ARRAYAGG / JSON_OBJECTAGG — aggregate to JSON
• JSON_TABLE — expand JSON array to rows

JSON Indexing

-- Functional index (8.0.13+)
ALTER TABLE t ADD INDEX idx_theme ((preferences ->> '$.theme'));

-- Multi-valued index for arrays (8.0.17+)
ALTER TABLE t ADD INDEX idx_tags ((CAST(tags AS CHAR(50) ARRAY)));
SELECT * FROM t WHERE 'electronics' MEMBER OF (tags);  -- uses index

When to Use JSON

Use regular columns for core business fields that need WHERE/JOIN/index. Use JSON for flexible extension attributes, SKU properties, preference settings, and snapshots. Don't put everything in JSON — you lose all relational benefits.