JSON и JSONB в PostgreSQL: гайд для аналитика

Главное сразу (BLUF): для аналитики почти всегда нужен тип jsonb, а не json. Доступ к полям — операторами -> (возвращает JSON), ->> (возвращает text), #> / #>> (путь вглубь). Массивы разворачиваешь через jsonb_array_elements, фильтруешь через @> (вложенность) и ? (наличие ключа), ускоряешь через GIN-индекс. Ниже — все операторы на живых примерах с event-логами и UTM-метками.

json vs jsonb: когда что

В PostgreSQL два типа для JSON, и разница между ними принципиальная.

• json хранит текст как есть — байт в байт, с пробелами, порядком ключей и дублями. При каждом обращении к полю Postgres парсит строку заново.
• jsonb хранит разобранное бинарное дерево. Занимает чуть больше места, чуть медленнее на вставке, зато читается в разы быстрее, поддерживает индексы и операторы @>, ?.

Простая проверка, что они и правда разные:

SELECT '{"b": 1, "a": 2, "a": 3}'::json  AS as_json,
       '{"b": 1, "a": 2, "a": 3}'::jsonb AS as_jsonb;

Результат:

     as_json          |    as_jsonb
----------------------+-----------------
 {"b": 1, "a": 2, ... | {"a": 3, "b": 1}

Практическое правило простое.

Дальше всё пишу на jsonb — операторы у типов почти одинаковые, но @>, ? и GIN-индексы есть только у него.

Заведём таблицу событий, на которой будем упражняться. Это типичный event-лог: одна строка — одно событие, весь контекст в колонке payload.

CREATE TABLE events (
    id         bigserial PRIMARY KEY,
    user_id    int,
    event_type text,
    created_at timestamptz,
    payload    jsonb
);

INSERT INTO events (user_id, event_type, created_at, payload) VALUES
(101, 'purchase', '2026-06-01 10:15', '{
    "order_id": 5501, "amount": 2990, "currency": "RUB",
    "utm": {"source": "yandex", "medium": "cpc", "campaign": "summer"},
    "items": [{"sku": "A1", "qty": 2}, {"sku": "B7", "qty": 1}]
}'),
(102, 'purchase', '2026-06-01 11:42', '{
    "order_id": 5502, "amount": 1490, "currency": "RUB",
    "utm": {"source": "vk", "medium": "social"},
    "items": [{"sku": "C3", "qty": 1}]
}'),
(103, 'signup', '2026-06-01 12:03', '{
    "utm": {"source": "yandex", "medium": "cpc", "campaign": "brand"},
    "plan": "free"
}');

Операторы доступа: -> ->> #> #>>

Четыре оператора, которые нужно довести до автоматизма. Разница в двух осях: возвращает JSON или текст, и работает по одному ключу или по пути.

Смотрим на живом поле:

SELECT
    payload -> 'amount'          AS amount_json,   -- 2990 (jsonb)
    payload ->> 'amount'         AS amount_text,   -- "2990" (text)
    payload -> 'utm'             AS utm_json,      -- {"source":"yandex",...}
    payload -> 'utm' ->> 'source' AS utm_source    -- "yandex" (text)
FROM events
WHERE id = 1;

Ключевое отличие -> от ->>: первый оставляет результат JSON-объектом (можно продолжить углубляться, как payload -> 'utm' ->> 'source'), второй сразу отдаёт текст (дальше углубиться нельзя, но можно кастовать в число).

Кастинг — обязательный шаг для чисел. ->> всегда даёт text, поэтому для арифметики приводи тип явно:

SELECT
    (payload ->> 'amount')::numeric  AS amount,     -- 2990, можно суммировать
    (payload ->> 'order_id')::int    AS order_id
FROM events
WHERE event_type = 'purchase';

Если написать SUM(payload ->> 'amount') без каста — получишь ошибку function sum(text) does not exist. Частая ловушка на собесе и в первом рабочем запросе.

Путь через #> и #>> удобен, когда вложенность глубокая. Вместо цепочки -> -> ->> пишешь массив ключей:

SELECT
    payload #>  '{utm, source}'  AS src_json,   -- "yandex" как jsonb
    payload #>> '{utm, source}'  AS src_text,   -- yandex как text
    payload #>> '{items, 0, sku}' AS first_sku  -- A1: индекс массива тоже путь
FROM events
WHERE id = 1;

Извлечение полей в колонки

Первое, что делают с JSON-логом — раскладывают в нормальные колонки, чтобы дальше работать как с обычной таблицей. Это основа любой аналитической задачи над событиями.

SELECT
    id,
    user_id,
    event_type,
    created_at::date                       AS dt,
    (payload ->> 'amount')::numeric        AS amount,
    payload #>> '{utm, source}'            AS utm_source,
    payload #>> '{utm, medium}'            AS utm_medium,
    payload #>> '{utm, campaign}'          AS utm_campaign
FROM events
WHERE event_type = 'purchase';

Получаешь плоскую витрину:

 id | user_id | event_type |    dt      | amount | utm_source | utm_medium | utm_campaign
----+---------+------------+------------+--------+------------+------------+-------------
  1 |     101 | purchase   | 2026-06-01 |   2990 | yandex     | cpc        | summer
  2 |     102 | purchase   | 2026-06-01 |   1490 | vk         | social     | (null)

Заметь: у второго заказа нет campaign — оператор молча вернул NULL, а не упал. Это удобно: JSON-структура может отличаться от строки к строке, и запрос это переживёт.

Если такая витрина нужна регулярно — заверни в представление, чтобы аналитики не копипастили путь {utm, source} по всей кодовой базе:

CREATE VIEW purchases AS
SELECT id, user_id, created_at::date AS dt,
       (payload ->> 'amount')::numeric AS amount,
       payload #>> '{utm, source}'     AS utm_source
FROM events
WHERE event_type = 'purchase';

Такие задачи — разложить JSON-payload в колонки и посчитать метрику — постоянно встречаются в тестовых. Разобрать десяток их вариантов можно в SQL-тренажёре: движок на настоящем PostgreSQL 16, так что операторы ->> и #>> работают один в один.

jsonb_array_elements: разворачиваем массивы

Самый частый и самый недооценённый кейс. В payload лежит массив (список товаров в заказе, список ошибок валидации, теги), и его нужно «размножить» — превратить N элементов массива в N строк. Это делает jsonb_array_elements.

Функция табличная: возвращает по строке на каждый элемент массива. Её ставят в FROM через CROSS JOIN LATERAL (или просто через запятую).

SELECT
    e.id                        AS event_id,
    e.user_id,
    item ->> 'sku'              AS sku,
    (item ->> 'qty')::int       AS qty
FROM events e
CROSS JOIN LATERAL jsonb_array_elements(e.payload -> 'items') AS item
WHERE e.event_type = 'purchase';

Одна строка события с двумя товарами превращается в две строки:

 event_id | user_id | sku | qty
----------+---------+-----+-----
        1 |     101 | A1  |   2
        1 |     101 | B7  |   1
        2 |     102 | C3  |   1

Теперь это обычная таблица — можно агрегировать. Топ SKU по количеству за день:

SELECT
    item ->> 'sku'               AS sku,
    SUM((item ->> 'qty')::int)   AS total_qty
FROM events e
CROSS JOIN LATERAL jsonb_array_elements(e.payload -> 'items') AS item
WHERE e.event_type = 'purchase'
GROUP BY item ->> 'sku'
ORDER BY total_qty DESC;

Два подводных камня.

Первый — пустой или отсутствующий массив. Если у события нет ключа items или он null, jsonb_array_elements при CROSS JOIN выкинет всю строку из результата (как INNER JOIN). Чтобы событие сохранилось, используй LEFT JOIN LATERAL ... ON true:

FROM events e
LEFT JOIN LATERAL jsonb_array_elements(e.payload -> 'items') AS item ON true

Второй — jsonb_array_elements vs jsonb_array_elements_text. Первая возвращает элементы как jsonb, вторая — сразу как text. Если массив из строк (["ios", "push"]), бери _text, чтобы не городить лишний ->>:

SELECT jsonb_array_elements_text('["ios", "push", "beta"]'::jsonb) AS tag;
-- ios / push / beta по строке

Разворачивание массивов — типичный шаг при подготовке событийных витрин, а такие витрины лежат в основе продуктовых метрик. Если хочешь связать это с бизнес-логикой — посмотри разбор метрик и продуктовых кейсов, где JSON-события превращаются в retention и конверсию.

Фильтрация по JSON: @> и ?

Достать поле — полдела. Часто нужно отфильтровать строки по содержимому JSON, и тут есть операторы, которые работают быстрее, чем ->> в WHERE.

@> — «содержит». Проверяет, что левый JSON содержит правый как поддерево. Идеально для точного матча по вложенным полям.

-- Все события из Яндекса с medium = cpc
SELECT id, user_id
FROM events
WHERE payload @> '{"utm": {"source": "yandex", "medium": "cpc"}}';

Это эквивалентно payload #>> '{utm,source}' = 'yandex' AND payload #>> '{utm,medium}' = 'cpc', но короче и — главное — умеет пользоваться GIN-индексом (об этом ниже). @> проверяет именно вложенность: указанные ключи со значениями должны присутствовать, лишние поля в payload не мешают.

? — «есть ли ключ верхнего уровня». Проверяет наличие ключа (или строки в массиве), не глядя на значение.

-- События, где вообще заполнен блок utm
SELECT id FROM events WHERE payload ? 'utm';

-- События, где есть план (только signup в нашем примере)
SELECT id FROM events WHERE payload ? 'plan';

Родственные операторы: ?| — есть хотя бы один из перечисленных ключей, ?& — есть все.

-- Есть хотя бы один из ключей
SELECT id FROM events WHERE payload ?| array['plan', 'order_id'];

-- Есть оба ключа сразу
SELECT id FROM events WHERE payload ?& array['utm', 'items'];

Индексы GIN и производительность

Пока таблица маленькая, всё летает. На проде в events лежат десятки миллионов строк, и запрос WHERE payload @> '...' без индекса делает полный проход (Seq Scan) — сотни миллисекунд, а то и секунды. Спасает GIN-индекс.

GIN (Generalized Inverted Index) строит инвертированный индекс по всем ключам и значениям внутри jsonb. Создаётся одной строкой:

CREATE INDEX idx_events_payload ON events USING gin (payload);

Теперь запросы с @>, ?, ?|, ?& могут использовать индекс. Проверяем планом:

EXPLAIN ANALYZE
SELECT id FROM events
WHERE payload @> '{"utm": {"source": "yandex"}}';
-- Bitmap Index Scan on idx_events_payload  (было Seq Scan)

Есть нюанс, который отделяет джуна от миддла. У GIN два класса операторов:

• jsonb_ops (по умолчанию) — индексирует и ключи, и значения. Поддерживает @>, ?, ?|, ?&. Индекс крупнее.
• jsonb_path_ops — индексирует только пути к значениям. Меньше по размеру и быстрее на @>, но не поддерживает ?, ?|, ?&.

-- Если фильтруешь в основном через @>, бери path_ops — легче и быстрее
CREATE INDEX idx_events_payload_path
ON events USING gin (payload jsonb_path_ops);

А что если фильтр всегда по одному полю, например по utm_source? Тогда GIN избыточен — дешевле сделать обычный B-tree по выражению:

CREATE INDEX idx_events_utm_source
ON events ((payload #>> '{utm, source}'));

-- Этот индекс использует ровно такой предикат:
SELECT id FROM events WHERE payload #>> '{utm, source}' = 'yandex';

Выражение в индексе и в WHERE должны совпадать дословно — иначе планировщик индекс не подхватит.

Правило по индексам: @>-фильтры по разным полям — GIN (jsonb_path_ops, если не нужны ?); один-два фиксированных предиката — B-tree по выражению. И не забудь ANALYZE после массивной вставки, чтобы планировщик увидел свежую статистику. Понимание планов запросов и индексов регулярно спрашивают на собесах — типовые вопросы собраны в разделе вопросов для интервью.

Типовые аналитические задачи

Соберём операторы в задачи, которые реально прилетают в работе.

Задача 1. Разбивка выручки по UTM-источнику. Классический маркетинговый отчёт из event-лога:

SELECT
    payload #>> '{utm, source}'        AS source,
    payload #>> '{utm, medium}'        AS medium,
    COUNT(*)                           AS orders,
    SUM((payload ->> 'amount')::numeric) AS revenue
FROM events
WHERE event_type = 'purchase'
GROUP BY 1, 2
ORDER BY revenue DESC;

Задача 2. Конверсия по источнику: signup → purchase. Один и тот же UTM в разных типах событий:

WITH by_source AS (
    SELECT
        payload #>> '{utm, source}' AS source,
        COUNT(*) FILTER (WHERE event_type = 'signup')   AS signups,
        COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchases
    FROM events
    GROUP BY 1
)
SELECT source, signups, purchases,
       ROUND(purchases::numeric / NULLIF(signups, 0), 3) AS cr
FROM by_source
ORDER BY cr DESC NULLS LAST;

Формула конверсии тут — $CR = \frac{purchases}{signups}$, а NULLIF(signups, 0) защищает от деления на ноль, если у источника были только покупки без регистраций.

Задача 3. Средний размер корзины (по вложенному массиву). Сколько товаров в среднем в заказе — считаем по развёрнутому массиву items:

SELECT
    ROUND(AVG(item_count), 2) AS avg_items_per_order
FROM (
    SELECT e.id, SUM((item ->> 'qty')::int) AS item_count
    FROM events e
    CROSS JOIN LATERAL jsonb_array_elements(e.payload -> 'items') AS item
    WHERE e.event_type = 'purchase'
    GROUP BY e.id
) t;

Задача 4. Проверка качества данных. Сколько покупок пришло без разметки — частый вопрос от маркетинга «почему у нас столько direct/none»:

SELECT
    COUNT(*) FILTER (WHERE NOT (payload -> 'utm' ? 'source')) AS no_source,
    COUNT(*)                                                  AS total
FROM events
WHERE event_type = 'purchase';

Эти четыре паттерна — плоская витрина, конверсия через FILTER, разворот массива, аудит полноты — покрывают львиную долю задач с JSON. Отработать их на большем числе примеров можно в тренажёре и на реальных тестовых заданиях, а системно подготовиться к собесу — на курсе и в гайде по собеседованию аналитика данных.

Частые вопросы

Когда использовать json, а когда jsonb?

Почти всегда jsonb. Он поддерживает индексы (GIN), операторы вложенности @> и ?, читается быстрее за счёт бинарного хранения. Единственный аргумент за json — нужно сохранить исходную строку абсолютно точно: порядок ключей, пробелы, дубликаты ключей (например, сырой лог вебхука «как пришёл» для аудита). Для витрин, отчётов и любой фильтрации — только jsonb.

В чём разница между -> и ->> ?

Оба достают поле по ключу, но -> возвращает jsonb (можно продолжить углубляться дальше), а ->> возвращает text (последний шаг, дальше углубиться нельзя). Практическое следствие: чтобы посчитать сумму по числовому полю, бери ->> и кастуй — (payload ->> 'amount')::numeric. Если написать SUM(payload ->> 'amount') без каста, будет ошибка «function sum(text) does not exist».

Как развернуть JSON-массив в строки?

Через табличную функцию jsonb_array_elements(массив) в FROM с CROSS JOIN LATERAL — она даёт по строке на каждый элемент. Если массив может быть пустым или отсутствовать, а строку события нужно сохранить, используй LEFT JOIN LATERAL ... ON true. Для массива строк удобнее jsonb_array_elements_text — она сразу возвращает text без лишнего ->>.

Ускорит ли GIN-индекс любой запрос по jsonb?

Нет. GIN ускоряет операторы вложенности и наличия ключа — @>, ?, ?|, ?&. Он не помогает предикатам вида payload ->> 'x' = 'y' — для них нужен обычный B-tree по выражению: CREATE INDEX ... ((payload #>> '{utm,source}')). Причём выражение в индексе и в WHERE должны совпадать дословно. Если фильтруешь в основном через @> — вариант jsonb_path_ops компактнее и быстрее, но не поддерживает ?.

JSON в PostgreSQL — не «сложная тема для сениоров», а обычный рабочий инструмент. Четыре оператора доступа, jsonb_array_elements для массивов, @> и ? для фильтров, GIN для скорости — этого набора хватает на 90% задач с событийными логами. Остальное нарабатывается практикой: бери свою events-таблицу, разложи payload в колонки, посчитай выручку по UTM — и следующая задача с JSON перестанет быть проблемой.