Explain the difference between -> and ->> operators in MySQL JSON. When would you use each?

-> and ->> operators extract JSON values with different return types. -> operator: Returns JSON value (with quotes for strings). Example: preferences->'$.theme' returns "dark" (with quotes). Use when: (1) Chaining extractions (preferences->'$.notifications'->'$.email'), (2) Need JSON for further processing. ->> operator: Returns text value (without quotes). Example: preferences->>'$.theme' returns dark (without quotes). Use when: (1) Final extraction (no chaining), (2) Comparing with text (WHERE preferences->>'$.theme' = 'dark'), (3) Displaying to user. Equivalent: preferences->> '$.theme' = JSON_UNQUOTE(JSON_EXTRACT(preferences, '$.theme')). Performance: ->> is faster than JSON_EXTRACT + JSON_UNQUOTE. Real-world: Slack uses ->> for final extractions (theme, language). Uses -> for chaining (preferences->'$.notifications'->'$.email'). Lesson: Use ->> for final text extraction, -> for chaining.

Why are generated columns essential for JSON indexing? How do they work?

Generated columns enable JSON indexing because JSON columns cannot be indexed directly. How they work: (1) Generated column: Computed from JSON path (theme AS (preferences->>'$.theme') STORED), (2) STORED: Pre-computed and stored (not computed on query), (3) Index: Created on generated column (CREATE INDEX ON users(theme)), (4) Automatic updates: When JSON changes, generated column updates automatically. Why essential: Without generated columns: Cannot index JSON (sequential scan, 1000ms for 100K rows). With generated columns: Index on generated column (index scan, 10ms, 100x faster). Example: Query users by theme. Without: WHERE preferences->>'$.theme' = 'dark' (sequential scan). With: ADD COLUMN theme AS (preferences->>'$.theme') STORED, CREATE INDEX idx_theme ON users(theme), WHERE theme = 'dark' (index scan, 100x faster). STORED vs VIRTUAL: STORED (pre-computed, can index), VIRTUAL (computed on query, cannot index). Real-world: Shopify uses generated columns for product brand/category (100x faster). Lesson: Generated columns are essential for JSON indexing.

When would you use JSON columns vs normalized tables? What are the trade-offs?

JSON vs normalized tables decision depends on schema flexibility needs. Use JSON when: (1) Flexible schema (attributes vary by category), (2) Rapid development (no ALTER TABLE), (3) Semi-structured data (API responses, logs), (4) Read-heavy workloads (< 1M rows). Use normalized tables when: (1) Fixed schema (well-defined structure), (2) Complex queries (JOINs, aggregations), (3) Write-heavy workloads, (4) Large scale (> 1M rows), (5) Referential integrity needed. Trade-offs: JSON: Flexible (no ALTER TABLE), but limited queries (no JOIN on nested data), requires generated columns for indexing, slower than PostgreSQL JSONB (text vs binary). Normalized: Fixed schema (requires ALTER TABLE), but flexible queries (JOIN, aggregate), direct indexing, better performance at scale. Performance: JSON: Fast reads (no JOIN), slow writes (update entire JSON). Normalized: Slower reads (JOIN), fast writes (update single column). Real-world: Shopify uses JSON for product attributes (flexible schema). Uses normalized tables for orders (fixed schema, complex queries). Lesson: JSON for flexible schemas (< 1M rows), normalized for fixed schemas or large scale.

Design a user preferences system using JSON. Include generated columns, indexes, and queries for: (1) Find users with dark theme, (2) Update user language, (3) Add new preference.

-- Design user preferences with JSON

CREATE TABLE users (

user_id INT AUTO_INCREMENT PRIMARY KEY,

email VARCHAR(255) NOT NULL UNIQUE,

preferences JSON,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

-- Add generated columns for frequently queried fields

ALTER TABLE users

ADD COLUMN theme VARCHAR(20) AS (preferences->>'$.theme') STORED,

ADD COLUMN language VARCHAR(10) AS (preferences->>'$.language') STORED;

-- Create indexes

CREATE INDEX idx_users_theme ON users(theme);

CREATE INDEX idx_users_language ON users(language);

-- Insert sample users

INSERT INTO users (email, preferences) VALUES

(

'john@example.com',

JSON_OBJECT(

'theme', 'dark',

'language', 'en',

'notifications', JSON_OBJECT('email', true, 'sms', false)

)

),

(

'jane@example.com',

JSON_OBJECT(

'theme', 'light',

'language', 'es',

'notifications', JSON_OBJECT('email', false, 'sms', true)

)

);

-- (1) Find users with dark theme (uses index)