JSON и JSONB в SQL: как достать значение и искать по свойствам событий

Операторы, которые нужны в 90% запросов

Базовых операторов немного, и различие между ними принципиальное. Стрелка -> возвращает JSON-значение, то есть снова jsonb. Двойная стрелка ->> извлекает то же значение как текст. Для вложенных путей есть #> и #>> — они работают так же, но принимают массив ключей.

Разница между -> и ->> не формальная. Первый оставляет значение в JSON, поэтому строка вернётся вместе с кавычками, а сравнение с обычным текстом не сработает. Второй отдаёт готовый text, который можно сравнивать, группировать и приводить к нужному типу. В аналитических запросах почти всегда нужен ->>, а -> пригодится, когда вы идёте глубже по структуре или проверяете тип значения.

Отдельно стоят операторы проверки. ? отвечает на вопрос «есть ли такой ключ», а @> — «содержит ли этот JSON вот такой фрагмент». Второй особенно полезен для флагов эксперимента: одним условием можно проверить сразу пару ключ-значение, и именно этот оператор умеет опираться на индекс. Начиная с PostgreSQL 12 к ним добавился язык путей JSONPath с операторами @? и @@ — он удобнее, когда условие уходит в глубину вложенной структуры.

Всё, что извлечено двойной стрелкой, — это текст

Оператор ->> всегда возвращает text, независимо от того, что лежало в JSON. Пока значение просто показывают, это незаметно. Проблема начинается при сортировке и агрегации: как текст «9» больше, чем «100», а sum по такому полю вообще не выполнится без приведения.

Приведение решает задачу, но приносит новый риск: если хотя бы одна строка содержит не число, весь запрос упадёт с ошибкой конвертации. Причём упасть он может через месяц после написания — когда в JSON приедет пустая строка или значение "none" из нового релиза.

Надёжный вариант — приводить тип только там, где вы уверены в содержимом, и проверять это через jsonb_typeof. Такой запрос не падает и заодно показывает, сколько событий пришло с неожиданным типом: это и есть метрика качества трекинга.

В учебной базе симулятора JSON-столбцов нет, поэтому запросы статьи несут данные с собой — с пометкой «учебная вставка» — и выполняются в PostgreSQL; запись для DuckDB и песочницы — в «Частых вопросах». В примере ниже семь покупок: у тарифа pro скидки складываются в 20, а ещё одна, пришедшая строкой "10", в сумму не попала и посчитана отдельно.

-- учебная вставка: семь покупок с разным содержимым discount
WITH events (event_id, properties) AS (
  VALUES
    (1, '{"plan":"pro","discount":15}'::jsonb),
    (2, '{"plan":"pro","discount":"10"}'::jsonb),
    (3, '{"plan":"pro","discount":5}'::jsonb),
    (4, '{"plan":"basic","discount":null}'::jsonb),
    (5, '{"plan":"basic"}'::jsonb),
    (6, '{"plan":"basic","discount":""}'::jsonb),
    (7, '{"plan":"team","discount":0}'::jsonb)
)
SELECT
  properties->>'plan' AS plan,
  count(*) AS purchases,
  sum(
    CASE WHEN jsonb_typeof(properties->'discount') = 'number'
         THEN CAST(properties->>'discount' AS numeric)
    END
  ) AS discount_total,
  count(*) FILTER (WHERE jsonb_typeof(properties->'discount') = 'string') AS discount_as_string,
  count(*) FILTER (WHERE NOT properties ? 'discount')                     AS discount_missing
FROM events
GROUP BY 1
ORDER BY purchases DESC, plan;

Три разных способа не иметь значения

В JSONB «нет значения» бывает трёх видов, и они ведут себя по-разному. Ключа может не быть вообще. Ключ может присутствовать со значением JSON null. И значение может быть пустой строкой — формально заполнено, фактически нет.

Ловушка в том, что properties->>'discount' для первых двух случаев вернёт одинаковый SQL NULL, а вот properties ? 'discount' для JSON null вернёт true: ключ-то есть. Если различие важно для интерпретации — «параметр не передали» против «передали пустым» — проверять нужно именно через ? и jsonb_typeof, а не через сравнение извлечённого текста с NULL.

На практике это различие часто и объясняет расхождение метрик. «Скидки нет» и «скидку не залогировали» — разные вещи: первое можно считать нулём, второе честнее показать отдельной строкой отчёта.

Разбивка по свойству теряет события без ключа: 9 200 из 9 510 покупок

Когда свойство хранит не одно значение, а список билетов или допуслуг, оператора ->> недостаточно: после разворота JSON-массива в строки меняется зерно результата, и пустые списки надо сохранять осознанно. Отдельный пример показывает это на покупках учебной базы авиакомпании.

