Как работать с JSON в PostgreSQL: jsonb, индексы и запросы

Когда настройки пользователя или тело вебхука лежат в колонке с типом text, PostgreSQL не видит в них структуры. Запросы по такому значению читают всю таблицу строка за строкой. JSON в PostgreSQL решает эту проблему двумя типами, json и jsonb — выбор между ними определяет, будут ли запросы быстрыми. Сначала посмотрим, какой тип подойдёт для ваших данных. Затем разберём операторы, которыми из документа достают нужные значения, и на примере таблицы заказов выясним, какие индексы действительно сокращают время выборки, а какие только занимают место на диске.

Чем json отличается от jsonb

В PostgreSQL есть два типа для документов. Тип json сохраняет исходный текст целиком, поэтому пробелы, порядок ключей и повторяющиеся ключи остаются на своих местах. Тип jsonb (буква b означает binary, двоичный формат) разбирает документ и хранит готовое двоичное представление. Форматирование теряется, дубликаты ключей схлопываются до последнего значения, и ключи выстраиваются сначала по длине, затем по содержимому.

SELECT '{"b":1,"a":2,"a":3}'::json AS as_json,
       '{"b":1,"a":2,"a":3}'::jsonb AS as_jsonb;
-- as_json                | as_jsonb
-- {"b":1,"a":2,"a":3} | {"a": 3, "b": 1}

Тип json вернул строку без изменений. Тип jsonb оставил один ключ a со значением 3 и поставил его перед b.

В сравнении json vs jsonb практический итог такой: документ в jsonb записывается чуть медленнее, потому что при вставке его разбирают. Зато читается заметно быстрее, так как текст не приходится разбирать при каждом запросе, и такой документ можно индексировать. Поэтому в новом коде при хранении JSON в PostgreSQL берём jsonb. Тип json остаётся для случаев, когда документ нужно вернуть ровно в том виде, в каком он пришёл, например, тело вебхука, которое сохраняют для проверки подписи.

Когда данные стоит держать в jsonb, а когда развести по колонкам

Хранение JSON в PostgreSQL удобно, пока документ нужен как единое целое. На jsonb в этом случае указывают четыре признака:

Значение лучше вынести в обычную колонку, если по нему постоянно фильтруют и сортируют. То же касается случая, когда оно входит во внешний ключ или в ограничение уникальности, и когда нужна строгая типизация с проверкой на уровне базы. Уникальный email, к примеру, проще держать отдельной колонкой. Как типы колонок ведут себя в этих ситуациях, подробно описано в материале про типы данных в SQL.

Лучше крайностей работает гибридная схема. Около пяти часто используемых значений, скажем, status и email, лежат в обычных колонках, а всё остальное остаётся в jsonb.

Операторы jsonb: как доставать значения -> и ->>

Запросы к JSON в PostgreSQL строятся на операторах jsonb. Каждый оператор принимает документ и возвращает кусок документа, текст или логическое значение. В таблице все примеры даны на заказе с id = 1, а для оператора ? добавлен ещё заказ с id = 2.

Оператор	Что возвращает	Пример
->	jsonb: ключ или элемент массива	data->’status’ → “paid”
->>	text: то же значение, но как текст	data->>’status’ → paid
#>	jsonb: значение по пути	data #> ‘{items,0}’ → {“qty”: 1, “sku”: “KB-01”, “price”: 4500}
#>>	text: значение по пути	data #>> ‘{items,0,price}’ → 4500
@>	boolean: входит ли справа документ в левый	data @> ‘{“status”:”paid”}’ → true
?	boolean: есть ли такой ключ верхнего уровня	data ? ‘promo’ → false (у id = 2 → true)

Операторы -> и ->> берут один ключ или элемент массива по номеру, нумерация начинается с нуля. Операторы #> и #>> принимают сразу весь путь. Одинарная стрелка и #> оставляют результат в jsonb, двойная стрелка и #>> отдают text. Разница заметна при сравнении: после -> в руках остаётся jsonb, и сравнить его с обычным числом не получится.

-- слева jsonb, справа число: так нельзя
SELECT id FROM orders WHERE data->'items'->0->'price' = 4500;
-- ERROR: operator does not exist: jsonb = integer

-- достаём текст и приводим к числу
SELECT id FROM orders WHERE (data->'items'->0->>'price')::int = 4500;
-- id = 1

