Практикум PostgreSQL по JSONB

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Тестировщику Архитектору Инженеру

JSONB в PostgreSQL закрывает частую задачу интеграций: хранить структурированные данные в одном поле, быстро фильтровать по вложенным ключам и агрегировать значения в SQL. Теория типа, отличия от текстового json и от JSON в API — в статье JSONB.

В этом практикуме используем полноценный сценарий:

• создаём таблицу на 6 полей, где одно поле payload JSONB;
• загружаем 10 записей с большим JSON;
• разбираем выборки, фильтрацию, операторы ->, ->>, @>;
• применяем функции jsonb_array_elements, jsonb_set и другие;
• делаем расчёты с приведением типов, ROUND, SUM, COUNT, HAVING;
• добавляем JOIN, CROSS JOIN, USING, REFERENCES, RETURNING;
• создаём индексы и проверяем план запроса через EXPLAIN.

---

Шаг 1 — структура таблиц

Код ITЗагрузка примера кода…

Разбор фрагмента:

• CREATE SCHEMA IF NOT EXISTS создаёт отдельное пространство имён, чтобы учебные таблицы не смешивались с остальными.
• GENERATED ALWAYS AS IDENTITY даёт автоинкрементный ключ современным способом PostgreSQL.
• REFERENCES shop_data.accounts(account_id) связывает событие с существующим аккаунтом и защищает от "висячих" ссылок.
• created_at TIMESTAMPTZ хранит время в таймзоне, что удобно для распределённых систем.
• payload JSONB хранит структурированные данные в бинарном формате, который индексируется.

shop_data.events содержит 6 полей:

1. event_id
2. account_id
3. event_type
4. source
5. created_at
6. payload JSONB

---

Шаг 2 — наполнение данными

Сначала добавим аккаунты.

INSERT INTO shop_data.accounts (account_name)
VALUES
  ('Northwind Trade'),
  ('Pixel Bakery'),
  ('Cloud Retail');

Разбор фрагмента:

• это базовый справочник аккаунтов, на который ссылаются события;
• сначала вставляем "родительские" строки, чтобы FOREIGN KEY в events не ломался.

Теперь добавим 10 событий с крупным JSON.

Код ITЗагрузка примера кода…

RETURNING сразу показывает, какие строки добавились.

Разбор фрагмента:

• каждая запись содержит разные наборы ключей в payload (order, payment, refund, shipment), это реалистичный поток событий;
• суффикс ::jsonb приводит строковый литерал к нужному типу;
• в items вложен массив объектов, который позже развернём через jsonb_array_elements;
• RETURNING полезен в ETL и интеграциях: можно сразу получить идентификаторы новых строк.

---

Шаг 3 — выборка и фильтрация JSONB

Ключевые операторы

• -> возвращает JSON-значение;
• ->> возвращает текст;
• #> возвращает JSON по пути;
• #>> возвращает текст по пути;
• @> проверяет вхождение JSON;
• ? проверяет наличие ключа.

Полный рабочий набор операторов JSON/JSONB в PostgreSQL:

Оператор	Где работает	Что делает	Короткий пример
->	json, jsonb	Достаёт поле JSON-типом	payload->'order'
->>	json, jsonb	Достаёт поле как text	payload->'order'->>'id'
#>	json, jsonb	Достаёт по пути JSON-типом	payload#>'{order,total}'
#>>	json, jsonb	Достаёт по пути как text	payload#>>'{order,total}'
@>	jsonb	"Левый содержит правый"	payload @> '{"flags":{"is_priority":true}}'::jsonb
<@	jsonb	"Левый содержится в правом"	'{"status":"paid"}'::jsonb <@ payload->'order'
?	jsonb	Есть ключ верхнего уровня	payload ? 'payment'
`?	`	jsonb	Есть хотя бы один ключ из списка
?&	jsonb	Есть все ключи из списка	payload ?& array['order','flags']
@?	jsonb	Проверяет JSONPath, вернёт boolean	payload @? '$.items[*] ? (@.qty > 1)'
@@	jsonb	Сопоставляет JSONPath-предикат	payload @@ '$.order.total > 1000'
-	jsonb	Удаляет ключ или элемент	payload - 'metrics'
#-	jsonb	Удаляет по пути	payload #- '{flags,is_gift}'
`		`	jsonb

Когда что применять:

• для чтения полей используйте ->/->> и #>/#>>;
• для фильтрации по структуре используйте @> и GIN-индекс;
• для проверки ключей используйте ?, ?|, ?&;
• для "точечного" обновления и удаления используйте jsonb_set, -, #-.

