JSON в SQL: как аналитику парсить вложенные структуры в PostgreSQL и BigQuery без помощи разработчика

Событийные логи, ответы внешних API, конфиги фичей, параметры пушей - всё это приходит в базу одной колонкой типа jsonb или STRING. Разработчики так хранят данные намеренно: гибкая схема удобнее, чем десятки разреженных столбцов. Но аналитику от этого не легче - нужно вытащить конкретное поле, развернуть массив или посчитать метрику по вложенному объекту.

Обычная реакция - написать задачу разработчику: «Можешь добавить отдельные столбцы в витрину?» Но это очередь, приоритеты и несколько дней ожидания. Хорошая новость: базовые операции с полуструктурированными данными аналитик может сделать самостоятельно, прямо в запросе.

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

Зачем это вообще нужно аналитику

Представим типичную таблицу событий. Каждое событие - строка с полями event_name, user_id, created_at и properties. Последнее поле - это объект: у клика по кнопке там лежит название кнопки, у покупки - сумма и список товаров, у регистрации - источник и промокод.

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

PostgreSQL: базовые операторы

В PostgreSQL два типа для хранения - json и jsonb. На практике почти всегда используют jsonb: он индексируется, быстрее при поиске и поддерживает больше операторов. Дальше речь именно о нём.

Три основных оператора:

• -> - извлечь поле, вернуть как jsonb (то есть как объект или массив, а не как текст)
• ->> - извлечь поле, вернуть как text
• #>> - извлечь по пути из массива ключей, вернуть как text

Простой пример: есть таблица events с колонкой properties jsonb. Событие регистрации хранит источник трафика:

-- properties: {"source": "organic", "promo": "SUMMER24"}

SELECT
  user_id,
  properties->>'source' AS source,
  properties->>'promo'  AS promo
FROM events
WHERE event_name = 'registration';

Оператор ->> всегда отдаёт текст. Если нужно число - нужно явно привести тип:

SELECT
  user_id,
  (properties->>'amount')::numeric AS amount
FROM events
WHERE event_name = 'purchase';

Вложенные объекты: цепочка операторов и json_extract_path_text

Когда структура глубже одного уровня, операторы можно цеплять друг за другом. Представим событие просмотра товара, где properties выглядит так:

{
  "product": {
    "id": 4821,
    "category": "electronics",
    "price": 12990
  },
  "session_id": "abc123"
}

Достать категорию товара можно двумя способами:

-- Способ 1: цепочка операторов
SELECT
  properties->'product'->>'category' AS category
FROM events
WHERE event_name = 'product_view';

-- Способ 2: функция json_extract_path_text
SELECT
  json_extract_path_text(properties::json, 'product', 'category') AS category
FROM events
WHERE event_name = 'product_view';

Оба варианта дадут одинаковый результат. Цепочка операторов читается чуть проще, когда уровней немного. Функция удобнее, если путь формируется динамически или передаётся как параметр.

SELECT
  properties->'product'->>'category'        AS category,
  AVG((properties->'product'->>'price')::numeric) AS avg_price,
  COUNT(*)                                    AS views
FROM events
WHERE event_name = 'product_view'
  AND created_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY views DESC;

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

Самая частая задача в событийной аналитике - массив внутри события. Например, покупка содержит список товаров:

{
  "order_id": 99021,
  "items": [
    {"sku": "A1", "qty": 2, "price": 500},
    {"sku": "B3", "qty": 1, "price": 1200}
  ]
}

Чтобы посчитать метрики по каждому товару отдельно, массив нужно «развернуть» - превратить каждый элемент в отдельную строку. Для этого используют jsonb_array_elements:

SELECT
  e.user_id,
  e.created_at,
  item->>'sku'            AS sku,
  (item->>'qty')::int     AS qty,
  (item->>'price')::numeric AS price
FROM events e,
  jsonb_array_elements(e.properties->'items') AS item
WHERE e.event_name = 'purchase';

Конструкция FROM events e, jsonb_array_elements(...) AS item - это неявный LATERAL JOIN. Каждая строка из events «умножается» на количество элементов в массиве. Если в заказе два товара, на выходе получим две строки.

FROM events e,
  jsonb_array_elements(
    COALESCE(e.properties->'items', '[]'::jsonb)
  ) AS item

Если нужны только строки, где массив непустой:

WHERE jsonb_array_length(e.properties->'items') > 0

Фильтрация по содержимому поля

Те же операторы работают в условии WHERE. Можно фильтровать события по значениям внутри объекта:

-- Только события с источником 'paid'
SELECT COUNT(*)
FROM events
WHERE event_name = 'registration'
  AND properties->>'source' = 'paid';

-- Покупки дороже 5000 рублей
SELECT user_id, created_at
FROM events
WHERE event_name = 'purchase'
  AND (properties->>'amount')::numeric > 5000;

Для проверки вхождения используют оператор @> - он проверяет, содержит ли левый объект правый:

-- Все события, где properties содержит "platform": "ios"
SELECT *
FROM events
WHERE properties @> '{"platform": "ios"}'::jsonb;

Этот оператор работает эффективно, если на колонке есть GIN-индекс. Без индекса на больших таблицах будет полный скан.

BigQuery: JSON_VALUE, JSON_QUERY и работа с путями

В BigQuery нет отдельного типа jsonb. Данные обычно хранятся как STRING или как нативный тип JSON (доступен с 2022 года). Большинство продуктовых хранилищ до сих пор используют строку - так исторически сложилось при миграции из Firebase или Amplitude.