Первый запрос падает, потому что слева стоит jsonb, а справа integer. Во втором последняя стрелка двойная, поэтому приходит текст, и его можно привести к int.

Вложенный ключ достаётся двумя способами. Цепочка стрелок удлиняется с каждым уровнем, а путь в #>> записывается одним литералом.

-- один и тот же вложенный ключ двумя способами
SELECT data->'items'->0->>'sku'  AS via_arrows,
       data #>> '{items,0,sku}'  AS via_path
FROM orders WHERE id = 1;
-- via_arrows | via_path
-- KB-01      | KB-01

Как искать по jsonb: @>, ? и jsonpath

Самый привычный вариант для запросов к JSON в PostgreSQL выглядит так: достать значение оператором ->> и сравнить его с константой. Q2 задаёт тот же вопрос иначе. Оператор вхождения @> (containment) проверяет, лежит ли документ справа внутри документа из колонки data.

-- Q1: достаём текст и сравниваем
SELECT id FROM orders WHERE data->>'status' = 'paid';
-- id = 1

-- Q2: проверяем вхождение
SELECT id FROM orders WHERE data @> '{"status":"paid"}';
-- id = 1

На эталонных заказах результат у обоих запросов одинаков, но смысл разный. Оператор @> проверяет вхождение, а не равенство. Поэтому условие data @> ‘{“status”:”paid”}’ найдёт и заказ, где кроме status есть ещё двадцать ключей. Главное, чтобы указанная пара была на месте.

Если нужно узнать лишь о наличии ключа, подойдёт оператор ?. Условие data ? ‘promo’ вернёт заказ 2, а data ? ‘deleted_at’ вернёт заказ 3. Значение ключа при этом не проверяется, поэтому такой запрос найдёт и ключ со значением null.

Третий способ поиска по jsonb работает через выражение jsonpath и оператор @?. Знак @ в выражении означает текущий элемент. Проверка после знака вопроса работает как условие.

-- Q3: то же условие на языке jsonpath
SELECT id FROM orders WHERE data @? '$.status ? (@ == "paid")';
-- id = 1

Выражение jsonpath пригодится там, где одним вхождением не обойтись. Например, при сравнении чисел внутри массива объектов items. Звёздочка в квадратных скобках просматривает все его элементы, и строка подходит, если условию отвечает хотя бы один из них.

-- Q4: есть ли позиция дороже 1000
SELECT id FROM orders WHERE data @? '$.items[*] ? (@.price > 1000)';
-- id = 1, 3

Заказ 1 попал в выборку из-за клавиатуры за 4500, а заказ 3 из-за монитора за 18900. Заказ 2 с ценой 350 не прошёл.

Что касается скорости, GIN-индекс умеет ускорять @>, ? и jsonpath с равенством. Сравнение через ->> в него не попадает.

Как разложить jsonb в таблицу: JSON_TABLE и jsonb_to_recordset

Массив items удобно превратить в обычные строки: одна позиция заказа в одной строке, а sku, price и qty лежат в отдельных колонках. В таком виде данные проще объединять с другими таблицами и считать по ним суммы. Функция JSON_TABLE появилась в PostgreSQL 17 (релиз 26 сентября 2024 года) и работает в 18-й ветке. Раньше массивы разворачивали через jsonb_array_elements и jsonb_to_recordset.

-- до PostgreSQL 17
SELECT o.id, i.sku, i.price, i.qty
FROM orders o,
     jsonb_to_recordset(o.data->'items') AS i(sku text, price numeric, qty int);

Тот же результат даёт JSON_TABLE:

-- PostgreSQL 17 и новее
SELECT o.id, jt.sku, jt.price, jt.qty
FROM orders o,
     JSON_TABLE(o.data, '$.items[*]' COLUMNS (
       sku   text    PATH '$.sku',
       price numeric PATH '$.price',
       qty   int     PATH '$.qty')) AS jt;
-- id | sku   | price | qty
--  1 | KB-01 | 4500  | 1
--  1 | MS-07 | 900   | 2
--  2 | CB-12 | 350   | 3
--  3 | MN-24 | 18900 | 1

Оба запроса возвращают четыре строки для заказов 1, 2 и 3. Запрос с jsonb_to_recordset короче: путь к массиву передан аргументом, типы колонок указаны в скобках после AS. В JSON_TABLE у колонок есть собственный путь — запрос длиннее, зато сразу видно, откуда берётся значение.

