Примеры сложных запросов с JSONB

В предыдущих лекциях мы изучили основы JSONB: как создавать, изменять и извлекать данные. Теперь пришло время для настоящих испытаний — сложных запросов, которые покажут всю мощь этого типа данных.

Представьте интернет-магазин с каталогом товаров. У каждого товара есть базовая информация (название, ID), но характеристики могут кардинально отличаться: у ноутбука есть RAM и CPU, у одежды — размеры и материалы, у книг — авторы и жанры. Хранить всё это в отдельных таблицах? Неудобно. В JSONB? Идеально! Но как найти все товары определенного бренда, отсортировать их по цене или посчитать статистику по категориям? Как работать с данными, которые находятся не в обычных столбцах, а спрятаны внутри JSON-структуры?

Сегодня мы разберем реальные сценарии: от простой фильтрации до комплексных запросов с группировкой и агрегацией. Вы увидите, как JSONB превращает PostgreSQL в гибкий инструмент для работы с любыми данными.

Фильтрация данных в JSONB

Фильтрация данных — это как ситечко для чая: вы оставляете только то, что нужно, и убираете лишнее. С JSONB все становится еще интереснее, так как мы можем фильтровать не только по обычным столбцам, но и по данным, спрятанным глубоко внутри JSON-структуры.

Операторы для фильтрации JSONB:

• @> — "JSONB-содержит". Проверяет, содержит ли JSONB-объект указанный подмножество.
• ? — "Ключ присутствует". Проверяет, есть ли указанный ключ в JSONB-объекте.
• ?| — "Любой из ключей присутствует". Проверяет, есть ли хотя бы один из указанных ключей.
• ?& — "Все ключи присутствуют". Проверяет, что все указанные ключи присутствуют.

Пример: фильтрация по ключу и его значению. Допустим, у нас есть таблица products с колонкой details, которая хранит JSONB-информацию о товарах:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

Пример данных:

INSERT INTO products (name, details) VALUES
('Laptop', '{"brand": "Dell", "price": 1200, "specs": {"ram": "16GB", "cpu": "i7"}}'),
('Smartphone', '{"brand": "Apple", "price": 800, "specs": {"ram": "4GB", "cpu": "A15"}}'),
('Tablet', '{"brand": "Samsung", "price": 500, "specs": {"ram": "8GB", "cpu": "Exynos"}}');

Результат:

Чтобы найти все продукты с brand "Apple":

SELECT *
FROM products 
WHERE details @> '{"brand": "Apple"}';

Результат:

Если вы хотите найти все продукты, у которых есть ключ specs, используйте оператор ?:

SELECT *
FROM products 
WHERE details ? 'specs';

Результат:

Все строки содержат поле details и ключ specs.

Сортировка данных в JSONB

Иногда вам нужно отсортировать данные не по обычным столбцам, а по значениям, которые находятся внутри JSONB. Для этого вы можете использовать операторы ->> (извлечение значения в виде текста) и CAST для преобразования текстового значения в нужный тип.

Пример: отсортируем продукты по цене:

SELECT *
FROM products 
ORDER BY (details->>'price')::INTEGER;

Результат:

Группировка данных в JSONB

Группировка позволяет агрегировать данные и выводить статистику. Например, вы можете узнать, сколько продуктов приходится на каждый бренд.

Пример: посчитаем количество продуктов для каждого бренда:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand';

Результат:

Практические примеры

Фильтрация и группировка. Посчитаем, сколько продуктов дороже 600 для каждого бренда:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
WHERE (details->>'price')::INTEGER > 600
GROUP BY details->>'brand';

Результат:

Сортировка после группировки. Теперь отсортируем бренды по количеству продуктов:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand'
ORDER BY product_count DESC;

Комплексный запрос: фильтрация, сортировка, группировка

Представим, что вы хотите найти бренды, у которых есть продукты дороже 600 и выбрать один самый дешевый продукт для каждого бренда. Вот как это сделать:

WITH filtered_products AS (
    SELECT *
    FROM products
    WHERE (details->>'price')::INTEGER > 600
)
SELECT
    details->>'brand' AS brand,
    MIN((details->>'price')::INTEGER) AS min_price
FROM filtered_products
GROUP BY details->>'brand'
ORDER BY min_price;

Результат:

Типичные ошибки и советы

Ошибка: Неправильное использование операторов. Не путайте операторы -> и ->>: первый возвращает объект, второй — текстовое значение.

Ошибка: Проблемы с производительностью. Если вы часто выполняете сложные запросы, создайте индекс GIN на JSONB-колонке.

Ошибка: Проблемы с типами. Значения из JSONB — это строки, так что не забывайте использовать CAST.

Пример создания индекса:

CREATE INDEX idx_products_details ON products USING GIN (details);

Теперь фильтрация вроде details @> '{"brand": "Apple"}' будет работать гораздо быстрее.