-- JSONB queries
SELECT data->>'name' as name, data->'address'->>'city' as city
FROM customers
WHERE data @> '{"active": true}'::jsonb;

-- Array operations
SELECT name, tags
FROM products
WHERE 'electronics' = ANY(tags);

-- Window functions
SELECT
    department,
    employee,
    salary,
    salary - LAG(salary) OVER (PARTITION BY department ORDER BY hire_date) as salary_change
FROM employees;

-- CTEs with RETURNING
WITH inserted AS (
    INSERT INTO orders (customer_id, total)
    VALUES (1, 99.99)
    RETURNING *
)
SELECT * FROM inserted;

-- Full-text search
SELECT title, ts_rank(search_vector, query) as rank
FROM articles, to_tsquery('english', 'database & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;