JSON and JSONB Columns

Objective

Create tables with JSON and JSONB columns for storing flexible, semi-structured data. JSON columns are ideal for metadata, configurations, and data that varies between records.

Step 1: Create Table with JSON Column

Create a table using JSON for flexible metadata storage.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    base_price DECIMAL(10, 2) NOT NULL,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Records with JSON Data

Insert records with varied JSON structures.

INSERT INTO products (id, name, base_price, metadata) VALUES
(1, 'Laptop Pro', 1299.99,
    '{"brand": "TechCo", "specs": {"cpu": "Intel i7", "ram": "16GB", "storage": "512GB SSD"}, "colors": ["silver", "black"]}'),
(2, 'Wireless Mouse', 49.99,
    '{"brand": "ClickMaster", "specs": {"dpi": 1600, "buttons": 6, "wireless": true}, "colors": ["black", "white", "blue"]}'),
(3, 'USB Hub', 29.99,
    '{"brand": "ConnectAll", "specs": {"ports": 7, "usb_version": "3.0", "powered": true}}');

Step 3: Create Table with JSONB Column

JSONB is binary JSON format, optimized for querying.

CREATE TABLE user_preferences (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    preferences JSONB,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO user_preferences (id, user_id, preferences) VALUES
(1, 101, '{"theme": "dark", "language": "en", "notifications": {"email": true, "push": false, "sms": false}}'),
(2, 102, '{"theme": "light", "language": "es", "notifications": {"email": true, "push": true, "sms": true}}'),
(3, 103, '{"theme": "auto", "language": "fr", "notifications": {"email": false, "push": true, "sms": false}}');

Step 4: Store Complex Nested JSON

Create a table for storing event data with complex JSON structures.

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    event_data JSON NOT NULL,
    occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (id, event_type, event_data) VALUES
(1, 'user_signup',
    '{"user": {"id": 1001, "email": "new@example.com"}, "source": "web", "campaign": "summer2024", "metadata": {"browser": "Chrome", "os": "Windows"}}'),
(2, 'purchase',
    '{"order_id": 5001, "items": [{"sku": "ABC123", "qty": 2, "price": 29.99}, {"sku": "XYZ789", "qty": 1, "price": 49.99}], "total": 109.97}'),
(3, 'page_view',
    '{"page": "/products/laptop", "referrer": "google.com", "session_id": "sess_abc123", "duration_seconds": 45}');

Step 5: Query JSON Data

Query and filter records based on JSON content.

-- Select all products with their metadata
SELECT id, name, base_price, metadata
FROM products;

-- Select events by type
SELECT event_type, event_data, occurred_at
FROM events
WHERE event_type = 'purchase';

Step 6: Configuration Storage with JSON

Use JSON for application configuration storage.

CREATE TABLE app_configurations (
    id INTEGER PRIMARY KEY,
    config_name VARCHAR(100) NOT NULL UNIQUE,
    config_data JSONB NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    version INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO app_configurations (id, config_name, config_data) VALUES
(1, 'email_settings',
    '{"smtp_host": "smtp.example.com", "smtp_port": 587, "use_tls": true, "from_address": "noreply@example.com", "templates": {"welcome": "tmpl_001", "reset": "tmpl_002"}}'),
(2, 'feature_flags',
    '{"new_dashboard": true, "beta_features": false, "dark_mode": true, "experimental_api": false}'),
(3, 'rate_limits',
    '{"api": {"requests_per_minute": 100, "burst": 20}, "uploads": {"max_size_mb": 50, "allowed_types": ["jpg", "png", "pdf"]}}');

SELECT config_name, config_data, version
FROM app_configurations
WHERE is_active = TRUE;

Step 7: Audit Log with JSON

Create an audit log table storing changes as JSON.

CREATE TABLE audit_log (
    id INTEGER PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id INTEGER NOT NULL,
    action VARCHAR(20) NOT NULL,
    old_values JSON,
    new_values JSON,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO audit_log (id, table_name, record_id, action, old_values, new_values, changed_by) VALUES
(1, 'products', 1, 'UPDATE',
    '{"base_price": 1199.99}',
    '{"base_price": 1299.99}',
    'admin@example.com'),
(2, 'users', 101, 'UPDATE',
    '{"status": "pending", "verified": false}',
    '{"status": "active", "verified": true}',
    'system'),
(3, 'products', 4, 'INSERT',
    NULL,
    '{"name": "New Product", "base_price": 79.99}',
    'admin@example.com');

SELECT table_name, record_id, action, old_values, new_values, changed_at
FROM audit_log
ORDER BY changed_at DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS user_preferences;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS app_configurations;
DROP TABLE IF EXISTS audit_log;

Expected Outcomes

• JSON columns store varied structures
• JSONB optimized for binary storage
• Complex nested objects supported
• Flexible schema per record
• Ideal for metadata and configurations

JSON vs JSONB

Key Concepts Learned

• JSON column type for flexible data
• JSONB for optimized binary storage
• Storing nested objects and arrays
• Configuration and metadata patterns
• Audit logging with JSON