Программный комплект Postgres Pro Standard включает jsquery в качестве дополнительного модуля (contrib). Установив необходимые компоненты Postgres Pro Standard, создайте расширение jsquery следующим образом:

CREATE EXTENSION jsquery;

Расширение JsQuery содержит тип jsquery, представляющий весь запрос JSON в виде одного значения (как и tsquery для полнотекстового поиска). В этом запросе задаётся выражение, обращающееся к документам JSON.

Простое выражение записывается в виде путь бинарный_оператор значение или путь унарный_оператор. См. следующие примеры.

Путь выбирает набор значений JSON для проверки, используя заданные операторы. В простейшем случае путь — это просто имя ключа. В общем случае путь задают имена ключей и подстановочные знаки, соединённые точками. Подстановочные знаки в пути могут быть следующими:

Результат выражения — true, когда оператор выдаёт true как минимум для одного значения, выбранного по заданному пути.

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

Поддерживаются следующие бинарные операторы:

Поддерживаются следующие унарные операторы:

Выражения могут быть сложными. Сложное выражение образуется набором выражений, соединённых логическими операторами (AND, OR и NOT) и сгруппированных скобками.

Примеры сложных выражений приведены ниже.

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

Путь также может содержать следующие подстановочные знаки, обозначающие «каждый»:

Рассмотрим следующий пример.

%.#:($ >= 0 AND $ <= 1)

Это условие можно прочитать как: существует минимум один ключ, для которого значением является массив чисел от 0 до 1.

Мы можем переписать этот пример в следующей форме с дополнительными скобками.

%(#:($ >= 0 AND $ <= 1))

Первый подстановочный знак % проверяет, что выражение в скобках истинно как минимум для одного значения в объекте. Второй подстановочный знак #: проверяет, что значение — массив, все элементы которого удовлетворяют условию в скобках.

Мы можем переписать этот пример без подстановочного знака #: следующим образом.

%(NOT #(NOT ($ >= 0 AND $ <= 1)) AND $ IS ARRAY)

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

Ниже приведены примеры использования путей.

Использование операторов пути и скобок требует дополнительных разъяснений. Когда операторы пути используются неоднократно, они могут ссылаться на различные значения, тогда как, используя скобки и оператор $, вы будете многократно обращаться к одному значению. Взгляните на следующие примеры.

Те же правила применяются при поиске внутри объектов и ветвей.

Операторы проверки типа и подстановочные знаки «каждый» полезны для проверки правильности схемы документа. Оператор сопоставления JsQuery @@ является постоянным (IMMUTABLE) и может применяться в ограничениях CHECK. Взгляните на следующий пример.

CREATE TABLE js (
    id serial,
    data jsonb,
    CHECK (data @@ '
        name IS STRING AND
        similar_ids.#: IS NUMERIC AND
        points.#:(x IS NUMERIC AND y IS NUMERIC)'::jsquery));

В этом примере ограничение-проверка контролирует данные в колонке data типа jsonb: значением ключа name должна быть строка, значением ключа similar_ids — массив чисел, значением ключа points — массив объектов, содержащих числовые значения для ключей x и y.

Другие примеры можно найти в нашей презентации для pgconf.eu.

Расширение JsQuery содержит два класса операторов для GIN, которые реализуют различные варианты оптимизации запросов.

В этих классах GIN документы jsonb раскладываются на элементы. Каждый элемент сопоставляется с определённым значением и своим путём. Различаются эти классы операторов тем, как в них представлены элементы, как они сравниваются и используются для оптимизации поиска.

Например, документ jsonb {"a": [{"b": "xyz", "c": true}, 10], "d": {"e": [7, false]}} будет разложен на следующие элементы:

Так как JsQuery не поддерживает поиск в определённом элементе массива, мы рассматриваем все элементы массива как одинаковые. Таким образом, все элементы массива помечаются одинаковым знаком # в пути.

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

(hash(элемент_пути_1.элемент_пути_2. ... .элемент_пути_n); значение)

(значение; bloom(элемент_пути_1) | bloom(элемент_пути_2) | ... | bloom(элемент_пути_n))

    # SELECT gin_debug_query_path_value('x = 1 AND (*.y = 1 OR y = 2)');
     gin_debug_query_path_value
    ----------------------------
     x = 1 , entry 0           +

    # SELECT gin_debug_query_value_path('x = 1 AND (*.y = 1 OR y = 2)');
     gin_debug_query_value_path
    ----------------------------
     AND                       +
       x = 1 , entry 0         +
       OR                      +
         *.y = 1 , entry 1     +
         y = 2 , entry 2       +

    # SELECT gin_debug_query_path_value('x = 1 AND y > 0');
     gin_debug_query_path_value
    ----------------------------
     x = 1 , entry 0           +

    SELECT gin_debug_query_path_value('x = 1 AND y /*-- index */ > 0');
     gin_debug_query_path_value
    ----------------------------
     AND                       +
       x = 1 , entry 0         +
       y > 0 , entry 1         +

    SELECT gin_debug_query_path_value('x /*-- noindex */ = 1 AND y > 0');
     gin_debug_query_path_value
     ----------------------------
      y > 0 , entry 0           +

Мы выражаем признательность спонсору разработки, компании Wargaming.net.