Как обновить jsonb: jsonb_set, || и удаление ключей

Обновление JSON в PostgreSQL строится на операторах и функциях, которые возвращают новый документ. Все примеры ниже берут заказ id = 2 в эталонном состоянии. Результат показан через SELECT, а в UPDATE то же выражение ставится после SET data =.

Оператор || склеивает два документа — новые ключи добавляются, совпавшие перезаписываются. Оператор – удаляет ключ по имени. Функция jsonb_set правит значение по пути, например {items,0,qty}. Последним аргументом она может принять false, тогда отсутствующий ключ не создаётся.

SELECT data || '{"status":"paid"}' AS data FROM orders WHERE id = 2;
-- status стал paid, остальное без изменений

SELECT data - 'promo' AS data FROM orders WHERE id = 2;
-- ключ promo исчез

SELECT jsonb_set(data, '{items,0,qty}', '5') AS data FROM orders WHERE id = 2;
-- qty первой позиции равен 5

Если новое значение равно NULL, jsonb_set вернёт NULL на весь документ. Колонка data объявлена как NOT NULL, поэтому такой UPDATE завершится ошибкой. Функция jsonb_set_lax ведёт себя мягче. Четвёртый аргумент разрешает NULL, пятый говорит, что с ним делать.

SELECT jsonb_set_lax(data, '{promo}', NULL, true, 'delete_key') AS data
FROM orders WHERE id = 2;
-- ключ promo удалён

-- PostgreSQL 18
SELECT jsonb_strip_nulls('[1,null,{"a":null}]', true);  -- [1, {}]
SELECT ('null'::jsonb)::int;                            -- NULL (раньше была ошибка)

В PostgreSQL 18 у jsonb_strip_nulls появился параметр strip_in_arrays, который убирает null и внутри массивов. Приведение jsonb null к числу или тексту теперь даёт SQL NULL.

Учтите, что при любой правке PostgreSQL записывает новую версию строки вместе с документом целиком. Если документ занимает мегабайт, замена одного ключа обойдётся почти как перезапись всего документа, ведь большие значения хранятся отдельно.

Индексы для jsonb: GIN, jsonb_path_ops и B-tree

Индекс ускоряет выборку, только если планировщик запросов находит в условии то, что этот индекс умеет искать. Для jsonb есть четыре варианта.

Тип индекса	Какие операторы ускоряет	Когда брать
GIN jsonb_ops	@>, ?, ?|, ?&, @?, @@	Нужна проверка наличия ключа или разные виды запросов
GIN jsonb_path_ops	@>, @?, @@	Запросы только на вхождение и по jsonpath, а размер индекса важен
B-tree по выражению	=, <, >, BETWEEN, сортировка	Запросы идут к одному ключу, например к email
Частичный индекс	Операторы B-tree, если в запросе то же условие WHERE	Фильтр всегда одинаковый, а нужна лишь часть строк

GIN-индекс PostgreSQL хранит отдельные ключи и значения внутри них и может находить строки по содержимому. Класс операторов jsonb_path_ops хранит только хеши путей со значениями, поэтому индекс получается меньше.

-- GIN с классом по умолчанию
CREATE INDEX orders_data_gin  ON orders USING gin (data);
-- GIN только под вхождение и jsonpath
CREATE INDEX orders_data_path ON orders USING gin (data jsonb_path_ops);

В замере на 100 тыс строк jsonb_ops занял 18 МБ, а jsonb_path_ops 6 МБ. Запрос на вхождение выполнился за 3,2 мс и за 2,4 мс соответственно.

-- индекс по выражению для одного ключа
CREATE INDEX orders_email_idx ON orders ((data->>'email'));
-- частичный индекс: фильтр всегда один и тот же
CREATE INDEX orders_deleted_idx ON orders ((data->>'status')) WHERE data ? 'deleted_at';

-- оба запроса содержат то же выражение, поэтому индексы доступны
SELECT id FROM orders WHERE data->>'email' = 'anna@example.com';
SELECT id FROM orders WHERE data ? 'deleted_at' AND data->>'status' = 'cancelled';

Частичный индекс сработает, только если условие data ? ‘deleted_at’ есть и в запросе.

Как убедиться, что индекс действительно используется

