User Preferences with JSON and Generated Columns

intermediate

Application needs to store user preferences (theme, language, notifications). Traditional approach: Multiple columns (requires ALTER TABLE for new preferences). Solution: Use JSON column with generated columns for frequently queried fields.

-- Create users table with JSON preferences

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 on generated columns

CREATE INDEX idx_users_theme ON users(theme);

CREATE INDEX idx_users_language ON users(language);

-- Insert users with JSON preferences

INSERT INTO users (email, preferences) VALUES

(

'john@example.com',

JSON_OBJECT(

'theme', 'dark',

'language', 'en',

'notifications', JSON_OBJECT(

'email', true,

'sms', false,

'push', true

),

'timezone', 'UTC'

)

),

(

'jane@example.com',

JSON_OBJECT(

'theme', 'light',

'language', 'es',

'notifications', JSON_OBJECT(

'email', false,

'sms', true,