Три вида пропуска выше — это про одно событие. Теперь посмотрим, что они делают со знаменателем отчёта. Пример условный: запрос ниже собирает июль учебной вставкой — 9 510 событий purchase. Ключ plan есть у 9 200 из них, у остальных 310 его нет вовсе — 3,3% покупок. Разбивка по тарифу через properties->>'plan' отправит эти 310 строк в группу с NULL, и дальше всё зависит от того, кто смотрит отчёт: BI часто прячет пустую категорию, а человек читает оставшиеся группы как полную картину.

С деньгами из того же JSON картина хуже, потому что пропуск расползается по двум причинам сразу. Значение amount приехало числом у 8 700 событий. У 190 оно пришло строкой или пустым — защищённое приведение типа их отбрасывает, и sum уменьшается молча. Ещё у 620 событий ключа amount нет совсем: 9 510 минус 8 700 минус 190. Средний чек, посчитанный как avg, поделит выручку на 8 700 — на те строки, где значение удалось прочитать, — и окажется выше правды, если в пропущенных покупках суммы были небольшими. Ни одно из этих чисел не требует доверия на слово: все четыре бакета складываются в 9 510, и проверить это можно одним запросом.

Рабочее правило простое: событие без ключа остаётся в отчёте, но в явной категории. coalesce(properties->>'plan', '(не передано)') превращает невидимую группу NULL в строку, которую читатель дашборда заметит и про которую спросит. Это одна функция, и она меняет не число, а то, видно ли его. Для сверки удобно добавить rollup: он считает те же группы плюс итог по всему периоду, и расхождение между суммой групп и итогом становится видно сразу, в том же результате.

Отдельная ловушка живёт в фильтрах по свойству. Условие properties->>'plan' <> 'free' выглядит как «все тарифы, кроме бесплатного», но для события без ключа оно даёт UNKNOWN, и строка не проходит ни его, ни обратное условие — те самые 310 покупок исчезают из обеих половин разбивки. Если пропуск нужно сохранить, условие пишут как properties->>'plan' is distinct from 'free' или сначала подставляют категорию через coalesce, а фильтруют уже по ней.

Когда свойство попадает в регулярный отчёт, покрытие ключа стоит считать рядом с самой метрикой. Доля событий с заполненным amount — такой же показатель качества, как доля дублей: пока она держится на одном уровне, выручке из JSON можно верить, а её падение объясняет расхождение раньше, чем его найдут финансы.

-- учебная вставка: сколько покупок пришло с каким набором свойств
with shapes (events, properties) as (
  values
    (5100, '{"plan":"pro","amount":29}'::jsonb),
    (3400, '{"plan":"basic","amount":19}'::jsonb),
    (120,  '{"plan":"basic","amount":"19"}'::jsonb),
    (70,   '{"plan":"pro","amount":""}'::jsonb),
    (510,  '{"plan":"pro"}'::jsonb),
    (200,  '{"amount":19}'::jsonb),
    (110,  '{}'::jsonb)
),
events as (   -- каждая строка shapes повторена events раз
  select s.properties
  from shapes s
  cross join lateral generate_series(1, s.events)
)
select
  coalesce(properties->>'plan', '(не передано)')                              as plan_bucket,
  count(*)                                                                    as purchases,
  count(*) filter (where jsonb_typeof(properties->'amount') = 'number')       as amount_number,
  count(*) filter (where properties ? 'amount'
                     and jsonb_typeof(properties->'amount') <> 'number')      as amount_broken,
  count(*) filter (where not properties ? 'amount')                           as amount_missing,
  sum(case when jsonb_typeof(properties->'amount') = 'number'
           then cast(properties->>'amount' as numeric) end)                   as revenue
from events
group by rollup (1)
order by purchases desc, plan_bucket;

-- первая строка — итог rollup: plan_bucket пуст, purchases = 9510.
-- Он обязан совпасть с суммой групп: 5680 + 3520 + 310

Массивы размножают строки — и суммы

Когда в свойствах лежит массив (список товаров в заказе, набор применённых промокодов), возникает соблазн развернуть его через jsonb_array_elements и посчитать по элементам. Функция возвращает набор строк, поэтому одно событие превращается в несколько — и любая агрегация по полям самого события после этого удваивается.

Правило простое: решите заранее, на каком зерне вы отвечаете на вопрос. «Сколько товаров продали» — зерно элемента массива, разворачивать нужно. «Сколько было покупок» — зерно события, и здесь достаточно jsonb_array_length без разворачивания.

Если нужны оба числа в одном отчёте, считайте их в разных слоях и соединяйте по ключу события, а не смешивайте в одном SELECT после разворачивания.

