16 апреля 2026 г.

JSONB в PostgreSQL: работа с полуструктурированными данными

JSON vs JSONB

В PostgreSQL есть два типа для JSON: JSON и JSONB. Разница важна.

JSON хранит данные как текст. При каждом обращении парсится заново. Сохраняет whitespace и порядок ключей точно как введено.

JSONB (binary) — декомпилированная форма. Индексирует внутренние структуры, поддерживает операторы и индексы. Не сохраняет whitespace и порядок ключей.

Для аналитики всегда JSONB. JSON разве что для сервисов, где важно сохранить исходный формат (например, webhook-пейлоады для audit).

Зачем это аналитику

Event-based аналитика часто сталкивается с полуструктурированными данными. Клиентские события имеют общие поля (user_id, event_time) и variable properties, которые зависят от типа события. Это удобно хранить как JSONB:

CREATE TABLE events (
    event_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    event_time TIMESTAMPTZ,
    event_name VARCHAR(50),
    properties JSONB
);

-- Purchase event
INSERT INTO events VALUES (..., 'purchase', '{"amount": 1500, "category": "electronics", "items": 3}');

-- View event
INSERT INTO events VALUES (..., 'page_view', '{"url": "/product/123", "referrer": "google.com"}');

Без JSONB пришлось бы либо делать огромную таблицу с сотнями колонок (большинство NULL), либо EAV-pattern (отдельная таблица для key-value пар, медленно на чтении).

Операторы для извлечения

Самые частые операторы.

-> возвращает поле как JSONB:

SELECT properties -> 'amount' FROM events WHERE event_name = 'purchase';
-- Результат: "1500" (как JSONB)

->> возвращает поле как text:

SELECT properties ->> 'category' FROM events WHERE event_name = 'purchase';
-- Результат: 'electronics' (как text)

#> и #>> — для вложенных путей:

SELECT properties #>> '{address, city}' FROM users;
-- Работает, если properties = {"address": {"city": "Moscow"}}

Для чисел и фильтров используется ->> с cast:

SELECT * FROM events
WHERE event_name = 'purchase'
  AND (properties ->> 'amount')::numeric > 1000;

Операторы проверки

? — существует ли ключ:

SELECT * FROM events WHERE properties ? 'amount';
-- Все события, где есть ключ amount

?| — существует ли хотя бы один из ключей:

SELECT * FROM events WHERE properties ?| ARRAY['amount', 'price'];

?& — существуют ли все ключи:

SELECT * FROM events WHERE properties ?& ARRAY['user_id', 'session_id'];

@> — содержит ли JSONB (contains):

SELECT * FROM events
WHERE properties @> '{"category": "electronics"}';
-- Все события с category = electronics

Оператор @> особенно ценен, потому что может использовать GIN-индекс. Об этом ниже.

Агрегация на JSONB

Pattern: считать что-то по событиям с фильтрацией по properties.

-- Revenue по категориям из JSONB
SELECT
    properties ->> 'category' AS category,
    SUM((properties ->> 'amount')::numeric) AS revenue,
    COUNT(*) AS orders
FROM events
WHERE event_name = 'purchase'
GROUP BY 1
ORDER BY revenue DESC;

Это работает, но медленно без индекса. Для production-аналитики нужны индексы.

Индексы на JSONB

PostgreSQL поддерживает несколько типов индексов на JSONB.

GIN (Generalized Inverted iNdex) — полнофункциональный, но медленно строится и занимает место:

CREATE INDEX idx_events_properties_gin ON events USING GIN (properties);

Этот индекс ускоряет @> (contains), ?, ?|, ?& операторы. Поиск по «все события с category = electronics» будет быстрым.

GIN с jsonb_path_ops — более компактный, но только для @>:

CREATE INDEX idx_events_properties_gin_path ON events USING GIN (properties jsonb_path_ops);

Занимает меньше места, быстрее на @>, но не поддерживает ? операторы.

BTree на extract expression — для поиска по конкретному полю:

CREATE INDEX idx_events_category ON events ((properties ->> 'category'));

Это обычный btree-индекс на результате выражения. Ускоряет WHERE properties ->> 'category' = 'electronics'.

Для аналитических запросов обычно полезны expression indexes на часто используемых полях. GIN хорош, когда нужно искать по произвольным ключам.

