JSON в SQL: шпаргалка для аналитика

Зачем аналитику JSON

Часто аналитическая таблица содержит колонку payload с JSON: event properties, пользовательские поля, сырой API-ответ.

id | event_type | payload
---|------------|--------
1  | purchase   | {"product_id": 42, "price": 999, "tags": ["promo", "new"]}

Умение парсить такое без выгрузки в Python — базовый навык аналитика.

PostgreSQL

Извлечение скалярного поля

-- вернёт JSON
SELECT payload -> 'product_id' FROM events;
-- → 42

-- вернёт TEXT
SELECT payload ->> 'product_id' FROM events;
-- → '42'

-- вложенные поля
SELECT payload -> 'user' ->> 'email' FROM events;

CAST к нужному типу

SELECT (payload ->> 'price')::NUMERIC AS price FROM events;
SELECT (payload ->> 'created_at')::TIMESTAMPTZ FROM events;

Доступ к массивам

-- первый элемент массива tags
SELECT payload -> 'tags' -> 0 FROM events;

-- развернуть массив в строки
SELECT payload ->> 'product_id', tag
FROM events, jsonb_array_elements_text(payload -> 'tags') AS tag;

Фильтр по JSON-полю

-- точное совпадение
SELECT * FROM events
WHERE payload ->> 'event_name' = 'purchase';

-- по числу
SELECT * FROM events
WHERE (payload ->> 'price')::NUMERIC > 1000;

-- существование ключа
SELECT * FROM events
WHERE payload ? 'product_id';

-- существование любого из ключей
SELECT * FROM events
WHERE payload ?| array['product_id', 'order_id'];

Ключи и значения JSON

-- все ключи объекта
SELECT jsonb_object_keys(payload) FROM events LIMIT 10;

-- развернуть объект в пары key, value
SELECT key, value
FROM events, jsonb_each(payload);

Модификация JSON

-- добавить или обновить поле
SELECT jsonb_set(payload, '{status}', '"updated"') FROM events;

-- удалить поле
SELECT payload - 'temp_field' FROM events;

-- слияние двух JSON
SELECT payload || '{"source": "api"}'::JSONB FROM events;

Индекс на JSON-поле

-- GIN-индекс для быстрого поиска по ключам
CREATE INDEX idx_payload_gin ON events USING GIN (payload);

-- индекс на конкретное поле
CREATE INDEX idx_event_name ON events ((payload ->> 'event_name'));

JSON vs JSONB

	JSON	JSONB
Хранение	текст	binary
Скорость чтения	медленнее	быстрее
Размер	больше	меньше
Сохраняет порядок ключей	да	нет
Поддержка GIN index	нет	да

Правило: всегда JSONB для аналитики.

MySQL

Работает с MySQL 5.7.8+.

Извлечение

-- JSON_EXTRACT или оператор ->
SELECT JSON_EXTRACT(payload, '$.product_id') FROM events;
SELECT payload -> '$.product_id' FROM events;

-- UNQUOTE (как ->> в Postgres)
SELECT payload ->> '$.product_id' FROM events;

-- вложенные поля
SELECT payload ->> '$.user.email' FROM events;

Фильтр

SELECT * FROM events
WHERE JSON_EXTRACT(payload, '$.event_name') = 'purchase';

-- существование ключа
SELECT * FROM events WHERE JSON_CONTAINS_PATH(payload, 'one', '$.product_id');

Массивы

-- первый элемент
SELECT payload ->> '$.tags[0]' FROM events;

-- развернуть — через JSON_TABLE (MySQL 8.0+)
SELECT e.id, t.tag
FROM events e,
JSON_TABLE(
    payload -> '$.tags',
    '$[*]' COLUMNS (tag VARCHAR(100) PATH '$')
) t;

ClickHouse

-- простой доступ
SELECT JSONExtract(payload, 'product_id', 'Int64') FROM events;
SELECT JSONExtractString(payload, 'event_name') FROM events;
SELECT JSONExtractFloat(payload, 'price') FROM events;

