Работа с массивами и другими сложными типами

🧩 Зачем нужны сложные типы данных?

В SQL не только числа и строки! Массивы, JSON, hstore и другие сложные типы открывают новые возможности:

Компактность — храним связанные данные в одном поле
Гибкость — обрабатываем нереляционные структуры
Производительность — меньше JOIN'ов и подзапросов

🔥 Пример из жизни: интернет-магазин может хранить историю просмотров товаров как массив ID, а характеристики — в JSON. Элегантно и эффективно!

📦 Массивы — ваш новый суперскилл

Работа с массивами в PostgreSQL — это просто и мощно. Основные операции:

-- Создаем таблицу с массивом
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    favorite_colors TEXT[]  -- Массив текстовых значений
);

-- Вставка данных
INSERT INTO users (name, favorite_colors) 
VALUES ('Анна', '{"красный", "синий", "зеленый"}');

-- Доступ по индексу (индексация с 1!)
SELECT favorite_colors[1] FROM users WHERE id = 1;  --> "красный"

-- Поиск элементов
SELECT name FROM users WHERE 'синий' = ANY(favorite_colors);

🔍 Продвинутые трюки с массивами

-- Объединение массивов
SELECT ARRAY[1,2,3] || ARRAY[4,5];  --> {1,2,3,4,5}

-- Развертывание массива в строки
SELECT unnest(favorite_colors) FROM users WHERE id = 1;
-- Результат:
-- красный
-- синий
-- зеленый

-- Фильтрация пустых массивов
SELECT * FROM orders WHERE array_length(items, 1) > 0;

🗂️ JSON — работаем с документами в SQL

PostgreSQL предлагает два типа для JSON: - json — проверяет валидность, но не оптимизирует хранение - jsonb (лучше!) — бинарный формат с индексацией и операторами

CREATE TABLE products (
    id SERIAL,
    data JSONB
);

INSERT INTO products (data) VALUES ('{
    "name": "Смартфон",
    "price": 599.99,
    "specs": {"RAM": "8GB", "storage": "128GB"},
    "tags": ["смартфон", "android", "новинка"]
}');

-- Извлечение значений
SELECT data->>'name' AS name FROM products;  --> "Смартфон"
SELECT data->'specs'->>'RAM' AS ram FROM products;  --> "8GB"

-- Поиск в массиве JSON
SELECT * FROM products 
WHERE data @> '{"tags": ["новинка"]}';

🔧 Модификация JSON

-- Добавление поля
UPDATE products 
SET data = jsonb_set(data, '{warranty}', '"2 года"')
WHERE id = 1;

-- Удаление ключа
UPDATE products 
SET data = data - 'tags'
WHERE id = 1;

🧑‍💻 Комбо-пример: аналитика интернет-магазина

Соберем все знания в одном запросе:

WITH order_stats AS (
    SELECT 
        user_id,
        array_length(product_ids, 1) AS products_count,
        product_ids[1] AS first_product_id
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT 
    u.name,
    os.products_count,
    p.data->>'name' AS first_product_name,
    jsonb_agg(DISTINCT p.data->'category') AS categories
FROM order_stats os
JOIN users u ON u.id = os.user_id
JOIN products p ON p.id = ANY(os.product_ids)
GROUP BY u.name, os.products_count, p.data->>'name';

⚡ Производительность и советы от Данилы Бежина

Для сложных типов важны индексы:

-- GIN-индекс для массивов
CREATE INDEX idx_users_tags ON users USING gin(favorite_colors);

-- GIN-индекс для jsonb
CREATE INDEX idx_products_data ON products USING gin(data jsonb_path_ops);

💡 Совет: Используйте jsonb вместо json везде, где возможно. Разница в производительности может достигать 10 раз!

🏆 Твой следующий уровень

Попробуй применить эти знания:

Создай таблицу с товарами, где характеристики хранятся в jsonb
Напиши запрос для поиска товаров с определенным тегом в массиве
Протестируй разницу между json и jsonb на 100 тыс. записей

Сложные типы — это не страшно. Это мощный инструмент в твоих руках! 🛠️