Приклади складних запитів з 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"}' буде працювати набагато швидше.