-- вложенные
SELECT JSONExtractString(payload, 'user', 'email') FROM events;

-- массивы
SELECT JSONExtract(payload, 'tags', 'Array(String)') FROM events;

-- существование
SELECT JSONHas(payload, 'product_id') FROM events;

-- все ключи
SELECT JSONExtractKeys(payload) FROM events;

Совет для ClickHouse: если JSON большой — добавляйте Object('json') тип (ClickHouse 22.4+) или парсите в MaterializedView.

BigQuery

-- стандарт
SELECT JSON_VALUE(payload, '$.product_id') FROM events;
SELECT JSON_EXTRACT(payload, '$.user.email') FROM events;

-- массивы через UNNEST
SELECT e.id, tag
FROM events e, UNNEST(JSON_EXTRACT_ARRAY(e.payload, '$.tags')) AS tag;

Snowflake

-- VARIANT тип (auto JSON)
SELECT payload:product_id FROM events;
SELECT payload:user:email FROM events;

-- кастинг
SELECT payload:product_id::INTEGER FROM events;

-- массивы
SELECT e.id, f.value::STRING AS tag
FROM events e, LATERAL FLATTEN(INPUT => e.payload:tags) f;

Типовые задачи

1. Event properties из payload

-- Postgres
SELECT
    payload ->> 'event_name' AS event,
    payload ->> 'user_id'    AS user_id,
    (payload ->> 'price')::NUMERIC AS price,
    COUNT(*)
FROM events
GROUP BY 1, 2, 3;

2. Все уникальные ключи JSON

-- Postgres
SELECT DISTINCT jsonb_object_keys(payload) FROM events;

3. Развернуть массив тегов

-- один пользователь — несколько тегов
SELECT user_id, tag
FROM users u, jsonb_array_elements_text(u.tags) AS tag;

4. Подсчёт по тегу

SELECT tag, COUNT(DISTINCT u.user_id)
FROM users u, jsonb_array_elements_text(u.tags) AS tag
GROUP BY tag;

Частые ошибки

Ошибка 1. Путаница -> и ->>

• -> возвращает JSON (JSONB)
• ->> возвращает TEXT

-- JSON — нельзя в WHERE = 'text'
payload -> 'name' = 'Alice'  -- ошибка

-- правильно
payload ->> 'name' = 'Alice'

Ошибка 2. Забыть cast к NUMERIC

-- сравнение строк, не чисел!
(payload ->> 'price') > '100'  -- '9' > '100' = TRUE (!!!)

-- правильно
(payload ->> 'price')::NUMERIC > 100

Ошибка 3. Полный скан без индекса

Если часто фильтруете payload ->> 'event_name' — нужен индекс.

Ошибка 4. JSON вместо нормальной таблицы

JSON удобен для гибкости. Но если структура стабильна — лучше вынести в отдельные колонки (быстрее, понятнее, дешевле).

Производительность

• JSONB на 100M записей — быстрый с индексами
• ->> быстрее, чем функции JSON_EXTRACT
• GIN-индекс критичен при фильтрации по ключам
• Cast к NUMERIC медленнее, чем обычная integer колонка

Связанные темы

• String функции SQL
• CAST SQL — шпаргалка
• Индексы SQL — шпаргалка

FAQ

JSON или JSONB в Postgres?

JSONB. Всегда. Для аналитики — без вариантов.

Как парсить массивы?

Postgres: jsonb_array_elements_text. MySQL: JSON_TABLE. BigQuery: UNNEST. Snowflake: FLATTEN.

Можно ли JOIN по JSON-полю?

Да: ON a.id = (b.payload ->> 'user_id')::INTEGER. Но медленно без индекса.

Как индексировать JSON?

GIN-индекс на JSONB-колонку или выражение-индекс на конкретное поле.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.