Запросы и индексы по JSONB

Как искать документы по содержимому, ускорять это GIN-индексом и аккуратно менять отдельное поле внутри JSONB.

Оператор содержания @> проверяет, что левый JSONB-документ содержит правый как поддокумент: ключи и значения справа должны присутствовать слева.

Доставать поля стрелками вы уже умеете. Но настоящая сила JSONB — в поиске: «найди все товары, где brand = Acme», «у кого в массиве тегов есть travel», «где вообще задан ключ oled». Для этого есть специальные операторы, а чтобы они не сканировали всю таблицу — индекс GIN.

Зачем это на практике

Фильтрация по JSONB нужна везде, где документ — это и данные, и критерий: каталог с фасетным поиском по атрибутам, выборка событий по полю внутри payload, поиск пользователей по гибким настройкам. Делать это через ->> в WHERE можно, но такой запрос почти всегда читает всю таблицу. Операторы содержания плюс GIN-индекс превращают полный скан в точечный поиск.

Оператор @>: документ содержит поддокумент

Самый частый способ фильтра. Слева — колонка, справа — «образец» того, что должно в ней быть. PostgreSQL проверяет вложенность по ключам и значениям.

-- товары бренда Acme
SELECT title FROM product
WHERE attrs @> '{"brand": "Acme"}';

-- товары, у которых в массиве tags есть "travel"
SELECT title FROM product
WHERE attrs @> '{"tags": ["travel"]}';

Второй пример важен: для массива @> означает «массив слева включает все элементы массива справа» — то есть это проверка вхождения. Образец {"tags": ["travel"]} матчит любой товар, в чьём tags есть travel, даже если там ещё десяток других тегов.

Операторы существования: ? ?| ?&

Иногда важно не значение, а сам факт наличия ключа (или элемента в массиве строк). Для этого — операторы со знаком вопроса:

-- у каких товаров вообще задан ключ author
SELECT title FROM product
WHERE attrs ? 'author';

Тонкость: ? и его варианты смотрят ключи объекта на верхнем уровне (а для массива — наличие строкового элемента). Они не спускаются вглубь сами по себе — для проверки во вложенном объекте сначала извлеките его через ->.

GIN-индекс по jsonb

Без индекса операторы @> и ? работают, но через полный скан таблицы. GIN (Generalized Inverted Index) строит обратный индекс по ключам и значениям документа и резко ускоряет такие фильтры.

-- индекс по умолчанию: поддерживает @>, ?, ?|, ?&
CREATE INDEX idx_product_attrs ON product USING GIN (attrs);

-- более компактный класс операторов: ускоряет ТОЛЬКО @>
CREATE INDEX idx_product_attrs_path
  ON product USING GIN (attrs jsonb_path_ops);

У GIN по JSONB два класса операторов. По умолчанию (jsonb_ops) индекс поддерживает и @>, и операторы существования ?/?|/?&. Класс jsonb_path_ops меньше по размеру и быстрее для @>, но не ускоряет операторы существования. Если фильтруете в основном через @> — jsonb_path_ops выгоднее.

jsonb_path_query и JSONPath

Для сложной навигации с условиями в PostgreSQL есть язык SQL/JSONPath и функция jsonb_path_query. Она применяет путь к документу и возвращает совпавшие значения. В пути $ — корень, ?() — фильтр, @ — текущий элемент.

-- все теги товара одной выборкой (распаковка массива)
SELECT jsonb_path_query(attrs, '$.tags[*]')
FROM product WHERE title = 'Ноутбук Pro';

-- вернуть размер экрана, только если он больше 13
SELECT jsonb_path_query(attrs, '$.screen.size ? (@ > 13)')
FROM product WHERE title = 'Ноутбук Pro';

Вывод:

"work"
"travel"
14

JSONPath удобен, когда нужно вытащить набор значений по сложному условию или пройти по массиву с фильтром — это выразительнее цепочек стрелок. Для простого «есть ли такой поддокумент» по-прежнему лучше @>: он индексируется GIN, а jsonb_path_query чаще считается уже на отобранных строках.

Обновление: jsonb_set

JSONB-документ неизменяем целиком: чтобы поменять одно поле, его пересобирают. Функция jsonb_set(документ, путь, новое_значение) возвращает копию с изменённой веткой — её и записывают обратно через UPDATE.

-- увеличить ram до 32
UPDATE product
SET attrs = jsonb_set(attrs, '{ram}', '32')
WHERE title = 'Ноутбук Pro';

-- добавить новый ключ warranty (4-й аргумент true = создать, если нет)
UPDATE product
SET attrs = jsonb_set(attrs, '{warranty}', '"24 мес"', true)
WHERE title = 'Ноутбук Pro';

-- удалить ключ оператором минус
UPDATE product
SET attrs = attrs - 'warranty'
WHERE title = 'Ноутбук Pro';

Новое значение для jsonb_set — это тоже JSONB-литерал: число '32', строка '"24 мес"' (в кавычках!), объект '{...}'. Удаление ключа делает оператор -, а удаление по глубокому пути — оператор #- с путём-массивом.

Как это работает под капотом

GIN раскладывает документ на множество «ключей индекса»: для jsonb_ops это и имена ключей, и значения; для jsonb_path_ops — хеши целых путей вида «ключ→значение». На каждый такой элемент GIN держит список идентификаторов строк (TID), где он встречается. Запрос attrs @> '{"brand":"Acme"}' превращается в поиск нужных элементов в индексе и пересечение их списков строк — отсюда скорость. Важное следствие: GIN отлично отвечает на «в каких строках есть вот это», но не ускоряет диапазонные сравнения вроде (attrs ->> 'ram')::int > 16 — для них нужен обычный B-tree-индекс по выражению.

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

• Фильтруют через ->> и удивляются скану. WHERE attrs ->> 'brand' = 'Acme' не использует обычный GIN. Либо пишите @>, либо стройте B-tree-индекс по выражению ((attrs ->> 'brand')).
• Путают @> и ? . @> проверяет наличие пары ключ-значение, ? — наличие только ключа. attrs ? 'brand' не проверяет, что бренд именно Acme.
• Забывают про путь в jsonb_set. Путь — это массив, '{ram}', а не 'ram'. И не забывайте записать результат обратно: jsonb_set сам по себе ничего не меняет, нужен UPDATE ... SET.
• Ждут от GIN ускорения диапазонов. GIN — про вхождение и существование. Сравнения «больше/меньше» по числу из JSON он не ускоряет.

Итоги

• @> ищет поддокумент (включая вхождение в массив), операторы ?/?|/?& — наличие ключей.
• GIN (attrs) ускоряет @> и операторы существования; jsonb_path_ops — компактнее и быстрее, но только для @>.
• jsonb_path_query и JSONPath дают навигацию с фильтрами ?(); для простого вхождения лучше индексируемый @>.
• Менять поле — через jsonb_set(doc, '{путь}', значение) внутри UPDATE; удалять — операторами - и #-.