В примере ниже четыре покупки и шесть позиций. После разворачивания строк становится шесть, а покупка без items из результата исчезает.

-- учебная вставка: четыре покупки, у последней списка items нет
WITH events (event_id, properties) AS (
  VALUES
    (1, '{"items":[{"sku":"A-1","price":1200},{"sku":"B-7","price":300}]}'::jsonb),
    (2, '{"items":[{"sku":"B-7","price":300}]}'::jsonb),
    (3, '{"items":[{"sku":"A-1","price":1200},{"sku":"C-3","price":500},{"sku":"B-7","price":300}]}'::jsonb),
    (4, '{"plan":"pro"}'::jsonb)
)
SELECT count(*) AS purchases,
       count(*) FILTER (WHERE jsonb_typeof(properties->'items') = 'array') AS with_items,
       sum(CASE WHEN jsonb_typeof(properties->'items') = 'array'
                THEN jsonb_array_length(properties->'items')
           END) AS items_total
FROM events;

-- учебная вставка: те же четыре покупки
WITH events (event_id, properties) AS (
  VALUES
    (1, '{"items":[{"sku":"A-1","price":1200},{"sku":"B-7","price":300}]}'::jsonb),
    (2, '{"items":[{"sku":"B-7","price":300}]}'::jsonb),
    (3, '{"items":[{"sku":"A-1","price":1200},{"sku":"C-3","price":500},{"sku":"B-7","price":300}]}'::jsonb),
    (4, '{"plan":"pro"}'::jsonb)
)
SELECT item->>'sku'                         AS sku,
       count(*)                             AS times_bought,
       sum(CAST(item->>'price' AS numeric)) AS revenue
FROM events e
CROSS JOIN LATERAL jsonb_array_elements(e.properties->'items') AS item
GROUP BY 1
ORDER BY revenue DESC, sku;

Как выглядит расхождение с биллингом

Типичная история: продуктовая аналитика показывает средний размер скидки 12%, финансы видят 14,8%. Обе стороны уверены в своих цифрах, обсуждение уходит в спор о методологии. На деле разница почти всегда собирается из мелочей внутри JSON, и её можно разложить по слагаемым.

Начните с покрытия: какая доля событий вообще содержит нужный ключ. Затем посмотрите на типы: сколько значений пришло строкой вместо числа и потерялось при безопасном приведении. Потом на нули и null: считаются ли они как «скидки не было» или выпадают из знаменателя. И только после этого сравнивайте формулы.

На графике ниже — условный пример такого разложения. Полезно строить его один раз и оставлять в дашборде качества: тогда следующее расхождение объясняется за минуту, а не за встречу на час.

Индексы: какой запрос вы собираетесь ускорять

JSONB индексируется, но универсального индекса под все запросы не существует. GIN-индекс по всему полю поддерживает проверки существования и вхождения — ?, @> и операторы JSONPath. Это правильный выбор, когда фильтры разнообразны и ключи заранее неизвестны.

Если запросы всегда идут по одному конкретному ключу, дешевле обычный B-tree индекс по выражению: он меньше, быстрее обновляется и работает с равенством и диапазонами. Классический пример — фильтр по тарифу или по группе A/B-теста в каждом отчёте.

У GIN есть облегчённый вариант jsonb_path_ops: проверку наличия ключа ? он не поддерживает — только вхождение @> и операторы JSONPath, — но занимает заметно меньше места. Выбор между ними — это выбор между гибкостью и стоимостью, и делать его стоит по реальному списку запросов, а не заранее.

Блок ниже строит все три индекса на 20 000 событий во временной таблице. Проверку @> планировщик ведёт через jsonb_path_ops, проверку ? — через полный GIN, равенство по тарифу — через B-tree. В PostgreSQL 14 на этих данных полный GIN занимает 232 kB, облегчённый — 136 kB.

BEGIN;

-- учебная вставка: 20 000 событий во временной таблице
CREATE TEMP TABLE demo_events ON COMMIT DROP AS
SELECT g AS event_id,
       jsonb_build_object(
         'plan', CASE WHEN g % 3 = 0 THEN 'pro' ELSE 'basic' END,
         'screen', 'screen_' || g % 40,
         'ab_group', CASE WHEN g % 100 = 0 THEN 'test' ELSE 'control' END
       ) AS properties
FROM generate_series(1, 20000) AS g;

-- разнообразные фильтры по разным ключам
CREATE INDEX demo_events_props_gin ON demo_events USING gin (properties);

-- без проверки наличия ключа, зато компактнее
CREATE INDEX demo_events_props_path ON demo_events USING gin (properties jsonb_path_ops);

-- один часто используемый ключ
CREATE INDEX demo_events_plan_idx ON demo_events ((properties->>'plan'));

