Тип JSONPath

Евгения Кузина

Тип JSONPath реализует поддержку языка путей SQL/JSON в PostgreSQL для эффективных запросов данных типа JSONB. JSONPath является двоичным представлением выражения пути SQL/JSON, которое указывает элементы для извлечения и дальнейших операций.

JSONPath использует следующий синтаксис соглашения JavaScript:

  • Точка (.) применяется для доступа к членам иерархии JSON.

  • Квадратные скобки ([]) используются для доступа к массиву.

  • Нумерация элементов массивов SQL/JSON начинается с 0, в отличие от обычных массивов PostgreSQL, которые начинают нумерацию элементов с 1.

Вы можете заключить выражение пути SQL/JSON в одинарные кавычки, чтобы включить его в запрос SQL. Чтобы использовать в выражении одинарную кавычку, продублируйте её. Например: 'Dianne''s horse'.

Некоторые формы выражений пути требуют использования строковых литералов. Строковые литералы должны быть заключены в двойные кавычки. Они также могут содержать заэкранированные обратной косой чертой символы. Чтобы использовать двойную кавычку, заэкранируйте её: \". Чтобы добавить обратную косую черту, продублируйте её (\\). Вы также можете использовать другие специальные последовательности с обратной косой чертой: \b, \f, \n, \r, \t, \v для управляющих символов ASCII; \xNN, \uNNNN и \u{N…​} для символов Unicode, определяемых двумя шестнадцатеричными цифрами, четырьмя шестнадцатеричными цифрами и от одной до шести шестнадцатеричных цифр соответственно.

Выражение пути может состоять из следующих элементов:

  • Переменные пути:

    • $ — текущий контекст элемента. Выражение с $ задает область JSON, которая подлежит обработке или используется в фильтре. Пример: $.items.

    • @ — текущий контекст в выражении фильтра (результат вычисления пути в выражении фильтра). Пример: $.items[*] ? (@.qty > 2).

    • $varname — именованная переменная. Её значение может быть задано в параметре vars. Пример: '$[*] ? (@ > $x)',vars ⇒ '{"x": 2}'.

  • Выражение фильтра, которое начинается со знака вопроса (?) и содержит условие в скобках:

    ? (condition)

    Выражение фильтра работает аналогично выражению WHERE.

  • Операторы и методы JSONPath перечисленные в следующем разделе: SQL/JSON Path Operators And Methods.

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

  • Операторы доступа, перечисленные в таблице ниже.

Оператор доступа Описание Пример

.ключ

."$имя_переменной"

Оператор доступа к ноде по заданному ключу. Если имя ключа совпадает с именем какой-либо переменной, начинающимся с $, или не соответствует действующим в JavaScript требованиям к идентификаторам, оно должно заключаться в двойные кавычки

$.items

.*

Оператор доступа, который возвращает значения всех нод, находящихся на верхнем уровне объекта

$.items.*

.**

Рекурсивный оператор доступа, который проходит по всем уровням иерархии JSON текущего объекта и возвращает все значения нод, вне зависимости от их уровня вложенности

$.**

.**{уровень}

.**{начальный_уровень to конечный_уровень}

Этот оператор выбирает только указанные уровни иерархии JSON. Уровни вложенности задаются целыми числами, при этом нулевой уровень соответствует текущему объекту. Для обращения к самому нижнему уровню вложенности можно использовать ключевое слово last

$.**{2}

$.**{1 to 3}

[индекс]

[начальный_индекс to конечный_индекс]

Оператор обращения к элементам массива. Селектор может задаваться в двух формах: индекс или начальный_индекс to конечный_индекс. Первая форма выбирает единственный элемент по индексу. Вторая форма выбирает срез массива по двум индексам, включающий крайние элементы, соответствущие значениям начальный_индекс и конечный_индекс

Задаваемый индекс может быть целочисленным значением или выражением, возвращающим единственное число, которое автоматически приводится к целому. Индекс 0 соответствует первому элементу массива. Также в качестве индекса принимается ключевое слово last, обозначающее индекс последнего элемента массива

$.a[1]

$.a[3 to 5]

[*]

Оператор обращения к элементам массива, возвращающий все элементы массива

$.items[*]

Вы можете использовать синтаксис JSONPath для создания более простых запросов, чем позволяет синтаксис SQL.

Функции JSONPath

Функции JSONPath работают с данными в формате JSONB. Все эти функции имеют необязательные аргументы vars и silent. vars позволяет передавать переменные в выражение JSONPath. silent определяет, подавляется ли обработка ошибок. Эти необязательные аргументы подробно описаны ниже.

