Postgres JSONB is a document store hiding inside your relational database

-- Schema with JSONB column
CREATE TABLE users (
    id UUID PRIMARY KEY,
    email TEXT NOT NULL,
    preferences JSONB DEFAULT '{}'
);

-- Insert with JSONB
INSERT INTO users (id, email, preferences) VALUES (
    gen_random_uuid(),
    'alice@example.com',
    '{"theme": "dark", "language": "en", "notifications": {"email": true, "push": false}}'
);

-- Operators:
-- -> returns JSON (preserves type as JSON)
-- ->> returns text (for string comparisons)

SELECT preferences -> 'theme' FROM users;           -- Returns "dark" (JSON string)
SELECT preferences ->> 'theme' FROM users;          -- Returns dark (text)
SELECT preferences -> 'notifications' -> 'email'    -- Nested access
FROM users;

-- Filtering with JSONB
SELECT * FROM users WHERE preferences ->> 'theme' = 'dark';
SELECT * FROM users WHERE (preferences -> 'notifications' ->> 'email') = 'true';

-- Contains operator @> (very useful for array membership checks)
SELECT * FROM users WHERE preferences @> '{"theme": "dark"}';

-- Update a single key without touching others
UPDATE users
SET preferences = jsonb_set(preferences, '{theme}', '"light"')
WHERE id = $1;

-- Update a nested key
UPDATE users
SET preferences = jsonb_set(preferences, '{notifications, push}', 'true')
WHERE id = $1;

-- Remove a key
UPDATE users
SET preferences = preferences - 'legacy_setting'
WHERE id = $1;

-- Merge two JSON objects (the right side wins on key conflicts)
UPDATE users
SET preferences = preferences || '{"theme": "light", "new_feature": true}'
WHERE id = $1;

-- GIN index: supports @>, ?, ?|, ?& operators
-- Best for: containment queries, key existence checks
CREATE INDEX idx_users_preferences ON users USING gin (preferences);

-- Expression index on a specific path: best for ->> equality
CREATE INDEX idx_users_theme 
ON users ((preferences ->> 'theme'));

-- Partial index: if most queries have a condition
CREATE INDEX idx_users_dark_theme 
ON users ((preferences ->> 'theme'))
WHERE preferences ->> 'theme' = 'dark';

-- After creating the GIN index, these become index-friendly:
SELECT * FROM users WHERE preferences @> '{"theme": "dark"}';  -- Fast with GIN
SELECT * FROM users WHERE preferences ? 'notifications';        -- Fast with GIN

-- E-commerce: different product types have different attributes
CREATE TABLE products (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    price_cents INTEGER NOT NULL,
    category TEXT NOT NULL,
    attributes JSONB DEFAULT '{}'
);

-- Shirt has size/color, laptop has RAM/storage — all in one table
INSERT INTO products (id, name, price_cents, category, attributes) VALUES
    (gen_random_uuid(), 'T-Shirt', 2999, 'clothing',
     '{"sizes": ["S","M","L","XL"], "color": "blue", "material": "cotton"}'),
    (gen_random_uuid(), 'MacBook Pro', 199900, 'electronics',
     '{"ram_gb": 16, "storage_gb": 512, "display_inches": 14}');

-- Query across different attribute shapes
SELECT name, attributes ->> 'ram_gb' AS ram
FROM products
WHERE category = 'electronics'
  AND (attributes ->> 'ram_gb')::integer >= 16;

-- Find all products available in size 'M'
SELECT name FROM products
WHERE attributes -> 'sizes' ? 'M';

-- Aggregate JSON data across rows
SELECT
    category,
    jsonb_agg(
        jsonb_build_object('id', id, 'name', name, 'price', price_cents)
        ORDER BY price_cents DESC
    ) AS products
FROM products
GROUP BY category;

-- Expand JSONB arrays into rows with jsonb_array_elements
SELECT
    p.name,
    size
FROM products p,
     jsonb_array_elements_text(p.attributes -> 'sizes') AS size
WHERE p.category = 'clothing'
ORDER BY p.name, size;