ANALYZE demo_events;

-- проверяем, какой индекс выбрал планировщик
EXPLAIN (COSTS OFF)
SELECT count(*) FROM demo_events
WHERE properties @> '{"ab_group": "test"}';
-- Bitmap Index Scan on demo_events_props_path

EXPLAIN (COSTS OFF)
SELECT count(*) FROM demo_events WHERE properties ? 'promo';
-- Bitmap Index Scan on demo_events_props_gin

EXPLAIN (COSTS OFF)
SELECT count(*) FROM demo_events WHERE properties->>'plan' = 'pro';
-- Bitmap Index Scan on demo_events_plan_idx

SELECT pg_size_pretty(pg_relation_size('demo_events_props_gin'))  AS gin_full,  -- 232 kB
       pg_size_pretty(pg_relation_size('demo_events_props_path')) AS gin_path;  -- 136 kB

ROLLBACK;  -- таблица и индексы исчезают

Контракт события важнее удобных операторов

Гибкая схема хороша ровно до того момента, пока по ней не считают деньги. Как только параметр из JSON попадает в регулярный отчёт, у него появляются те же требования, что и у обычной колонки: известный тип, известный набор допустимых значений, владелец и правило на случай отсутствия.

Практический порядок такой. Раз в период собирайте каталог ключей: какие вообще встречаются, в каких событиях, с какими типами и долей заполнения. Ключи, которые попали в KPI, выносите в типизированные колонки витрины — это снимает и приведение типов, и вопрос индексов. В JSONB оставляйте то, что действительно меняется часто: экспериментальные атрибуты, отладочный контекст, редкие параметры. В примере ниже discount есть у шести покупок из семи и приходит в трёх видах: числом, строкой и null.

-- учебная вставка: семь покупок
WITH events (event_id, properties) AS (
  VALUES
    (1, '{"plan":"pro","discount":15,"ab_group":"test"}'::jsonb),
    (2, '{"plan":"pro","discount":"10"}'::jsonb),
    (3, '{"plan":"pro","discount":5}'::jsonb),
    (4, '{"plan":"basic","discount":null}'::jsonb),
    (5, '{"plan":"basic"}'::jsonb),
    (6, '{"plan":"basic","discount":""}'::jsonb),
    (7, '{"plan":"team","discount":0}'::jsonb)
)
SELECT
  props.key,
  count(*)                                                     AS events,
  round(100.0 * count(*) / (SELECT count(*) FROM events), 1)   AS pct_of_events,
  count(*) FILTER (WHERE jsonb_typeof(props.value) = 'string') AS as_string,
  count(*) FILTER (WHERE jsonb_typeof(props.value) = 'number') AS as_number,
  count(*) FILTER (WHERE jsonb_typeof(props.value) = 'null')   AS as_null
FROM events e
CROSS JOIN LATERAL jsonb_each(e.properties) AS props(key, value)
GROUP BY props.key
ORDER BY events DESC, props.key;

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

-- учебная вставка: четыре события, свойства — обычный текст
WITH events (event_id, properties) AS (
  VALUES
    (1, '{"plan":"pro","discount":15,"payment":{"method":"card"},"ab_group":"test"}'),
    (2, '{"plan":"basic","discount":"none"}'),
    (3, '{"plan":"basic","discount":null}'),
    (4, '{"plan":"team"}')
)
SELECT event_id,
       properties ->> 'plan'                                 AS plan,
       properties ->> '$.payment.method'                     AS payment_method,  -- вместо #>>
       json_exists(properties, '$.discount')                 AS has_discount,    -- вместо ?
       json_contains(properties, '{"ab_group":"test"}')      AS is_test,         -- вместо @>
       json_type(properties, '$.discount')                   AS discount_type,   -- вместо jsonb_typeof
       TRY_CAST(properties ->> 'discount' AS DECIMAL(10, 2)) AS discount         -- "none" даёт NULL, а не ошибку
FROM events
ORDER BY event_id;

Чеклист перед расчётом по JSONB

Свойства событий — самая частая причина расхождений между продуктовой аналитикой и биллингом. Проверка занимает несколько минут и обычно окупается сразу.

• Для каждого используемого ключа известен ожидаемый тип, и он проверен через jsonb_typeof?
• Отсутствие ключа, JSON null и пустая строка разделены, а не свалены в один NULL?
• Приведение типов защищено от неожиданных значений, чтобы отчёт не падал после релиза?
• При работе с массивами зерно результата выбрано осознанно, и суммы не удвоились после разворачивания?
• Индекс соответствует реальным фильтрам, а не добавлен «на всякий случай»?
• Ключи, которые попали в KPI, вынесены в типизированные колонки витрины?