F.32. jsquery — язык запросов к типу данных jsonb #

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

JsQuery реализован посредством типа данных jsquery (подобно tsquery) и оператора @@ для применения запросов к jsonb.

F.32.1. Установка #

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

CREATE EXTENSION jsquery;

F.32.2. Язык запросов JSON #

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

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

  • x = "abc" — значение ключа "x" равно "abc";

  • $ @> [4, 5, "zzz"] — документ JSON представляет собой массив, содержащий значения 4, 5 и "zzz";

  • "abc xyz" >= 10 — значение ключа "abc xyz" больше или равно 10;

  • volume IS NUMERIC — ключ «volume» имеет числовой тип.

  • $ = true — весь документ JSON представляет собой просто значение true.

  • similar_ids.@# > 5 - similar_ids является массивом или объектом с числом элементов больше 5;

  • similar_product_ids.# = "0684824396" — массив similar_product_ids содержит строку "0684824396".

  • *.color = "red" — внутри документа существует объект, ключ "color" в котором имеет значение "red".

  • foo = * — в объекте существует ключ «foo».

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

  • # — любой элемент в массиве;

  • #N — N-ый индекс в массиве;

  • % — любой ключ объекта;

  • * — любой набор элементов массивов и ключей объектов;

  • @# — длина массива или объекта, может задаваться только в последнем компоненте пути;

  • $ — весь документ JSON в виде одного значения, может быть только единственным компонентом пути.

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

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

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

  • Оператор равенства: =;

  • Операторы численного сравнения: >, >=, <, <=;

  • Оператор IN для поиска в списке скалярных значений;

  • Операторы сравнения массивов: && (пересекается с), @> (содержит), <@ (содержится в).

  • Оператор фильтра: ~~. Принимая данные jsonb в качестве левого операнда и выражение jsquery в качестве правого, этот оператор ищет в данных jsonb элементы, удовлетворяющие условию, заданному в выражении jsquery, и возвращает массив таких элементов, если они находятся.

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

  • Оператор проверки существования: = *;

  • Операторы проверки типа: IS ARRAY, IS NUMERIC, IS OBJECT, IS STRING и IS BOOLEAN.

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

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

  • a = 1 AND (b = 2 OR c = 3) AND NOT d = 1

  • x.% = true OR x.# = true

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

  • #(a = 1 AND b = 2) — массив содержит элемент, в котором значение ключа a равно 2 и значение ключа b равно 2

  • %($ >= 10 AND $ <= 20) — объект содержит ключ со значением от 10 до 20

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

  • #: — каждый индекс в массиве;

  • %: — каждый ключ объекта;

  • *: — каждый элемент массива или ключ объекта.

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

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

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

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

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

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

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

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

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

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

  • numbers.#: IS NUMERIC — каждый элемент массива numbers является числом.

  • *:($ IS OBJECT OR $ IS BOOLEAN) — документ JSON представляет собой структуру вложенных объектов, на уровне листьев содержащую логические значения.

  • #:.%:($ >= 0 AND $ <= 1) — каждый элемент массива представляет собой объект, содержащий числовые значения от 0 до 1.

  • documents.#:.% = * — ключу documents соответствует массив из объектов, содержащих минимум один ключ.

  • %.#: ($ IS STRING) — объект JSON содержит минимум один массив со строками.

  • #.% = true — как минимум один элемент массива является объектом, содержащим минимум одно значение true.

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

  • # < 10 AND # > 20 — существует элемент меньше 10 и существует другой элемент больше 20.

  • #($ < 10 AND $ > 20) — существует элемент, который одновременно меньше 10 и больше 20 (что невозможно).

  • #($ >= 10 AND $ <= 20) — существует элемент между 10 и 20.

  • # >= 10 AND # <= 20 — существует элемент, больший или равный 10, и другой элемент, меньший или равный 20. Запрос может быть удовлетворён массивом без элементов между 10 и 20, например [0,30].

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

Операторы проверки типа и подстановочные знаки «каждый» полезны для проверки правильности схемы документа. Оператор сопоставления 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.

F.32.3. Индексы GIN #

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

  • jsonb_path_value_ops

  • jsonb_value_path_ops

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

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

  • "a".#."b"."xyz"

  • "a".#."c".true

  • "a".#.10

  • "d"."e".#.7

  • "d"."e".#.false

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

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

F.32.3.1. jsonb_path_value_ops #

Класс jsonb_path_value_ops представляет элемент в виде пары хеша пути и значения. Это иллюстрирует следующий псевдокод.

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

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

F.32.3.2. jsonb_value_path_ops #

Класс jsonb_value_path_ops представляет элемент в виде пары значения и фильтра Блума для пути.

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

При сравнении записей значение является старшей частью записи, а фильтр Блума для пути — младшей. Это определяет характеристики данного класса операторов. Так как значение находится в старшей части, мы можем выполнять очень эффективно только поиск точного значения. Поиск значений в интервале возможен, но для этого придётся отфильтровать все другие пути, в которых будут найдены подходящие значения. Фильтр Блума по элементам пути позволяет использовать индекс для условий, содержащих в путях % и *.

F.32.3.3. Оптимизация запросов #

Классы операторов JsQuery выполняют сложную оптимизацию запросов. Для разработчика или администратора очень ценно иметь возможность видеть результаты такой оптимизации. Но к сожалению, классы операторов не могут добавлять что-либо своё в вывод EXPLAIN. Именно поэтому JsQuery предоставляет следующие функции, позволяющие увидеть, как конкретный класс операторов оптимизирует заданный запрос.

  • gin_debug_query_path_value(jsquery) — для jsonb_path_value_ops

  • gin_debug_query_value_path(jsquery) — для jsonb_value_path_ops

Эти функции выдают текстовое представление дерева запросов, листьями в котором являются записи, искомые в индексе GIN. Следующие примеры показывают различия в результатах оптимизации запросов для разных классов операторов.

    # 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       +

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

  1. Равняется (x = c)

  2. В интервале (c1 < x < c2)

  3. Не равняется (x > c)

  4. Является (x is type)

  5. Любое (x = *)

Оптимизатор избегает использования индекса для менее избирательных условий, когда это возможно. Например, в запросе x = 1 AND y > 0 условие x = 1 считается более избирательным, чем y > 0. Поэтому для вычисления y > 0 индекс не используется.

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

Решения, принимаемые оптимизатором без учёта статистики, могут быть неточными. Поэтому в JsQuery поддерживаются подсказки. Комментарии /*-- index */ и /*-- noindex */, помещённые в условия, указывают оптимизатору соответственно использовать или не использовать индекс.

    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           +

F.32.4. Авторы #

  • Фёдор Сигаев , Postgres Professional, Москва, Россия

  • Александр Коротков , Postgres Professional, Москва, Россия

  • Олег Бартунов , Postgres Professional, Москва, Россия

F.32.5. Благодарности #

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