Планировщик запросов может обойти индекс стороной, поэтому его работу проверяют командой EXPLAIN ANALYZE. Она выполняет запрос и показывает план с реальным временем. Опция BUFFERS добавляет число прочитанных страниц.

Проверять стоит на большой таблице. Индекс становится заметен от сотен тысяч строк, поэтому берём 500 000 заказов. Оплаченных среди них 2%, то есть 10 000 строк. Запрос Q2 получается селективным: индекс отсекает почти всё лишнее.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders WHERE data @> '{"status":"paid"}';

-- Bitmap Heap Scan on orders  (cost=17.40..1650.12 rows=500 width=8) (actual time=3.9..9.8 rows=10000 loops=1)
--   Recheck Cond: (data @> '{"status": "paid"}'::jsonb)
--   Heap Blocks: exact=6900
--   Buffers: shared hit=7050
--   ->  Bitmap Index Scan on orders_data_path  (cost=0.00..17.28 rows=500 width=0) (actual time=2.9..2.9 rows=10000 loops=1)
--         Index Cond: (data @> '{"status": "paid"}'::jsonb)
--         Buffers: shared hit=150
-- Execution Time: 10.5 ms

Обратите внимание на четыре момента:

Подробнее о чтении планов.

Ошибки, из-за которых jsonb тормозит

Частые вопросы про JSON в PostgreSQL

Что выбрать для новой таблицы, json или jsonb?

В большинстве случаев подойдёт jsonb. Он хранит документ в разобранном виде, поэтому значения читаются быстрее, а индексы строятся только по нему. Тип json сохраняет текст как есть, вместе с пробелами, порядком ключей и повторами. Это нужно редко, например, когда исходную запись надо вернуть без изменения.

Можно ли поставить индекс на один ключ внутри jsonb?

Да, для этого подходит индекс по выражению, как orders_email_idx для адреса почты. Он ускоряет сравнение и сортировку по этому ключу. Планировщик запросов применит его, только если в запросе записано точно такое же выражение.

Почему запрос к jsonb игнорирует GIN-индекс?

Причин обычно три. Первая: условие написано через оператор извлечения текста, а GIN-индекс понимает вхождение, существование ключа и jsonpath. Вторая: таблица маленькая, и планировщик считает, что прочитать её целиком быстрее. Третья: класс операторов не подходит к запросу.

Какой максимальный размер jsonb-документа?

Значение любой колонки в PostgreSQL не может превышать 1 ГБ, но у jsonb есть и свой внутренний предел, около 255 МБ. Большой документ приходится читать и переписывать целиком при каждой правке, так что компактный документ почти всегда удобнее.

Стоит ли заменять PostgreSQL на MongoDB, если документов много?

Одного только количества документов для такого решения мало. Если хранение документов уже устроено через JSON в PostgreSQL, а запросы укладываются в нужное время, поводов для переезда нет. Выбор базы зависит от задач: нужны ли связи между данными, транзакции, привычен ли инструмент команде.

---

Хранение JSON в PostgreSQL через jsonb подходит там, где набор ключей в документах меняется от заказа к заказу. Платить за гибкость приходится размером строки. Ещё индекс нужно подбирать под конкретный вид запроса, один индекс на все случаи не работает. Поэтому разумно начать с гибридной схемы: стабильные значения, например id и status, вынести в обычные колонки, а непостоянные оставить в data. Поверх data стоит поставить GIN-индекс с классом операторов jsonb_path_ops — он обслуживает поиск по оператору вхождения @> и получается заметно меньше индекса по умолчанию.

Советуем дальше почитать

SQL-запросы это не страшно! Вся база с примерами — команды DDL, DML и TCL на PostgreSQL 14+, от SELECT и JOIN до подзапросов и ошибок новичков.

Реляционная база данных: что это такое, принципы работы и основные понятия — ключи, связи и нормализация, то есть всё, что jsonb позволяет обойти, и цена этого обхода.

Что такое NoSQL — модели «ключ-значение», колоночная, графовая и документная и их отличия от реляционных баз.

ClickHouse для начинающих: зачем нужен и чем отличается от PostgreSQL — когда аналитику пора уносить из Postgres в колоночную базу.

SQL-инъекции: механика атак, типы и методы защиты с примерами — как ввод пользователя становится чужим SQL-кодом и почему параметризованные запросы обязательны, в том числе для фильтров по jsonb.