JSON и JSONB в PostgreSQL — операторы, индексы, запросы

JSONB — бинарный формат хранения JSON в PostgreSQL с поддержкой GIN-индексов. Позволяет хранить произвольные атрибуты без изменения схемы и делать эффективные запросы к вложенным данным. Разбираем операторы, индексы и типичные паттерны использования.

JSON vs JSONB — что выбрать

Создание таблицы и вставка JSONB

-- Создать таблицу с JSONB колонкой:
CREATE TABLE events (
  id         SERIAL PRIMARY KEY,
  event_type TEXT NOT NULL,
  metadata   JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Вставить JSON данные:
INSERT INTO events (event_type, metadata) VALUES
  ('page_view', '{"url": "/home", "user_id": 1, "device": "mobile"}'),
  ('purchase', '{"product_id": 42, "amount": 99.99, "currency": "RUB"}'),
  ('error', '{"code": 500, "message": "Internal error", "path": "/api/users"}');

-- Проверить что это JSONB:
SELECT pg_typeof(metadata) FROM events LIMIT 1;
-- jsonb

Операторы JSONB

-- -> получить поле как JSONB (сохраняет тип):
SELECT metadata->'user_id' FROM events WHERE event_type = 'page_view';
-- 1  (тип: jsonb integer)

-- ->> получить поле как TEXT:
SELECT metadata->>'url' FROM events WHERE event_type = 'page_view';
-- /home  (тип: text)

-- Вложенные поля:
SELECT metadata->'address'->>'city' FROM users_data;

-- #> путь как массив ключей → JSONB:
SELECT metadata#>'{address,city}' FROM users_data;

-- #>> путь как массив ключей → TEXT:
SELECT metadata#>>'{address,city}' FROM users_data;

-- @> содержит ли (очень полезно с GIN-индексом!):
SELECT * FROM events WHERE metadata @> '{"event_type": "page_view"}';
SELECT * FROM events WHERE metadata @> '{"device": "mobile"}';

-- <@ содержится ли в:
SELECT * FROM events WHERE '{"code": 500}' <@ metadata;

-- ? ключ существует:
SELECT * FROM events WHERE metadata ? 'user_id';

-- ?| хотя бы один ключ существует:
SELECT * FROM events WHERE metadata ?| ARRAY['user_id', 'product_id'];

-- ?& все ключи существуют:
SELECT * FROM events WHERE metadata ?& ARRAY['url', 'user_id'];

-- || объединить JSONB:
SELECT '{"a": 1}'::JSONB || '{"b": 2}'::JSONB;
-- {"a": 1, "b": 2}

-- - удалить ключ:
SELECT '{"a": 1, "b": 2}'::JSONB - 'b';
-- {"a": 1}

Обновление JSONB полей

-- jsonb_set — установить значение по пути:
UPDATE events
SET metadata = jsonb_set(metadata, '{user_id}', '99')
WHERE id = 1;

-- Установить вложенное значение:
UPDATE users_data
SET metadata = jsonb_set(metadata, '{address, city}', '"Москва"')
WHERE id = 1;

-- Добавить новое поле через ||:
UPDATE events
SET metadata = metadata || '{"processed": true}'
WHERE event_type = 'purchase';

-- Удалить поле:
UPDATE events
SET metadata = metadata - 'user_id'
WHERE event_type = 'error';

-- Удалить вложенное поле:
UPDATE users_data
SET metadata = metadata #- '{address, zip}'
WHERE id = 1;

-- Инкрементировать числовое поле:
UPDATE events
SET metadata = jsonb_set(
  metadata,
  '{view_count}',
  (COALESCE(metadata->>'view_count', '0')::int + 1)::text::jsonb
)
WHERE id = 1;

GIN-индекс для JSONB

-- Без индекса @> делает Seq Scan:
EXPLAIN SELECT * FROM events WHERE metadata @> '{"device": "mobile"}';
-- Seq Scan on events  (cost=0.00..250.00 rows=100 width=...)

-- Создать GIN-индекс на всю JSONB-колонку:
CREATE INDEX idx_events_metadata ON events USING gin(metadata);

-- С индексом — Bitmap Index Scan:
EXPLAIN SELECT * FROM events WHERE metadata @> '{"device": "mobile"}';
-- Bitmap Index Scan on idx_events_metadata

-- GIN-индекс ускоряет операторы: @>, <@, ?, ?|, ?&

-- jsonb_path_ops — компактный GIN только для @> (меньше размер):
CREATE INDEX idx_events_meta_path ON events USING gin(metadata jsonb_path_ops);
-- Поддерживает только @>, но быстрее и меньше чем обычный GIN

-- Частичный индекс (только для нужных событий):
CREATE INDEX idx_purchases_meta ON events USING gin(metadata)
WHERE event_type = 'purchase';

-- Индекс на конкретное поле JSONB (B-tree, для =, >, <):
CREATE INDEX idx_events_user_id ON events((metadata->>'user_id'));
-- Ускоряет: WHERE metadata->>'user_id' = '42'

Функции для работы с JSONB

-- jsonb_each — развернуть объект в строки ключ-значение:
SELECT key, value FROM jsonb_each('{"a": 1, "b": "two", "c": true}'::JSONB);
-- a | 1
-- b | "two"
-- c | true

-- jsonb_object_keys — список ключей:
SELECT jsonb_object_keys(metadata) FROM events LIMIT 1;

-- jsonb_array_elements — развернуть массив:
SELECT jsonb_array_elements('["a", "b", "c"]'::JSONB);

-- to_jsonb — конвертировать запись в JSONB:
SELECT to_jsonb(u.*) FROM users u WHERE id = 1;

-- jsonb_build_object — создать JSONB из пар ключ-значение:
SELECT jsonb_build_object('id', id, 'email', email) FROM users;

-- jsonb_agg — агрегация в JSONB-массив:
SELECT jsonb_agg(email) FROM users WHERE active = TRUE;

-- jsonb_strip_nulls — удалить null значения:
SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": "text"}'::JSONB);
-- {"a": 1, "c": "text"}

Часто задаваемые вопросы

Когда использовать JSONB вместо отдельных колонок?

JSONB оправдан когда: набор атрибутов различается для разных записей (EAV-паттерн), атрибуты часто добавляются без изменения схемы, данные изначально приходят в JSON формате (API, логи, события). Отдельные колонки лучше для: фиксированной схемы, полей используемых в JOIN и WHERE (индексы B-tree эффективнее), полей требующих CHECK-ограничений, высокочастотных точечных запросов. Золотое правило: основные бизнес-поля — колонки, метаданные и расширения — JSONB.

Почему запрос к JSONB медленный даже с GIN-индексом?

Несколько причин: 1) Используете ->> вместо @> — GIN-индекс работает только с @>, <@, ?, ?|, ?&. Запрос WHERE metadata->>'field' = 'value' не использует GIN. 2) Нужен B-tree индекс на конкретное поле: CREATE INDEX ON events((metadata->>'user_id')). 3) GIN-индекс создан с jsonb_path_ops — поддерживает только @>. 4) Используете LIKE внутри JSONB — GIN не поддерживает. Проверяйте план через EXPLAIN (ANALYZE, BUFFERS).

Как обновить вложенное поле в JSONB массиве?

Для массивов в JSONB используйте jsonb_set с числовым индексом: jsonb_set(metadata, '{tags, 0}', '"новый_тег"') — обновляет первый элемент массива. Для добавления в конец массива: metadata || '{"tags": ["новый_тег"]}' не работает если tags уже есть — используйте jsonb_set(metadata, '{tags}', metadata->'tags' || '["новый_тег"]'). Для сложных трансформаций массивов часто проще: извлечь в Python/приложение, изменить, записать обратно.