Код ITЗагрузка примера кода…

Разбор фрагмента:

• в первом запросе payload->'order'->>'id' извлекает вложенный order.id как текст;
• второй запрос показывает путь #>> и фильтрацию по ключу payload ? 'customer';
• третий запрос проверяет частичное вхождение JSON через @>, это основной паттерн для поиска по JSONB.

---

Шаг 4 — функции JSONB и модификация данных

jsonb_array_elements и CROSS JOIN

Разворачиваем массив items в отдельные строки.

SELECT
    e.event_id,
    e.payload->'order'->>'id' AS order_id,
    i.value->>'sku' AS sku,
    (i.value->>'qty')::int AS qty,
    ROUND((i.value->>'price')::numeric, 2) AS price_rounded
FROM shop_data.events e
CROSS JOIN jsonb_array_elements(e.payload->'items') AS i(value)
WHERE e.event_type IN ('order_created', 'order_paid');

Разбор фрагмента:

• jsonb_array_elements(e.payload->'items') превращает массив JSON в набор строк;
• CROSS JOIN умножает строку события на количество элементов массива items;
• (i.value->>'qty')::int и (i.value->>'price')::numeric нужны, чтобы перейти от текста к числам;
• ROUND(..., 2) приводит денежные значения к читаемому виду.

jsonb_set и RETURNING

Обновляем JSONB и сразу проверяем результат.

UPDATE shop_data.events
SET payload = jsonb_set(
    payload,
    '{flags,is_priority}',
    'true'::jsonb,
    true
)
WHERE event_id = 3
RETURNING event_id, payload->'flags' AS updated_flags;

Разбор фрагмента:

• jsonb_set(payload, '{flags,is_priority}', 'true'::jsonb, true) обновляет вложенный ключ;
• четвёртый параметр true разрешает создать путь, если его нет;
• RETURNING сразу показывает JSON после изменения без отдельного SELECT.

Ещё полезные функции

SELECT
    event_id,
    jsonb_typeof(payload->'items') AS items_type,
    jsonb_array_length(payload->'items') AS items_count,
    jsonb_pretty(payload) AS payload_pretty
FROM shop_data.events
WHERE payload ? 'items'
LIMIT 2;

Разбор фрагмента:

• jsonb_typeof помогает валидировать формат входящих данных;
• jsonb_array_length быстро считает элементы в массиве;
• jsonb_pretty форматирует JSON для диагностики в psql/DBeaver.

Предикаты IS JSON (PostgreSQL 16+)

Стандарт SQL/JSON: проверка формы значения без разбора вручную:

SELECT se_id, se_details
FROM tv_series
WHERE se_details->'название' IS NOT JSON ARRAY;

UPDATE tv_series
SET se_details = jsonb_set(
  se_details, '{название}', '["17 мгновений весны"]'::jsonb
)
WHERE se_id = 2;

Предикат	Проверяет
IS JSON	Допустимый JSON
IS JSON OBJECT	Объект
IS JSON ARRAY	Массив
IS JSON SCALAR	Одиночное JSON-значение (строка, число, bool, null)

Конструкторы json_array, json_object, агрегаты json_arrayagg / json_objectagg — в обзоре PG16. Практика на JSON-анкетах — в практикуме demo (схема test в том же дампе).

Дополнительные функции и операторы, которые часто используют

Код ITЗагрузка примера кода…

Разбор фрагмента:

• jsonb_extract_path_text делает то же, что #>>, но в формате функции;
• jsonb_object_keys разворачивает ключи верхнего уровня в набор строк;
• jsonb_build_object собирает новый JSON из колонок SQL;
• @? проверяет JSONPath-выражение, а для фильтрации с условием по пути также применяют @@.

---

Шаг 5 — типы, приведение и сравнение

->> возвращает text, поэтому для арифметики делаем явное приведение.

SELECT
    event_id,
    payload->'order'->>'id' AS order_id,
    (payload->'order'->>'total')::numeric AS total_num,
    ROUND((payload->'order'->>'total')::numeric, 1) AS total_rounded_1
FROM shop_data.events
WHERE (payload->'order'->>'total')::numeric > 500::numeric;

Сравнение с приведением полезно для чисел в JSON, которые пришли как строки.

Разбор фрагмента:

• ->> всегда возвращает text, даже если внутри JSON было число;
• приведение ::numeric обязательно для корректного сравнения и арифметики;
• лучше явно приводить обе стороны сравнения к числу, чтобы запрос читался однозначно.

---

Шаг 6 — агрегаты, HAVING, JOIN, USING