Разбираться с такими тонкостями — важно для senior-уровня. В тренажёре Карьерник есть задачи на проектирование схем для event-based аналитики, где эти вопросы всплывают постоянно.

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

Для UPDATE-ов есть функции.

jsonb_set — устанавливает значение по пути:

UPDATE events
SET properties = jsonb_set(properties, '{refunded}', 'TRUE')
WHERE event_id = 42;

Второй аргумент — путь массивом, третий — новое значение (тоже JSONB).

|| — конкатенация, мерджит два JSONB:

UPDATE events
SET properties = properties || '{"processed_at": "2026-04-16"}'::jsonb
WHERE ...;

- — удаляет ключ:

UPDATE events SET properties = properties - 'tmp_field';

jsonb_each и jsonb_array_elements

Функции для «разворачивания» JSONB в строки.

jsonb_each — разворачивает объект в строки (key, value):

SELECT key, value
FROM events, jsonb_each(properties)
WHERE event_id = 42;

jsonb_array_elements — разворачивает массив в строки:

-- Если properties = {"items": [{"name": "A"}, {"name": "B"}]}
SELECT event_id, item ->> 'name' AS item_name
FROM events, jsonb_array_elements(properties -> 'items') AS item;

Это мощно для событий с вложенными массивами. Классический случай — список товаров в заказе.

Пример: конверсионная воронка из JSONB events

Реальная задача: посчитать конверсию по воронке из event-based данных, где шаги определяются через event_name и свойства.

WITH funnel_steps AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_name = 'landing_view' THEN 1 ELSE 0 END) AS step1,
        MAX(CASE WHEN event_name = 'add_to_cart'
                   AND properties ?& ARRAY['product_id', 'price']
                 THEN 1 ELSE 0 END) AS step2,
        MAX(CASE WHEN event_name = 'checkout_start' THEN 1 ELSE 0 END) AS step3,
        MAX(CASE WHEN event_name = 'purchase'
                   AND (properties ->> 'amount')::numeric > 0
                 THEN 1 ELSE 0 END) AS step4
    FROM events
    WHERE event_time >= '2026-04-01'
    GROUP BY user_id
)
SELECT
    SUM(step1) AS landing,
    SUM(step2) AS cart,
    SUM(step3) AS checkout,
    SUM(step4) AS purchase,
    ROUND(SUM(step4) * 100.0 / NULLIF(SUM(step1), 0), 2) AS overall_conv_pct
FROM funnel_steps;

Проверки на наличие ключей через ?& гарантируют, что мы считаем только валидные события.

Типичные ловушки

properties ->> 'amount' всегда возвращает text. Чтобы использовать в математике — cast: ::numeric или ::integer.

NULL vs missing. Если ключа нет, properties ->> 'amount' вернёт NULL. Если ключ есть со значением null JSON, — тоже NULL в text-виде. Не различить напрямую.

Индексы — зверь. GIN-индекс на JSONB может быть в несколько раз больше самой таблицы. Следите за размером.

Плохая производительность без индекса. Seq scan через миллион JSONB-ов даже по простому фильтру — медленно.

Смешение типов. JSON позволяет хранить число, строку, bool, объект, массив в одной колонке. Если структура непредсказуема, получите сюрпризы при извлечении.

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

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

Если данные часто меняются — UPDATE-ы на JSONB медленнее, чем на обычных колонках.

Если основной use-case — full-text search по значениям — лучше выделенное FTS-решение (tsvector, Elasticsearch).

JSONB — хорош для event-based данных с variable properties. Для классических business-данных с fixed schema — нет.

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

FAQ

JSON или JSONB?

JSONB всегда, кроме случаев, когда нужно точное сохранение формата (webhook logs, audit).

Можно ли делать JOIN на поле внутри JSONB?

Можно: JOIN users ON users.user_id = (events.properties ->> 'user_id')::bigint. Но для performance лучше извлечь в отдельную колонку.

Как часто менять структуру JSONB?

Технически в любой момент — no schema enforcement. Практически — документируйте изменения, иначе старые events с одной структурой и новые с другой создадут хаос.

ClickHouse JSON vs PostgreSQL JSONB?

ClickHouse имеет свой JSON тип и функции. В целом похоже, но синтаксис и возможности отличаются. Для event-analytics в ClickHouse часто лучше колонки-массивы, чем JSON.

Тренироваться в Telegram