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 должен принимать императивные решения, выбирая условия, обрабатываемые с индексом. При этом он руководствуется предположением, что некоторые типы условий являются менее избирательными, чем другие. Таким образом, оптимизатор разделяет условия на следующие категории избирательности (перечисленные по убыванию избирательности).
Равняется (x = c)
В интервале (c1 < x < c2)
Не равняется (x > c)
Является (x is type)
Любое (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. Авторы #
Фёдор Сигаев
<teodor@sigaev.ru>
, Postgres Professional, Москва, РоссияАлександр Коротков
<aekorotkov@gmail.com>
, Postgres Professional, Москва, РоссияОлег Бартунов
<oleg@sai.msu.su>
, Postgres Professional, Москва, Россия
F.32.5. Благодарности #
Мы выражаем признательность спонсору разработки, компании Wargaming.net.