Две основные функции для извлечения значений:

• JSON_VALUE(json_string, path) - возвращает скалярное значение как строку
• JSON_QUERY(json_string, path) - возвращает объект или массив как строку в формате JSON

Путь записывается в JSONPath-нотации: $.field, $.nested.field, $.array[0].

-- Аналог примера из PostgreSQL: регистрация с источником
SELECT
  user_id,
  JSON_VALUE(properties, '$.source') AS source,
  JSON_VALUE(properties, '$.promo')  AS promo
FROM `project.dataset.events`
WHERE event_name = 'registration';

Для вложенных полей путь просто удлиняется:

SELECT
  user_id,
  JSON_VALUE(properties, '$.product.category') AS category,
  CAST(JSON_VALUE(properties, '$.product.price') AS FLOAT64) AS price
FROM `project.dataset.events`
WHERE event_name = 'product_view';

Разворачивание массивов в BigQuery: JSON_EXTRACT_ARRAY и UNNEST

В BigQuery нет прямого аналога jsonb_array_elements. Для той же задачи используют связку JSON_EXTRACT_ARRAY и UNNEST:

SELECT
  e.user_id,
  e.created_at,
  JSON_VALUE(item, '$.sku')   AS sku,
  CAST(JSON_VALUE(item, '$.qty') AS INT64) AS qty,
  CAST(JSON_VALUE(item, '$.price') AS FLOAT64) AS price
FROM `project.dataset.events` e,
  UNNEST(JSON_EXTRACT_ARRAY(e.properties, '$.items')) AS item
WHERE e.event_name = 'purchase';

Логика та же, что в PostgreSQL: UNNEST разворачивает массив, каждый элемент становится отдельной строкой. Функция JSON_EXTRACT_ARRAY сначала вытаскивает массив из строки, а потом передаёт его в UNNEST.

SELECT
  JSON_VALUE(item, '$.sku') AS sku,
  SUM(CAST(JSON_VALUE(item, '$.qty') AS INT64)) AS total_qty
FROM `project.dataset.events` e,
  UNNEST(JSON_EXTRACT_ARRAY(e.properties, '$.items')) AS item
WHERE e.event_name = 'purchase'
  AND e.created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY 1
ORDER BY total_qty DESC
LIMIT 20;

Сравнение подходов: PostgreSQL vs BigQuery

Типичные ошибки

Работа с полуструктурированными данными добавляет несколько новых точек отказа, которых нет в обычных плоских таблицах.

Перепутали -> и ->>. Оператор -> возвращает jsonb, а не строку. Если написать WHERE properties->'source' = 'paid', запрос упадёт с ошибкой типов. Для сравнения со строкой нужен ->>.

Не привели тип после извлечения. ->> всегда возвращает текст, даже если в поле лежит число. Арифметика по text работает неявно в одних случаях и ломается в других. Лучше приводить явно: (props->>'amount')::numeric.

NULL при отсутствующем ключе. Если ключ не существует в объекте, оба оператора вернут NULL. Это нормальное поведение, но оно ломает агрегации и условия без COALESCE или IS NOT NULL.

Строка вместо jsonb в PostgreSQL. Иногда данные хранятся как text, а не jsonb. Тогда операторы -> и ->> не работают. Нужно сначала привести к типу: properties::jsonb->>'field'.

Неучтённые вариации в структуре. Разные события могут иметь разную схему в одном поле. Событие click содержит поле button_name, а событие purchase - нет. Если запрос объединяет разные типы событий, нужен COALESCE или явная фильтрация по event_name.

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

На больших таблицах извлечение значений из jsonb без индекса - это полный скан. Несколько сотен миллионов строк превратят любой запрос в часовое ожидание.

GIN-индекс на всю колонку ускоряет оператор @>:

CREATE INDEX idx_events_properties ON events USING GIN (properties);

Если нужен индекс на конкретное поле - делают функциональный индекс:

CREATE INDEX idx_events_source
  ON events ((properties->>'source'));

Тогда условие WHERE properties->>'source' = 'paid' использует индекс и работает быстро.

В BigQuery индексов в классическом понимании нет, но разбиение по дате (PARTITION BY) и кластеризация по часто используемым полям сильно снижают объём обрабатываемых данных. Это особенно важно, когда запрос идёт поверх событийной таблицы с годовой историей.

Чеклист: работа с JSON-полями в запросе

1. Проверь тип колонки: jsonb, json или text в PostgreSQL; STRING или JSON в BigQuery.
2. Если тип text в PostgreSQL - приведи к jsonb перед применением операторов.
3. Для скалярных значений используй ->> или JSON_VALUE - они возвращают строку.
4. Сразу приводи числовые поля к нужному типу: ::numeric, CAST(... AS FLOAT64).
5. Оберни в COALESCE, если ключ может отсутствовать и это влияет на агрегацию.
6. Для массивов: jsonb_array_elements в PostgreSQL, UNNEST(JSON_EXTRACT_ARRAY(...)) в BigQuery.
7. При фильтрации по полю в WHERE проверь, что оператор возвращает нужный тип.
8. Для повторяющихся запросов с извлечением полей рассмотри GIN-индекс или функциональный индекс (PostgreSQL).
9. Если схема нестабильна - фильтруй по event_name перед обращением к конкретным полям.
10. На больших таблицах сначала ограничь данные по дате или другому индексированному полю, потом разворачивай массивы.