15 апреля 2026 г.

Как работать с JSON в SQL

Когда нужно работать с JSON

  • Events с custom properties: {"source": "email", "campaign_id": 42}.
  • User settings в одной колонке JSON.
  • API responses в сырых логах.
  • Nested данные из NoSQL источников.

JSON в реляционной БД — компромисс между гибкостью (схема not required) и удобством запросов.

PostgreSQL: json vs jsonb

json

Хранит как текст. Медленные запросы, но сохраняет оригинальное форматирование.

jsonb

Бинарный формат. Быстрее, поддерживает индексы. Для аналитики почти всегда jsonb.

CREATE TABLE events (
    id SERIAL,
    data jsonb  -- не json
);

Базовые операторы

Извлечение поля

-- ->  возвращает jsonb
SELECT data -> 'source' FROM events;
-- "email"

-- ->> возвращает text
SELECT data ->> 'source' FROM events;
-- email (без кавычек)

Вложенные поля

-- {"user": {"id": 42, "name": "Иван"}}

-- Через цепочку
SELECT data -> 'user' -> 'name' FROM events;

-- Через путь
SELECT data #>> '{user,name}' FROM events;

Типы для filter / comparison

Текст

SELECT * FROM events WHERE data ->> 'source' = 'email';

Число

SELECT * FROM events WHERE (data ->> 'amount')::numeric > 1000;
-- Важно: cast text → numeric

Булевое

SELECT * FROM events WHERE (data ->> 'is_active')::boolean = TRUE;

Дата

SELECT * FROM events WHERE (data ->> 'TIMESTAMP')::TIMESTAMP >= '2026-01-01';

Проверки

Есть ли ключ

SELECT * FROM events WHERE data ? 'user_id';

Есть ли любой из

SELECT * FROM events WHERE data ?| ARRAY['source', 'campaign_id'];

Есть ли все

SELECT * FROM events WHERE data ?& ARRAY['source', 'campaign_id'];

Contains

-- Содержит ли JSON в поле
SELECT * FROM events WHERE data @> '{"source": "email"}';

-- Плюс: может использовать GIN-индекс

Если хочется сразу закрепить тему на практике — открой тренажёр в Telegram. 10 минут в день — и синтаксис в пальцах.

Массивы в JSON

Извлечение элемента

-- {"tags": ["premium", "annual"]}
SELECT data -> 'tags' -> 0 FROM subscriptions;

Размер массива

SELECT jsonb_array_length(data -> 'tags') FROM subscriptions;

Unnest / jsonb_array_elements

-- Разворачивает массив в строки
SELECT id, jsonb_array_elements_text(data -> 'tags') AS tag
FROM subscriptions;

Агрегаты из JSON

Популярные значения source

SELECT data ->> 'source' AS source, COUNT(*)
FROM events
GROUP BY 1
ORDER BY 2 DESC;

Средний amount

SELECT AVG((data ->> 'amount')::numeric)
FROM events
WHERE data ? 'amount';

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

Добавить / обновить поле

-- jsonb_set
UPDATE events
SET data = jsonb_set(data, '{status}', '"paid"')
WHERE id = 1;

Удалить поле

UPDATE events SET data = data - 'temp_field';

Merge объектов

-- Через оператор ||
UPDATE events SET data = data || '{"new_field": "value"}';

Индексы для JSON

GIN (generalised inverted)

Универсальный индекс для jsonb:

CREATE INDEX idx_events_data ON events USING GIN (data);

-- Работает для:
-- WHERE data @> '{"source": "email"}'
-- WHERE data ? 'campaign_id'

Индекс на конкретное поле

Если часто ищем по одному полю:

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

-- Теперь быстро:
-- WHERE data ->> 'source' = 'email'

Сравнение с разными СУБД

MySQL

SELECT JSON_EXTRACT(data, '$.source') FROM events;
-- или ->
SELECT data->'$.source' FROM events;

-- Без jsonb — только json, медленнее

ClickHouse

SELECT JSONExtractString(data, 'source') FROM events;

Плюс ClickHouse — nested типы данных для массивов.

SQL Server

SELECT JSON_VALUE(data, '$.source') FROM events;

Чтобы не только читать теорию, но и решать реальные задачи — загляните в бот Карьерника. Там по каждой теме подборка вопросов с разборами.

Когда НЕ использовать JSON

1. Когда структура фиксированная

Если у всех events поля одинаковые — делайте нормальные колонки. Будет быстрее и type-safe.

2. Для частых фильтров

JSON-запросы обычно медленнее, чем по обычным столбцам (даже с индексом).

3. Для агрегатов по множеству полей

SUM((data->>'x')::numeric) — нужно cast + extraction. Медленно на больших данных.

Best practice: Extract важные поля

Если часто используете data ->> 'source' — создайте отдельную колонку:

ALTER TABLE events ADD COLUMN source TEXT;
UPDATE events SET source = data ->> 'source';
CREATE INDEX ON events (source);

Теперь WHERE source = 'email' намного быстрее.

Пример полного анализа

-- Конверсия по источникам из JSON
SELECT
    data ->> 'source' AS source,
    COUNT(*) AS visits,
    COUNT(*) FILTER (WHERE data @> '{"converted": TRUE}') AS conversions,
    ROUND(
        COUNT(*) FILTER (WHERE data @> '{"converted": TRUE}') * 100.0 / COUNT(*),
        2
    ) AS conv_pct
FROM events
WHERE data ? 'source'
  AND created_at >= '2026-04-01'
GROUP BY source
ORDER BY visits DESC;

Читайте также

FAQ

jsonb или TEXT для хранения JSON?

jsonb — всегда. TEXT требует парсинга при каждом запросе.

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

Да: ON a.id = (b.data ->> 'related_id')::int. Но нужен индекс, иначе медленно.

NoSQL или JSON в PostgreSQL?

Для аналитики — JSON в Postgres достаточно. NoSQL (Mongo) — если 90% данных unstructured.

Как ускорить работу с JSON?

  1. jsonb (не json).
  2. GIN-индекс.
  3. Extract важные поля в отдельные столбцы.
  4. Для production — обычные таблицы везде, где возможно.
Тренироваться в Telegram