Сумма и количество по аккаунтам

SELECT
    e.account_id,
    COUNT(*) AS events_count,
    ROUND(SUM((e.payload->'order'->>'total')::numeric), 2) AS total_sum
FROM shop_data.events e
WHERE e.payload ? 'order'
GROUP BY e.account_id
HAVING COUNT(*) >= 2
ORDER BY total_sum DESC;

Разбор фрагмента:

• COUNT(*) считает события по аккаунту;
• SUM((...)->>'total')::numeric даёт сумму заказов;
• HAVING COUNT(*) >= 2 фильтрует уже после группировки.

JOIN с таблицей аккаунтов

SELECT
    a.account_name,
    e.event_type,
    COUNT(*) AS cnt
FROM shop_data.events e
JOIN shop_data.accounts a USING (account_id)
GROUP BY a.account_name, e.event_type
ORDER BY a.account_name, cnt DESC;

USING (account_id) работает, когда имя колонки одинаковое в обеих таблицах.

Разбор фрагмента:

• JOIN ... USING (account_id) короче записи ON e.account_id = a.account_id;
• в результате колонка account_id остаётся одной, без дубля;
• агрегат COUNT(*) показывает частоту событий по типу и аккаунту.

---

Шаг 7 — REFERENCES и каскадная логика

Связь уже задана в DDL:

account_id BIGINT NOT NULL REFERENCES shop_data.accounts(account_id)

Эта ссылка гарантирует, что событие нельзя вставить с несуществующим account_id.

Пример проверки:

INSERT INTO shop_data.events (account_id, event_type, source, payload)
VALUES (9999, 'order_created', 'api', '{"order":{"id":"ORD-X"}}'::jsonb);

Запрос завершится ошибкой foreign key violation.

Разбор фрагмента:

• проверка демонстрирует работу REFERENCES;
• СУБД останавливает вставку до попадания "битой" строки в таблицу;
• такой контроль лучше держать на уровне БД, а не только в коде приложения.

---

Шаг 8 — индексы для JSONB и эффект в плане

Создание индексов

-- Универсальный GIN для @>, ?, ?|, ?&
CREATE INDEX IF NOT EXISTS idx_events_payload_gin
ON shop_data.events
USING GIN (payload);

-- Индекс по выражению для быстрых сравнений total
CREATE INDEX IF NOT EXISTS idx_events_order_total_num
ON shop_data.events (((payload->'order'->>'total')::numeric));

ANALYZE shop_data.events;

Разбор фрагмента:

• GIN (payload) ускоряет операции @>, ?, ?|, ?&;
• индекс по выражению ускоряет числовые фильтры по вложенному order.total;
• ANALYZE обновляет статистику, чтобы оптимизатор выбирал корректный план.

Проверка использования индексов

EXPLAIN (ANALYZE, BUFFERS)
SELECT event_id
FROM shop_data.events
WHERE payload @> '{"flags":{"is_priority": true}}'::jsonb;

EXPLAIN (ANALYZE, BUFFERS)
SELECT event_id
FROM shop_data.events
WHERE ((payload->'order'->>'total')::numeric) > 10000;

Ожидаемый результат:

• для фильтра @> план обычно показывает Bitmap Index Scan или Index Scan по idx_events_payload_gin;
• для числового условия по total план использует idx_events_order_total_num;
• при маленьких таблицах PostgreSQL может выбрать Seq Scan, это нормальное решение оптимизатора.

Разбор фрагмента:

• EXPLAIN (ANALYZE, BUFFERS) показывает и фактическое время, и чтения страниц;
• сравнивайте планы до и после индекса;
• на маленьких объёмах индекс может проигрывать Seq Scan по стоимости, это штатно.

---

Шаг 9 — готовый мини-набор запросов для практики

Код ITЗагрузка примера кода…

Разбор фрагмента:

• блок 1 объединяет базовые извлечения, фильтры и приведения типов;
• блок 2 даёт фильтр по структурному условию внутри JSON;
• блок 3 показывает классический шаблон "развернуть массив -> агрегировать -> отфильтровать через HAVING".

---

Что важно запомнить

1. Для боевых фильтров и поиска в PostgreSQL используйте JSONB.
2. Для вычислений приводите ->> к нужному типу, например ::numeric или ::int.
3. Для массивов внутри JSON удобно применять jsonb_array_elements и CROSS JOIN.
4. Для частых условий по JSON используйте GIN и индексы по выражениям.
5. Для проверки эффекта индекса всегда смотрите EXPLAIN (ANALYZE, BUFFERS).

---