MySQL JSON enables flexible schemas without ALTER TABLE. Real examples: (1) E-commerce: Shopify stores product attributes as JSON (different attributes per category). (2) User preferences: Slack stores user settings as JSON (theme, notifications, language). (3) API responses: Stripe stores webhook payloads as JSON (flexible structure). (4) Metadata: GitHub stores repository metadata as JSON (tags, labels, custom fields). (5) Logs: Datadog stores log entries as JSON (flexible fields). Trade-offs: JSON is flexible (no ALTER TABLE) but slower than PostgreSQL JSONB (text vs binary). Good for flexible schemas, use normalized tables for fixed schemas.

Shopify: JSON for Product Attributes

Shopify has millions of products with varying attributes per category. Challenge: Fixed schema requires ALTER TABLE for new attributes. Solution: Use JSON columns with generated columns for common fields.

Shopify uses JSON for product attributes: (1) attributes JSON column stores flexible attributes, (2) Generated columns for common fields (brand, color, size), (3) Indexes on generated columns for fast queries. Architecture: products table with attributes JSON, generated columns (brand, color), indexes. Benefits: No ALTER TABLE needed, fast queries with indexes (100x faster), different attributes per category.

CREATE TABLE products (

product_id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(200),

attributes JSON,

brand VARCHAR(50) AS (attributes->>'$.brand') STORED

);

CREATE INDEX idx_brand ON products(brand);

SELECT * FROM products WHERE brand = 'Apple';

Millions of products, varying attributes

JSON: No ALTER TABLE needed

Generated columns: 100x faster queries

Flexible schema per category

Lesson: JSON for flexible attributes

MySQL

Slack: JSON for User Preferences

Slack stores user preferences (theme, language, notifications) with frequent changes. Challenge: Adding new preferences requires ALTER TABLE. Solution: Use JSON columns for flexible preferences.

Slack uses JSON for user preferences: (1) preferences JSON column stores all preferences, (2) Generated columns for frequently queried fields (theme, language), (3) Indexes for fast queries. Architecture: users table with preferences JSON, generated columns (theme, language), indexes. Benefits: No ALTER TABLE for new preferences, fast queries with indexes, flexible schema.

CREATE TABLE users (

user_id INT AUTO_INCREMENT PRIMARY KEY,

email VARCHAR(255),

preferences JSON,

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

);

CREATE INDEX idx_theme ON users(theme);

SELECT * FROM users WHERE theme = 'dark';

Millions of users, frequent preference changes

JSON: No ALTER TABLE needed

Generated columns: Fast queries

Flexible preferences

Lesson: JSON for user preferences

MySQL

Stripe: JSON for Webhook Payloads

Stripe stores webhook payloads with varying structures per event type. Challenge: Different event types have different fields. Solution: Use JSON columns for flexible payload storage.

Stripe uses JSON for webhook payloads: (1) payload JSON column stores event data, (2) Generated column for event type, (3) Index on event type for fast queries. Architecture: webhooks table with payload JSON, generated column (event_type), index. Benefits: Flexible structure per event type, fast queries by event type, no schema changes.

CREATE TABLE webhooks (

webhook_id INT AUTO_INCREMENT PRIMARY KEY,