Создадим таблицы book_orders и demand для тестовых примеров:

CREATE TABLE book_orders (
    id serial NOT NULL PRIMARY KEY,
    content jsonb NOT NULL
);

INSERT INTO book_orders (content)
VALUES('{ "supplier": "Jacob Johnson", "items": {"book": "Hyperion","qty": 3, "price": 21}}'),
        ('{ "supplier": "Adam Brown", "items": {"book": "War and Peace","qty": 2, "price": 26}}'),
        ('{ "supplier": "Andrew Nelson", "items": {"book": "1984","qty": 4, "price": 31}}');

CREATE TABLE demand(name text, max_price int);

INSERT INTO demand VALUES ('Jane', 25), ('Alex', 27 ), ('Max', 35);

jsonb_path_exists

jsonb_path_exists ( <значение> jsonb, <путь> jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

Функция определяет, возвращает ли путь какой-либо элемент для значения JSON.

Примеры:

SELECT jsonb_path_exists('{"a": 1}', '$.a');
-- возвращает true

SELECT jsonb_path_exists('{"a": 1}', '$.b');
-- возвращает false

SELECT jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
-- возвращает true

Следующий запрос определяет, содержат ли данные JSON, хранящиеся в столбце content таблицы book_orders, строки со значением Hyperion:

SELECT jsonb_path_exists(content, '$.** ? (@ == "Hyperion")')
FROM book_orders ORDER by id;

$.** проходит по иерархии значений JSON из поля content и возвращает все значения элементов, независимо от их уровня вложенности. @ ссылается на текущее значение элемента в выражении фильтра.

Результат:

 jsonb_path_exists
-------------------
 true
 false
 false

jsonb_path_match

jsonb_path_match ( <значение> jsonb, <путь> jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

Функция возвращает результат проверки предиката путь для значения JSON. Функция учитывает только первый элемент результата. Если результат не boolean, jsonb_path_match возвращает null.

Примеры:

SELECT jsonb_path_match('{"a": 1}', '$.a == 1');
-- возвращает true

SELECT jsonb_path_match('{"a": 1}', '$.a >= 2');
-- возвращает false

SELECT jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2, "max":4}');
-- возвращает true

Следующий запрос также определяет, содержит ли столбец content таблицы book_orders строки со значением Hyperion:

SELECT jsonb_path_match(content, 'exists($.** ? (@ == "Hyperion"))')
FROM book_orders ORDER by id;

Результат:

 jsonb_path_match
-------------------
 true
 false
 false

jsonb_path_query

jsonb_path_query ( <значение> jsonb, <путь> jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

Функция возвращает все элементы значения JSON, которые соответствуют значению путь.

Примеры:

SELECT jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)');
-- возвращает 3, 4, 5

SELECT jsonb_path_query('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)');
-- возвращает 0 строк

Следующий запрос возвращает items, где поле qty больше 2:

SELECT jsonb_path_query(content, '$.items[*] ? (@.qty > 2)')
FROM book_orders;

Результат:

              jsonb_path_query
---------------------------------------------
 {"qty": 3, "book": "Hyperion", "price": 21}
 {"qty": 4, "book": "1984", "price": 31}

jsonb_path_query_array

jsonb_path_query_array ( <значение> jsonb, <путь> jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

Функция возвращает все элементы значения JSON, которые соответствуют значению путь в виде массива JSON.

Примеры:

SELECT jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)');
-- возвращает [3, 4, 5]

SELECT jsonb_path_query_array('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)');
-- возвращает []

jsonb_path_query_first

jsonb_path_query_first ( <значение> jsonb, <путь> jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

Функция возвращает первый элемент значения JSON, который соответствует значению путь. jsonb_path_query_first возвращает null, если соответствующее значение не найдено.

Примеры:

SELECT jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 2)');
-- возвращает 3

SELECT jsonb_path_query_first('{"a": [1,2,3,4,5]}', '$.a[*] ? (@ > 5)');
-- возвращает null

Функции JSONPath c постфиксом _tz

В PostgreSQL есть следующие функции с постфиксом _tz: jsonb_path_exists_tz, jsonb_path_match_tz,jsonb_path_query_tz, jsonb_path_query_array_tz, и jsonb_path_query_first_tz. Эти функции работают как описанные выше аналогичные функции без _tz, но поддерживают сравнение значений даты/времени, для которых требуется преобразование с учетом часового пояса.

В приведенном ниже примере требуется преобразовать дату без указания времени 2022-08-02 в дату/время с учетом часового пояса. Результат будет зависеть от текущей настройки TimeZone. Из-за этой зависимости PostgreSQL помечает такие функции как stable, что означает, что эти функции нельзя использовать в индексах.

Примеры:

SELECT jsonb_path_exists_tz('["2022-08-01 12:00:00 -05"]', '$[*] ? (@.datetime() < "2022-08-02".datetime())');
-- возвращает false

SELECT jsonb_path_exists_tz('["2022-08-01 12:00:00"]', '$[*] ? (@.datetime() < "2022-08-02".datetime())');
-- возвращает true

Аргумент vars

Аргумент vars позволяет передавать переменные в выражение JSONPath. Значение vars должно быть объектом JSON, а его поля должны содержать именованные значения для замены в выражении JSONPath.

Выполним запрос с аргументом vars, чтобы найти книги с ценой ниже, чем max_price, которая указана для Alex в таблице demand:

SELECT jsonb_path_query(content, '$.items[*]? (@.price < $max_price)',
    vars => jsonb_build_object('max_price', demand.max_price))
    FROM book_orders, demand WHERE name = 'Alex';

Аргумент vars передает результат запроса, который определяет max_price, указанную для Alex, в функцию jsonb_path_query.

Результат:

                 jsonb_path_query
--------------------------------------------------
 {"qty": 3, "book": "Hyperion", "price": 21}
 {"qty": 2, "book": "War and Peace", "price": 26}

Аргумент silent

silent подавляет обработку ошибок. Выполним следующий запрос, в котором модификатор strict меняет режим обработки ошибок:

SELECT jsonb_path_query('[]', 'strict $.a');

Произойдёт ошибка:

ERROR: jsonpath member accessor can only be applied to an object

Установим значение true для аргумента silent:

SELECT jsonb_path_query('[]', 'strict $.a', silent => true);

Ошибка не отображается. Результат:

 jsonb_path_query
------------------
(0 rows)

Операторы JSONPath

Оператор совпадения @@

Оператор @@ возвращает результат проверки предиката JSONPath для указанного значения JSON. PostgreSQL учитывает только первый элемент результата. Если результат не boolean, @@ возвращает null.

Следующий запрос определяет, содержит ли столбец content таблицы book_orders строки со значением Hyperion:

SELECT content @@ '$.** == "Hyperion"' AS hyperion FROM book_orders;

Результат:

hyperion
----------
true
null
null

Оператор проверки существования @?

Оператор @? определяет, возвращает ли выражение JSONPath какой-либо элемент для указанного значения JSON.

Следующий запрос использует @?, чтобы отобрать книги со значением price больше 25.

SELECT jsonb_path_query(content, '$.items.book')
FROM book_orders WHERE content @? '$.items[*] ? (@.price > 25)';

Результат:

 jsonb_path_query
------------------
 "War and Peace"
 "1984"

Режимы strict и lax

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

  • lax — алгоритм JSONPath неявно адаптирует запрошенные данные к указанному значению JSONPath. Оставшиеся структурные ошибки подавляются и преобразуются в пустые последовательности SQL/JSON. Этот режим используется по умолчанию.

  • strict — если возникла ошибка структуры, происходит ошибка.

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

Чтобы посмотреть, как работают эти режимы, поищем ключ b в значении JSON, которое не содержит этот ключ. В режиме lax результатом будет false:

SELECT jsonb '{"a":1}' @? 'lax $.b ? (@ > 1)';

Результат:

 ?column?
----------
 false

В режиме strict возвращается null:

SELECT jsonb '{"a":1}' @? 'strict $.b ? (@ > 1)';

Результат:

 ?column?
----------
null

В режиме lax массив со сложной иерархией [1,3,[5,7,9]] обрабатывается как [1,3,5,7,9]:

SELECT jsonb '[1,3,[5,7,9]]' @? 'lax $[*] ? (@ == 9)';

Результат — true.

В режиме strict запрос вернет false:

SELECT jsonb '[1,3,[5,7,9]]' @? 'strict $[*] ? (@ == 9)';

Заменим @ на @[*], чтобы получить результат true в режиме strict:

SELECT jsonb '[1,3,[5,7,9]]' @? 'strict $[*] ? (@[*] == 9)';

Для получения дополнительной информации о режимах обратитесь к статье Strict and lax modes.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней