Привет, Хабр! Это Антон Дятлов, инженер по защите информации в Selectel. В современных базах данных JSON — де-факто стандарт для хранения полуструктурированных сведений. PostgreSQL предлагает два специализированных типа для работы с ним: json и jsonb. Первый хранит точную текстовую копию документа со всеми пробелами и порядком ключей. Второй применяет оптимизированный бинарный формат. 

При вставке информации в поле типа jsonb система немедленно разбирает (парсит) текст, проверяет его корректность, удаляет дубликаты ключей и сохраняет результат в виде высокоэффективной древовидной структуры.

И вот мы сталкиваемся с фундаментальным компромиссом — между скоростью записи и чтения.

Вставка в jsonb происходит медленнее, чем в json — ведь требуются дополнительные вычисления. Зато расплачиваться временем приходится лишь однажды.

Обратная сторона: каждая последующая операция чтения или поиска по полю jsonb выполняется значительно оперативнее, так как не нужно каждый раз разбирать и перепроверять, что написано в текстовой строке. Более того, jsonb поддерживает индексацию, а это решающий фактор производительности на больших объемах информации.

Несмотря на все достоинства jsonb, стандартные операторы PostgreSQL — @>, ?, ?|, ?& — создают так называемый «разрыв выразительности». Они прекрасно подходят для простых задач — например, проверки наличия ключа верхнего уровня или вхождения одного JSON-документа в другой.

Однако как только требуется сформулировать замысловатое условие — скажем, найти товары, у которых в массиве характеристик есть «синий» цвет и цена больше 1 000, — выражение превращается в громоздкую нечитаемую конструкцию. Разработчикам приходится прибегать к неэффективным обходным путям. В ход идут вложенные подзапросы, «разворачиванию» массивов с помощью функции jsonb_array_elements с последующей фильтрацией, а то и прямой перебор. Такой код трудно схватывать на лету, поддерживать и, что хуже всего, он медленно работает.

Именно для того, чтобы закрыть этот разрыв, и было создано расширение jsquery. Оно предоставляет полноценный язык для формулирования выборок, который возвращает возможность писать декларативные, понятные и благодаря индексам очень быстрые инструкции для работы с jsonb-полями.

Что такое jsquery

jsquery — расширение для PostgreSQL для поиска по данным jsonb. Появляются два ключевых компонента: собственный тип данных jsquery и новый оператор сопоставления @@.

В отличие от набора отдельных функций, jsquery — целостный декларативный язык. Его главная цель — предоставить удобный и эффективный синтаксис для работы с вложенными объектами и массивами. Кроме того, он предоставляет расширенный набор операторов сравнения и проверок.

Наиболее важный аспект jsquery — его глубокая интеграция в ядро PostgreSQL. Когда вы составляете команду, строка, скажем items.#.price > 100, сначала преобразуется в специальное значение типа jsquery. Далее СУБД парсит получившуюся последовательность символов, проверяет синтаксис и, наконец, сохраняет ее в оптимизированном внутреннем представлении. Затем указанный объект сравнивается с полем jsonb через оператор @@.

Такая архитектура — собственный тип данных и выделенный оператор — критически важна для быстродействия. Планировщик PostgreSQL «знает» об инструкции @@ и понимает, что ее выполнение можно ускорить с помощью GIN-индекса. В этом кардинальное отличие jsquery от пользовательских функций (UDF, User Defined Functions). Для планировщика те часто выглядят как «черный ящик», что вынуждает того прибегать к полному сканированию таблицы.

С точки зрения своего синтаксиса, jsquery вдохновлен селекторами CSS и языком запросов MongoDB, что делает его интуитивно понятным для многих разработчиков. Запросы выглядят декларативно — вы описываете, что хотите найти, а не как это сделать по шагам. В результате код становится чище, короче и проще для восприятия.

Ключевые преимущества jsquery можно свести к следующим пунктам.

• Простой и мощный синтаксис, который позволяет легко описывать пути к глубоко вложенным элементам JSON-структуры с помощью точек и специальных символов.

• Расширенные операторы предоставляют богатый набор для сравнения (=, >, < и др..), а также проверки типов (IS NUMERIC, IS OBJECT и т. д.).

• Поддержка GIN-индексов полностью интегрируется с GIN-индексами PostgreSQL, что гарантирует высокую производительность запросов даже при работе с таблицами, содержащими миллионы JSON-документов.

• Декларативность и читаемость позволяет формулировать сложные и гибкие условия фильтрации в виде единого, логически завершенного выражения, избавляя от необходимости создавать громоздкие и трудноподдерживаемые SQL-конструкции.

Установка и базовая настройка

Системные требования

jsquery — отдельное расширение, поэтому его нужно установить и подключить. Минимальная версия PostgreSQL — 9.4, именно в ней впервые появился jsonb. 

Для сборки из исходного кода или установки через некоторые пакетные менеджеры могут потребоваться заголовочные файлы для разработки PostgreSQL, которые названы в стиле postgresql-server-dev-XX.

Установка

Проще всего получить jsquery из репозиториев, где он часто доступен в виде готового пакета. Например, для дистрибутивов на базе Debian, таких как Ubuntu или SelectOS, установка выполняется одной командой.

sudo apt install postgresql-17-jsquery

В примере выше число 17 может потребоваться заменить на актуальную версию PostgreSQL.

Активация в базе данных

После того как пакет расширения установлен на сервере, его необходимо активировать в каждой базе данных, где он будет использоваться. Делается это с помощью одной SQL-команды, выполненной от имени суперпользователя или владельца БД:

CREATE EXTENSION jsquery;

Команда выше создаст в текущей базе БД тип данных jsquery, оператор сопоставления @@, а также все необходимые функции и операторские классы для поддержки индексации.

Проверка

Чтобы убедиться, что расширение установлено и работает корректно, достаточно выполнить простой тестовый запрос. Он не обращается к каким-либо данным, а лишь подтверждает, что PostgreSQL распознает тип jsquery и оператор @@.

SELECT '{}'::jsonb @@ '{}'::jsquery;

Если команда выполняется без ошибок и возвращает булево значение — true или false, — значит, установка прошла успешно.

Синтаксис и команды

Чтобы эффективно применять jsquery, необходимо освоить его синтаксис, который сочетает в себе и простоту, и мощные возможности для навигации и фильтрации.

Навигация по документу

Основа любого запроса — это путь к нужному значению, который в jsquery строится интуитивно.

• Доступ к вложенному ключу осуществляется через точку. Например, путь profile.contact.email обратится к ключу email, который находится внутри объекта contact, вложенного в profile.

• Корень документа обозначается символом $. Этот символ позволяет применить оператор ко всему JSON-документу как к единому значению.

• Длина объекта или массива получается с помощью символа @# в конце пути. Например, tags@# > 3 найдет документы, где массив tags содержит более трех элементов.

Литералы и операторы

• Строковые литералы в запросе должны заключаться в двойные кавычки — например, status = "active".

• Числовые литералы указываются без кавычек — age > 30.

Массивы и объекты

Самая мощная часть jsquery — плейсхолдеры (wildcards), которые избавляют от необходимости писать процедурный код для обхода информационных структур.

• # — любой элемент массива. Условие items.#.price > 100 проверяет, есть ли в массиве items хотя бы один объект, у которого поле price больше 100. Этот плейсхолдер напрямую заменяет громоздкую конструкцию с jsonb_array_elements.

• #N — N-й элемент массива (индексация с нуля). Скажем, tags.#0 = "featured" проверит только первый элемент массива tags.

• % — любой ключ объекта. Выражение versions.% = "1.0" найдет документы, где у объекта versions есть любое поле со значением 1.0.

•  — произвольная вложенность. Этот плейсхолдер позволяет искать ключ на любом уровне документа. Условие .color = "red" найдет ключ color со значением «red» вне зависимости от того, насколько глубоко тот спрятан. Так удается заменить запутанные рекурсивные запросы.

• $ — весь JSON-документ как единое значение.

• @# — длина массива или количество ключей в объекте. Используется в конце пути: items@# > 5 — вернет true, если в массиве items больше пяти элементов.

Фильтрация значений

Есть обширный набор операций для проверки значений.

• Команды сравнения — =, !=, >, <, >=, <=.1 — работают как с числами, так и со строками (лексикографическое сопоставление).

• Команды проверки типов — IS NUMERIC, IS STRING, IS OBJECT, IS ARRAY, IS BOOLEAN, IS NULL — позволяют убедиться в корректности типа данных прямо в запросе. Например, status IS STRING гарантирует, что значение ключа status — строка. Заметьте, такие команды принято писать заглавными буквами.

Поиск подстроки

Для поиска фрагментов текста в значениях jsquery предлагает две инструкции, работающие на основе регулярных выражений. Важно правильно понимать их назначение:

• ~ — поиск с учетом регистра,

• ~* — поиск без учета регистра.

Например, если в поле description может содержаться слово «Express» в разном регистре, следует задействовать оператор ~*:

WHERE info @@ 'description ~* "express"';

Этот запрос найдет строки «Express», «express» и «EXPRESS». Если же нужен точный поиск, применяется команда ~.

Проверка наличия ключа

Чтобы проверить, что ключ просто существует в документе, независимо от его значения, применяется специальная конструкция с плейсхолдером : foo = .

Комбинирование условий

Отдельные выражения можно объединять с помощью логических команд AND, OR и NOT. Для управления порядком выполнения используются скобки, как и в обычном SQL.

Пример комплексного запроса — в одном из примеров ниже.

Приведем несколько распространенных запросов, закрепим полученные знания по синтаксису, а также порадуемся гибкости и выразительности jsquery на практике.

Найдем все документы, у которых поле status имеет строковое значение «active».

SELECT * FROM documents WHERE data @@ 'status = "active"';

Найдем всех пользователей, которые живут в Москве. Предположим, что информация о городе хранится по пути person.address.city. Обратите внимание, как jsquery позволяет элегантно «проваливаться» вглубь JSON-структуры с помощью точечной нотации.

SELECT * FROM users WHERE profile_data @@ 'person.address.city = "Moscow"';

Найдем пользователей с именем «Иван», которые старше 30 лет.

SELECT * FROM users WHERE data @@ '(name = "Иван" AND age > 30)';

Найдем пользователей с именем «Иван» старше 30 лет или тех, у кого есть тег «vip»:

SELECT * FROM users
WHERE data @@ '((name = "Иван" AND age > 30) OR tags.# = "vip")';

Выберем товары, у которых в массиве тегов tags есть значение «электроника». Обратите внимание: плейсхолдер # используется для итерации по всем элементам массива без необходимости его «разворачивания».

SELECT * FROM products WHERE info @@ 'tags.# = "электроника"';

Найдем заказы, в которых хотя бы один товар (items) стоит дороже тысячи.

SELECT * FROM orders WHERE data @@ 'items.#.price > 1000';

Выберем все документы, где поле status является строкой, независимо от его значения.

SELECT * FROM docs WHERE info @@ 'status IS STRING';

Найдем все документы, где ключ foo просто существует, независимо от его значения.

SELECT * FROM documents WHERE data @@ 'foo = *';

Индексация jsquery — ключ к высокому быстродействию

Декларативный синтаксис — это лишь половина успеха. Настоящая сила jsquery раскрывается при работе с GIN-индексами. Без индекса любой запрос к jsonb-полю на большой таблице приведет к полному последовательному сканированию (Seq Scan) — PostgreSQL будет вынужден прочитать каждую строку, чтобы проверить условие. На миллионах записей такая процедура недопустимо медлительна.

Продемонстрируем это на практике. Создадим тестовую таблицу и вставим 200 000 строк со случайными ценами на вещи:

CREATE TABLE products (
    id serial PRIMARY KEY,
    data jsonb
);

INSERT INTO products (data)
SELECT jsonb_build_object(
    'items', jsonb_agg(jsonb_build_object('price', (random() * 2000)::int))
)
FROM generate_series(1, 200000)
GROUP BY generate_series;

Запрос без индекса: базовый уровень эффективности

Выполним запрос на поиск товаров, у которых есть хотя бы один предмет дороже 1500, и посмотрим на план выполнения с помощью EXPLAIN ANALYZE. Обратите внимание на корректный синтаксис jsquery:

EXPLAIN ANALYZE
SELECT count(*)
FROM products
WHERE data @@ 'items.#.price > 1500';

Результат покажет, что планировщик выбрал Seq Scan, а время выполнения будет измеряться сотнями миллисекунд или даже секундами на реальной системе.

Aggregate  (cost=12943.33..12943.34 rows=1 width=8) (actual time=245.873..245.874 rows=1 loops=1)

   ->  Parallel Seq Scan on products  (cost=0.00..12940.83 rows=1000 width=0) (actual time=136.438..245.615 rows=95163 loops=3)

         Filter: (data @@ 'items.#.price > 1500'::jsquery)

         Rows Removed by Filter: 38171

 Planning Time: 0.116 ms

 Execution Time: 245.924 ms

Создание GIN-индекса

Теперь создадим GIN-индекс на столбце data:

CREATE INDEX idx_products_data_gin ON products USING GIN (data);
ANALYZE products;

Запрос с индексом — скачок продуктивности

Выполним тот же самый запрос еще раз:

EXPLAIN ANALYZE
SELECT count(*)
FROM products
WHERE data @@ 'items.#.price > 1500';

План выполнения кардинально изменится. Вместо медленного Seq Scan мы увидим комбинацию Bitmap Index Scan и Bitmap Heap Scan.

Aggregate  (cost=148.83..148.84 rows=1 width=8) (actual time=34.483..34.484 rows=1 loops=1)

   ->  Bitmap Heap Scan on products  (cost=124.75..146.33 rows=1000 width=0) (actual time=16.351..34.223 rows=95163 loops=1)

         Recheck Cond: (data @@ 'items.#.price > 1500'::jsquery)

         Heap Blocks: exact=4483

         ->  Bitmap Index Scan on idx_products_data_gin  (cost=0.00..124.50 rows=1000 width=0) (actual time=14.931..14.931 rows=95163 loops=1)

               Index Cond: (data @@ 'items.#.price > 1500'::jsquery)

 Planning Time: 0.157 ms

 Execution Time: 34.532 ms

Двухэтапный процесс работает чрезвычайно эффективно. Сначала Bitmap Index Scan быстро просматривает GIN-индекс. Далее, чтобы найти все документы, удовлетворяющие условию, он создает в памяти битовую карту страниц таблицы, где находятся эти строки. Затем Bitmap Heap Scan точечно считывает с диска только нужные страницы.

Такой метод на порядки быстрее полного перебора таблицы, что и демонстрирует многократное уменьшение времени отклика.

Сравнение jsquery с альтернативами

Чтобы понять место jsquery в экосистеме PostgreSQL, полезно сравнить его со встроенными механизмами и стандартом SQL/JSON (jsonpath).

Встроенные инструменты остаются отличным решением для простых задач. Для замысловатых условий отбора jsquery предлагает более чистую и мощную грамматику. К примеру, jsonpath, появившийся в PostgreSQL 12 — мощная альтернатива, соответствующая стандарту SQL. Однако jsquery сильнее — в простоте применения индексов и доступности на более ранних версиях PostgreSQL.

Сценарии применения и лучшие практики

jsquery наиболее полезен в проектах, которые интенсивно работают с jsonb и требуют частой и замысловатой фильтрации сведений.

Когда выбирать jsquery

• Аналитика по логам и событиям. Когда нужно фильтровать JSON-логи по множеству вложенных параметров.

• Отбор позиций в каталогах товаров. Идеально для интернет-магазинов, где у товаров есть сложные наборы атрибутов в JSON.

• Работа с любыми полуструктурированными данными. Хранение пользовательских профилей, настроек, документов, где структура может варьироваться.

• Проекты на PostgreSQL до версии 12. В версиях 9.4−11 jsquery — безальтернативный выбор для производительных комплексных выборок из jsonb.

Лучшие практики

Индекс GIN — не опция, а требование. Всегда создавайте GIN-индекс на jsonb-столбцах, которые активно фильтруются. Без него быстродействие будет неприемлемым.

Доверяй, но проверяй — (EXPLAIN ANALYZE). Всегда анализируйте план выполнения запроса, чтобы убедиться, что PostgreSQL использует индекс (Bitmap Scan), а не полный перебор (Seq Scan). Иногда структура команды может помешать оптимизатору — тогда ее придется переписать.

Выбирайте правильный инструмент для задачи. Не прибегайте к jsquery там, где достаточно нативного оператора. Для простого извлечения значения data -> 'name' будет быстрее и проще. Задействуйте jsquery в чем он силен — для комплексных условий фильтрации.

Тестируйте на реальных данных. Эффективность jsquery и jsonpath (для PostgreSQL 12+) может зависеть от структуры данных и запросов. Проводите измерения на своем окружении, чтобы сделать осознанный выбор.

Заключение

jsquery — это зрелое, мощное и проверенное временем расширение, которое значительно обогащает возможности PostgreSQL при работе с jsonb.

Несмотря на появление стандарта SQL/JSON и jsonpath, jsquery не теряет своей актуальности. Его ценность — в уникальном сочетании простой декларативной синтаксической структуры, мощных плейсхолдерах и, что самое главное, предсказуемо высокой продуктивности благодаря тесной интеграции с GIN-индексами.

Для проектов, работающих на версиях PostgreSQL до 12-й, он остается незаменимым решением. Однако и в более новых версиях он часто выигрывает за счет легкости и прозрачности. Ведь jsquery — не просто расширение, а механизм, который возвращает разработчику контроль над комплексными выборками из jsonb, делая их чище, быстрее и проще в поддержке.

Если в БД хранятся значительные объемы неструктурированных сведений в jsonb, то jsquery определенно заслуживает внимания.