9.16. Функции и операторы JSON #
В этом разделе описываются:
функции и операторы, предназначенные для работы с данными JSON
язык путей SQL/JSON
функции запросов SQL/JSON
PostgreSQL реализует модель данных SQL/JSON, обеспечивая встроенную поддержку типов данных JSON в среде SQL. В этой модели данные представляются последовательностями элементов. Каждый элемент может содержать скалярные значения SQL, дополнительно определённое в SQL/JSON значение null и составные структуры данных, образуемые объектами и массивами JSON. Данная модель по сути формализует модель данных, описанную в спецификации JSON RFC 7159.
Поддержка SQL/JSON позволяет обрабатывать данные JSON наряду с обычными данными SQL, используя при этом транзакции, например:
Загружать данные JSON в базу и сохранять их в обычных столбцах SQL в виде символьных или двоичных строк.
Создавать объекты и массивы JSON из реляционных данных.
Обращаться к данным JSON, используя функции запросов SQL/JSON и выражения языка путей SQL/JSON.
Чтобы узнать больше о стандарте SQL/JSON, обратитесь к [sqltr-19075-6]. Типы JSON, поддерживаемые в PostgreSQL, описаны в Разделе 8.14.
9.16.1. Обработка и создание данных JSON #
В Таблице 9.45 показаны имеющиеся операторы для работы с данными JSON (см. Раздел 8.14). Кроме них для типа jsonb
, но не для json
, определены обычные операторы сравнения, показанные в Таблице 9.1. Они следуют правилам упорядочивания для операций B-дерева, описанным в Подразделе 8.14.4. В Разделе 9.21 вы также можете узнать об агрегатной функции json_agg
, которая агрегирует значения записи в виде JSON, и агрегатной функции json_object_agg
, агрегирующей пары значений в объект JSON, а также их аналогах для jsonb
, функциях jsonb_agg
и jsonb_object_agg
.
Таблица 9.45. Операторы для типов json
и jsonb
Оператор Описание Пример(ы) |
---|
Извлекает
|
Извлекает поле JSON-объекта по заданному ключу.
|
Извлекает
|
Извлекает поле JSON-объекта по заданному ключу, в виде значения
|
Извлекает внутренний JSON-объект по заданному пути, элементами которого могут быть индексы массивов или ключи.
|
Извлекает внутренний JSON-объект по заданному пути в виде значения
|
Примечание
Если структура входного JSON не соответствует запросу, например указанный ключ или элемент массива отсутствует, операторы извлечения поля/элемента/пути не выдают ошибку, а возвращают NULL.
Некоторые из следующих операторов существуют только для jsonb
, как показано в Таблице 9.46. В Подразделе 8.14.4 описано, как эти операторы могут использоваться для эффективного поиска в индексированных данных jsonb
.
Таблица 9.46. Дополнительные операторы jsonb
Оператор Описание Пример(ы) |
---|
Первое значение JSON содержит второе? (Что означает «содержит», подробно описывается в Подразделе 8.14.3.)
|
Первое значение JSON содержится во втором?
|
Текстовая строка присутствует в значении JSON в качестве ключа верхнего уровня или элемента массива?
|
Какие-либо текстовые строки из массива присутствуют в качестве ключей верхнего уровня или элементов массива?
|
Все текстовые строки из массива присутствуют в качестве ключей верхнего уровня или элементов массива?
|
Соединяет два значения
Чтобы вставить один массив в другой в качестве массива, поместите его в дополнительный массив, например:
|
Удаляет ключ (и его значение) из JSON-объекта или соответствующие строковые значения из JSON-массива.
|
Удаляет из левого операнда все перечисленные ключи или элементы массива.
|
Удаляет из массива элемент в заданной позиции (отрицательные номера позиций отсчитываются от конца). Выдаёт ошибку, если переданное значение JSON — не массив.
|
Удаляет поле или элемент массива с заданным путём, в составе которого могут быть индексы массивов или ключи.
|
Возвращает ли путь JSON какой-либо элемент для указанного значения JSON? (Это полезно только для выражений пути JSON по стандарту SQL, но не для выражений проверки предикатов, поскольку они всегда возвращают значение.)
|
Возвращает результат проверки предиката пути JSON для указанного значения JSON. (Это полезно только для выражений проверки предикатов, но не для выражений пути JSON по стандарту SQL, поскольку возвращается
|
Примечание
Операторы jsonpath
@?
и @@
подавляют следующие ошибки: отсутствие поля объекта или элемента массива, несовпадение типа элемента JSON и ошибки в числах и дате/времени. Описанные ниже функции, связанные с jsonpath
, тоже могут подавлять ошибки такого рода. Это может быть полезно, когда нужно произвести поиск по набору документов JSON, имеющих различную структуру.
В Таблице 9.47 показаны функции, позволяющие создавать значения типов json
и jsonb
. Для некоторых функций в этой таблице имеется предложение RETURNING
, которое определяет возвращаемый тип данных. Это должен быть json
, jsonb
, bytea
, тип символьной строки (text
, char
или varchar
) или тип, который можно привести к json
. По умолчанию возвращается тип json
.
Таблица 9.47. Функции для создания JSON
Функция Описание Пример(ы) |
---|
Преобразует произвольное SQL-значение в
|
Преобразует массив SQL в JSON-массив. Эта функция работает так же, как
|
Создаёт массив JSON либо из набора параметров
|
Преобразует составное значение SQL в JSON-объект. Эта функция работает так же, как
|
Формирует JSON-массив (возможно, разнородный) из переменного списка аргументов. Каждый аргумент преобразуется методом
|
Формирует JSON-объект из переменного списка аргументов. По соглашению в этом списке перечисляются по очереди ключи и значения. Аргументы, задающие ключи, приводятся к текстовому типу, а аргументы-значения преобразуются методом
|
Создаёт объект JSON из всех заданных пар ключ/значение или пустой объект, если ни одна пара не задана. В аргументе
|
Формирует объект JSON из текстового массива. Этот массив должен иметь либо одну размерность с чётным числом элементов (в этом случае они воспринимаются как чередующиеся ключи/значения), либо две размерности и при этом каждый внутренний массив содержит ровно два элемента, которые воспринимаются как пара ключ/значение. Все значения преобразуются в строки JSON.
|
Эта форма
|
Преобразует выражение, заданное в виде строки типа
|
Преобразует заданное скалярное значение SQL в скалярное значение JSON. Если передаётся NULL, возвращается SQL NULL. Если передаётся число или логическое значение, возвращается соответствующее числовое или логическое значение JSON. Для любого другого значения возвращается строка JSON.
|
Преобразует выражение SQL/JSON в символьную или двоичную строку. Аргумент
|
В Таблице 9.48 описаны средства SQL/JSON для проверки JSON.
Таблица 9.48. Функции проверки SQL/JSON
В Таблице 9.49 показаны функции, предназначенные для работы со значениями json
и jsonb
.
Таблица 9.49. Функции для обработки JSON
Функция Описание Пример(ы) |
---|
Разворачивает JSON-массив верхнего уровня в набор значений JSON.
value ----------- 1 true [2,false] |
Разворачивает JSON-массив верхнего уровня в набор значений
value ----------- foo bar |
Возвращает число элементов во внешнем JSON-массиве верхнего уровня.
|
Разворачивает JSON-объект верхнего уровня в набор пар ключ/значение (key/value).
key | value -----+------- a | "foo" b | "bar" |
Разворачивает JSON-объект верхнего уровня в набор пар ключ/значение (key/value). Возвращаемые значения будут иметь тип
key | value -----+------- a | foo b | bar |
Извлекает внутренний JSON-объект по заданному пути. (То же самое делает оператор
|
Извлекает внутренний JSON-объект по заданному пути в виде значения
|
Выдаёт множество ключей в JSON-объекте верхнего уровня.
json_object_keys ----------------- f1 f2 |
Разворачивает JSON-объект верхнего уровня в строку, имеющую составной тип аргумента Для преобразования значения JSON в SQL-тип выходного столбца последовательно применяются следующие правила:
В следующем примере значение JSON фиксировано, но обычно такая функция обращается с использованием
a | b | c ---+-----------+------------- 1 | {2,"a b"} | (4,"a b c") |
Функция для тестирования
jsonb_populate_record_valid ----------------------------- f (1 row)
ОШИБКА: значение не умещается в тип character(2)
jsonb_populate_record_valid ----------------------------- t (1 row)
a ---- aa (1 row) |
Разворачивает JSON-массив верхнего уровня с объектами в набор строк, имеющих составной тип аргумента
a | b ---+--- 1 | 2 3 | 4 |
Разворачивает JSON-объект верхнего уровня в строку, имеющую составной тип, определённый в предложении
a | b | c | d | r ---+---------+---------+---+--------------- 1 | [1,2,3] | {1,2,3} | | (123,"a b c") |
Разворачивает JSON-массив верхнего уровня с объектами в набор строк, имеющих составной тип, определённый в предложении
a | b ---+----- 1 | foo 2 | |
Возвращает объект
|
Если значение
|
Возвращает объект
|
Удаляет из данного значения JSON все поля объектов, имеющие значения null, на всех уровнях вложенности. Значения null, не относящиеся к полям объектов, сохраняются без изменений.
|
Определяет, выдаёт ли путь JSON какой-либо элемент при заданном значении JSON. (Это полезно только для выражений пути JSON по стандарту SQL, а не для выражений проверки предикатов, поскольку они всегда возвращают значение.) В случае присутствия аргумента
|
Возвращает результат проверки предиката пути JSON для заданного значения JSON. (Это полезно только для выражений проверки предикатов, но не для выражений пути JSON по стандарту SQL, поскольку возвращается
|
Возвращает все элементы JSON, полученные по указанному пути JSON для заданного значения JSON. Для выражений пути JSON по стандарту SQL возвращает значения JSON, выбранные из
jsonb_path_query ------------------ 2 3 4 |
Возвращает все элементы JSON, полученные по указанному пути для заданного значения JSON, в виде JSON-массива. Параметры у этой функции те же, что и у
|
Возвращает первый элемент JSON, полученный по указанному пути для заданного значения JSON, либо
|
Эти функции работают подобно их двойникам без суффикса
|
Преобразует данное значение JSON в визуально улучшенное текстовое представление с отступами.
[ { "f1": 1, "f2": null }, 2 ] |
Возвращает тип значения на верхнем уровне JSON в виде текстовой строки. Возможные типы:
|
9.16.2. Язык путей SQL/JSON #
Выражения путей SQL/JSON определяют элементы, извлекаемые из данных JSON, подобно тому, как выражения XPath позволяют обращаться из SQL к XML. В PostgreSQL выражения путей представляются в виде типа данных jsonpath
и могут использовать любые элементы, описанные в Подразделе 8.14.7.
Операторы и функции запросов к JSON передают поступившее им выражение обработчику путей для вычисления. Если выражению соответствуют фигурирующие в запросе данные JSON, в результате выдаётся соответствующий элемент JSON или набор элементов. Если совпадение не найдено, в зависимости от функции выдаётся NULL
, false
или ошибка. Выражения путей записываются на языке путей SQL/JSON и могут включать сложные арифметические выражения и функции.
Выражение пути состоит из последовательности элементов, допустимых для типа jsonpath
. Обычно оно вычисляется слева направо, но при необходимости порядок операций можно изменить, добавив скобки. В случае успешного вычисления выдаётся последовательность элементов JSON, и результат вычисления возвращается в функцию JSON-запроса, которая завершает обработку выражения.
Для обращения к поступившему в запрос значению JSON (элементу контекста) в выражении пути используется переменная $
. Первый элемент пути всегда должен быть $
. Затем могут следовать один или более операторов обращения, которые, опускаясь в структуре JSON с одного уровня на другой, извлекают элементы, вложенные в текущий элемент контекста. При этом каждый оператор обращения имеет дело с результатом вычисления, полученным на предыдущем шаге, и выдаёт ноль, один или более выходных элементов на каждый входной элемент.
Допустим, нужно проанализировать данные JSON с GPS-трекера, например:
SELECT '{ "track": { "segments": [ { "location": [ 47.763, 13.4034 ], "start time": "2018-10-14 10:05:14", "HR": 73 }, { "location": [ 47.706, 13.2635 ], "start time": "2018-10-14 10:39:21", "HR": 135 } ] } }' AS json \gset
(Пример выше можно скопировать и вставить в psql, чтобы настроить всё для следующих примеров. Затем psql расширит :'json'
до строковой константы в соответствующих кавычках, содержащей значение JSON.)
Чтобы получить доступные сегменты отслеживания, используйте оператор доступа .
для спуска по окружающим объектам JSON, например: ключ
=>
select jsonb_path_query(:'json', '$.track.segments');
jsonb_path_query ------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}]
Для извлечения содержимого массива обычно используется оператор [*]
. В следующем примере возвращаются координаты местоположения для всех доступных сегментов треков:
=>
select jsonb_path_query(:'json', '$.track.segments[*].location');
jsonb_path_query ------------------- [47.763, 13.4034] [47.706, 13.2635]
Здесь мы начали с целого входного значения JSON ($
), затем оператор обращения .track
выбрал объект JSON, связанный с ключом объекта "track"
, оператор обращения .segments
выбрал массив JSON, связанный с ключом "segments"
внутри этого объекта, оператор обращения [*]
выбрал каждый элемент этого массива (создав ряд элементов), а оператор обращения .location
выбрал массив JSON, связанный с ключом "location"
внутри каждого из этих объектов. В этом примере у каждого из этих объектов был ключ "location"
, но если бы это было не так, вывод оператора обращения .location
был бы пустым для этого элемента ввода.
Чтобы получить координаты только первого сегмента, можно задать соответствующий индекс в операторе обращения []
. Обратите внимание, что индексы в JSON-массивах отсчитываются с 0:
=>
select jsonb_path_query(:'json', '$.track.segments[0].location');
jsonb_path_query ------------------- [47.763, 13.4034]
Результат каждого шага вычисления выражения может быть обработан одним или несколькими операторами и методами jsonpath
, перечисленными в Подразделе 9.16.2.3. Перед именем метода должна стоять точка. Например, так можно получить размер массива:
=>
select jsonb_path_query(:'json', '$.track.segments.size()');
jsonb_path_query ------------------ 2
Другие примеры использования операторов и методов jsonpath
в выражениях пути приведены ниже в Подразделе 9.16.2.3.
Определяя путь, также можно использовать выражения фильтра, работающие подобно предложению WHERE
в SQL. Выражение фильтра начинается со знака вопроса и содержит условие в круглых скобках:
? (условие
)
Выражения фильтра указываются сразу после шага вычисления пути, к которому они должны применяться. Результаты шага проходят через фильтр, и на выходе остаются только те элементы, которые удовлетворяют заданному условию. В SQL/JSON действует троичная логика, то есть результатом проверки условия может быть true
, false
или unknown
(неизвестность). Значение unknown
играет ту же роль, что и NULL
в SQL, и может быть проверено предикатом is unknown
. На последующих шагах вычисления пути будут обрабатываться только те элементы, для которых выражение фильтра выдало true
.
Функции и операторы, которые можно использовать в выражениях фильтра, перечислены в Таблице 9.51. Переменная @
в выражении фильтра представляет рассматриваемое значение (результат предыдущего шага в пути). Для получения внутренних элементов этого значения вы можете добавить операторы обращения после @
.
Например, нужно получить все значения пульса выше 130. Это можно сделать следующим образом:
=>
select jsonb_path_query(:'json', '$.track.segments[*].HR ? (@ > 130)');
jsonb_path_query ------------------ 135
Чтобы получить в результате время начала соответствующих сегментов, нужно отфильтровать ненужные сегменты, а затем выбрать время, так что фильтр будет применяться к предыдущему шагу и путь окажется другим:
=>
select jsonb_path_query(:'json', '$.track.segments[*] ? (@.HR > 130)."start time"');
jsonb_path_query ----------------------- "2018-10-14 10:39:21"
Можно также использовать несколько выражений фильтра по очереди, когда это требуется. Например, следующее выражение выбирает время начала всех сегментов с определёнными координатами и высоким показателем пульса:
=>
select jsonb_path_query(:'json', '$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"');
jsonb_path_query ----------------------- "2018-10-14 10:39:21"
Также возможно использовать фильтры на разных уровнях вложенности. В следующем примере сначала сегменты фильтруются по координатам, а затем для подходящих сегментов, если они находятся, выбираются значения высокого пульса:
=>
select jsonb_path_query(:'json', '$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)');
jsonb_path_query ------------------ 135
Можно также вкладывать выражения фильтра одно в другое. Данное выражение возвращает количество сегментов в треке, если он содержит сегменты с высокими показателями пульса, или пустую последовательность, если таких сегментов нет:
=>
select jsonb_path_query(:'json', '$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()');
jsonb_path_query ------------------ 2
9.16.2.1. Отличия от стандарта SQL #
Реализация языка путей SQL/JSON в PostgreSQL имеет следующие отличия от стандарта SQL/JSON.
9.16.2.1.1. Выражения проверки булевых предикатов #
Как расширение стандарта SQL, выражение пути PostgreSQL может быть булевым предикатом, тогда как стандарт SQL допускает предикаты только в фильтрах. В то время как выражения пути стандарта SQL возвращают соответствующий элемент(ы) запрошенного значения JSON, выражения проверки предикатов возвращают один из трёх возможных результатов проверки предиката: true
, false
или unknown
. Например, можно написать такое выражение фильтра стандарта SQL:
=>
select jsonb_path_query(:'json', '$.track.segments ?(@[*].HR > 130)');
jsonb_path_query --------------------------------------------------------------------------------- {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}
Аналогичное выражение проверки предикатов просто возвращает true
, указывая на то, что совпадение существует:
=>
select jsonb_path_query(:'json', '$.track.segments[*].HR > 130');
jsonb_path_query ------------------ true
Примечание
Выражения проверки предикатов обязательны для оператора @@
(и функции jsonb_path_match
) и не должны использоваться с оператором @?
(или функцией jsonb_path_exists
).
9.16.2.1.2. Интерпретация регулярных выражений #
Есть небольшие различия в интерпретации шаблонов регулярных выражений, используемых в фильтрах like_regex
; имеющиеся особенности описаны в Подразделе 9.16.2.4.
9.16.2.2. Строгий и нестрогий режимы #
Когда вы обращаетесь к данным JSON, выражение пути может не соответствовать фактической структуре данных JSON. Попытка обратиться к несуществующему члену объекта или элементу массива определяется как ошибка структурного типа. Для обработки такого рода ошибок в выражениях путей SQL/JSON предусмотрены два режима:
lax (по умолчанию) — нестрогий режим, в котором обработчик путей неявно адаптирует обрабатываемые данные к указанному пути. Любые структурные ошибки, которые не могут быть устранены указанными ниже способами, подавляются, и запрос не возвращает для них совпадения.
strict — строгий режим, в котором структурные ошибки выдаются как есть.
Нестрогий режим упрощает сопоставление документа JSON с выражением пути в случаях, когда данные JSON не соответствуют ожидаемой схеме. Если операнд не удовлетворяет требованиям определённой операции, он может перед выполнением этой операции автоматически оборачиваться в массив SQL/JSON или наоборот, разворачиваться так, чтобы его элементы образовали последовательность SQL/JSON. Также в нестрогом режиме операторы сравнения автоматически разворачивают свои операнды, что позволяет легко сравнивать массивы SQL/JSON. Массив с одним элементом в таком режиме считается равным своему элементу. Автоматическое разворачивание не выполняется в следующих случаях:
В выражении пути фигурируют методы
size()
иtype()
, возвращающие соответственно число элементов в массиве и тип.Обрабатываемые данные JSON содержат вложенные массивы. В этом случае разворачивается только массив верхнего уровня, а внутренние массивы остаются без изменений. Таким образом, неявное разворачивание может опускаться на каждом шаге вычисления пути только на один уровень.
Например, обрабатывая данные GPS, показанные выше, в нестрогом режиме можно не обращать внимание на то, что в них содержится массив сегментов:
=>
select jsonb_path_query(:'json', 'lax $.track.segments.location');
jsonb_path_query ------------------- [47.763, 13.4034] [47.706, 13.2635]
В строгом режиме указанный путь должен точно соответствовать структуре запрашиваемого документа JSON, поэтому использование этого выражения пути вызовет ошибку:
=>
select jsonb_path_query(:'json', 'strict $.track.segments.location');
ОШИБКА: выражение обращения к члену в jsonpath может применяться только к объекту
Чтобы получить тот же результат, что и в нестрогом режиме, нужно явно развернуть массив segments
:
=>
select jsonb_path_query(:'json', 'strict $.track.segments[*].location');
jsonb_path_query ------------------- [47.763, 13.4034] [47.706, 13.2635]
Поведение разворачивания в нестрогом режиме может выдавать несколько неожиданные результаты. Например, следующий запрос с оператором обращения .**
выберет каждое значение HR
дважды:
=>
select jsonb_path_query(:'json', 'lax $.**.HR');
jsonb_path_query ------------------ 73 135 73 135
Это происходит потому, что оператор .**
выбирает и массив segments
, и каждый из его элементов, а обращение .HR
в нестрогом режиме автоматически разворачивает массивы. Во избежание подобных сюрпризов мы рекомендуем использовать оператор обращения .**
только в строгом режиме. Следующий запрос выбирает каждое значение HR
в единственном экземпляре:
=>
select jsonb_path_query(:'json', 'strict $.**.HR');
jsonb_path_query ------------------ 73 135
Развёртывание массивов также может привести к неожиданным результатам. Рассмотрим пример, в котором выбираются все массивы location
:
=>
select jsonb_path_query(:'json', 'lax $.track.segments[*].location');
jsonb_path_query ------------------- [47.763, 13.4034] [47.706, 13.2635] (2 rows)
Как и ожидалось, возвращаются полные массивы. Но применение выражения фильтра приводит к тому, что массивы разворачиваются для оценки каждого элемента, возвращая только те элементы, которые соответствуют выражению:
=>
select jsonb_path_query(:'json', 'lax $.track.segments[*].location ?(@[*] > 15)');
jsonb_path_query ------------------ 47,763 47,706 (2 rows)
Это происходит несмотря на то, что выражение пути выбирает полные массивы. Используйте строгий режим для восстановления выбора только массивов:
=>
select jsonb_path_query(:'json', 'strict $.track.segments[*].location ?(@[*] > 15)');
jsonb_path_query ------------------- [47.763, 13.4034] [47.706, 13.2635] (2 rows)
9.16.2.3. Операторы и методы SQL/JSON #
В Таблице 9.50 показаны операторы и методы, поддерживаемые в значениях jsonpath
. Обратите внимание, что унарные операторы и методы могут применяться к множеству значений, полученных на предыдущем шаге пути, тогда как бинарные операторы (сложение и т. п.) применяются только к отдельным значениям. В нестрогом режиме методы, применяемые к массиву, будут исполняться для каждого значения в массиве. Исключениями являются .type()
и .size()
, которые применяются к самому массиву.
Таблица 9.50. Операторы и методы jsonpath
Оператор/Метод Описание Пример(ы) |
---|
Сложение
|
Унарный плюс (нет операции); в отличие от сложения, он может итерационно применяться к множеству значений
|
Вычитание
|
Смена знака; в отличие от вычитания, этот оператор может итерационно применяться к множеству значений
|
Умножение
|
Деление
|
Остаток от деления
|
Тип элемента JSON (см.
|
Размер элемента JSON (число элементов в массиве либо 1, если это не массив)
|
Логическое значение, преобразованное из логического значения JSON, числа или строки
|
Строковое значение, преобразованное из логического значения JSON, числа, строки или значения типа дата-время
|
Приблизительное число с плавающей точкой, преобразованное из строки или числа JSON
|
Ближайшее целое, большее или равное заданному числу
|
Ближайшее целое, меньшее или равное заданному числу
|
Модуль заданного числа (абсолютное значение)
|
Большое целочисленное значение, преобразованное из числового или строкового значения JSON
|
Округлённое десятичное значение, преобразованное из числового или строкового значения JSON (значения
|
Целочисленное значение, преобразованное из числового или строкового значения JSON
|
Числовое значение, преобразованное из числового или строкового значения JSON
|
Значение даты/времени, полученное из строки
|
Значение даты/времени, преобразованное из строки по шаблону
|
Значение даты, преобразованное из строки
|
Значение времени без часового пояса, полученное из строки
|
Значение времени без часового пояса, преобразованное из строки, с долями секунды, округлённой до заданной точности
|
Значение времени с часовым поясом, полученное из строки
|
Значение времени с часовым поясом, преобразованное из строки, с долями секунды, округлённой до заданной точности
|
Значение даты/времени без часового пояса, полученное из строки
|
Значение даты-времени без часового пояса, преобразованное из строки, с долями секунды, округлённой до заданной точности
|
Значение даты/времени с часовым поясом, полученное из строки
|
Значение даты/времени с часовым поясом, преобразованное из строки, с долями секунды, округлённой до заданной точности
|
Пары ключ-значение, представленные в виде массива объектов со следующими тремя полями:
|
Примечание
Результирующим типом методов datetime()
и datetime(
может быть шаблон
)date
, timetz
, time
, timestamptz
или timestamp
. Эти два метода определяют тип своего результата автоматически.
Метод datetime()
пытается последовательно сопоставить поступившую на вход строку с ISO-форматами типов date
, timetz
, time
, timestamptz
и timestamp
. Встретив первый подходящий формат, он останавливается и возвращает соответствующий тип данных.
Метод datetime(
определяет результирующий тип в соответствии с полями заданного шаблона.шаблон
)
Методы datetime()
и datetime(
применяют те же правила разбора строки, что и SQL-функция шаблон
)to_timestamp
(см. Раздел 9.8), но с тремя исключениями. Во-первых, эти методы не позволяют использовать в шаблоне поля, которым не находится соответствие. Во-вторых, в шаблоне допускаются только следующие разделители: знак минуса, точка, косая черта, запятая, апостроф, точка с запятой, запятая и пробел. В-третьих, разделители в шаблоне должны в точности соответствовать входной строке.
Если требуется сравнить значения разных типов даты/времени, применяется неявное приведение типа. Значение date
может быть приведено к типу timestamp
или timestamptz
; timestamp
— к типу timestamptz
, а time
— к timetz
. Однако все эти приведения, кроме первого, зависят от текущего значения TimeZone и поэтому не могут выполняться в функциях jsonpath
, не учитывающих часовой пояс. Аналогично, в других методах, связанных с датой/временем и преобразующих строки в типы даты/времени, также выполняется это преобразование, в котором может участвовать параметр TimeZone с текущим значением. Поэтому эти преобразования также могут выполняться только в функциях jsonpath
с поддержкой часового пояса.
В Таблице 9.51 перечислены допустимые элементы выражения фильтра.
Таблица 9.51. Элементы выражения фильтра jsonpath
Предикат/значение Описание Пример(ы) |
---|
Проверка равенства (все операторы сравнения, включая этот, работают с любыми скалярными значениями JSON)
|
Проверка неравенства
|
Проверка «меньше»
|
Проверка «меньше или равно»
|
Проверка «больше»
|
Проверка «больше или равно»
|
JSON-константа
|
JSON-константа
|
JSON-константа
|
Логическое И
|
Логическое ИЛИ
|
Логическое НЕ
|
Проверяет, является ли
|
Проверяет, соответствует ли первый операнд регулярному выражению, которое задаёт второй операнд с необязательным аргументом
|
Проверяет, является ли второй операнд начальной подстрокой первого.
|
Проверяет, соответствует ли выражению пути минимум один элемент SQL/JSON. Возвращает
|
9.16.2.4. Регулярные выражения SQL/JSON #
Выражения путей SQL/JSON могут содержать фильтры like_regex
, позволяющие сопоставлять текст с регулярным выражением. Например, следующий запрос пути SQL/JSON выберет все строки в массиве, которые начинаются с английской гласной в любом регистре:
$[*] ? (@ like_regex "^[aeiou]" flag "i")
Необязательная строка flag
может содержать один или несколько следующих символов: i
, делающий поиск регистронезависимым, m
, допускающий сопоставление ^
и $
с переводами строк, s
, допускающий сопоставление .
с символом новой строки, и q
, берущий в кавычки весь шаблон (в результате производится простой поиск подстроки).
Стандарт SQL/JSON заимствует определение регулярных выражений от оператора LIKE_REGEX
, который, в свою очередь, реализуется по стандарту XQuery. Однако в PostgreSQL оператор LIKE_REGEX
в настоящее время отсутствует. Поэтому фильтр like_regex
реализован с использованием механизма регулярных выражений POSIX, который описан в Подразделе 9.7.3. Вследствие этого наблюдается ряд небольших отклонений от описанного в стандарте поведения SQL/JSON, о которых рассказывается в Подразделе 9.7.3.8. Заметьте однако, что описанная там несовместимость букв флагов не проявляется на уровне SQL/JSON, так как заданные в SQL/JSON флаги XQuery переводятся во флаги, воспринимаемые механизмом POSIX.
Помните, что аргумент, задающий шаблон для like_regex
, является строкой пути JSON и записывается по правилам, описанным в Подразделе 8.14.7. Это в частности означает, что каждую косую черту в регулярном выражении надо дублировать. Например, чтобы отобрать строковые значения, находящиеся на уровне корня документа и содержащие только цифры, нужно написать:
$.* ? (@ like_regex "^\\d+$")
9.16.3. Функции запросов SQL/JSON #
Функции SQL/JSON JSON_EXISTS()
, JSON_QUERY()
и JSON_VALUE()
, описанные в Таблица 9.52, можно использовать для запросов к документам JSON. Все эти функции используют выражение_пути
(запрос пути SQL/JSON) к элементу_контекста
(документу). За более подробным описанием возможного содержимого выражения_пути
обратитесь к Подразделу 9.16.2. В выражении_пути
также можно ссылаться на переменные, значения которых указаны с соответствующими именами в предложении PASSING
, которое поддерживается всеми функциями. элемент_контекста
может быть значением jsonb
или символьной строкой, которая может быть приведена к jsonb
.
Таблица 9.52. Функции запросов SQL/JSON
Сигнатура функции Описание Пример(ы) |
---|
Примеры:
ОШИБКА: индекс массива jsonpath вне диапазона |
Примеры:
ОШИБКА: ошибочный литерал массива: "[1, 2]" ПОДРОБНОСТИ: После размерностей массива отсутствует "]". |
Примеры:
|
Примечание
Выражение элемент_контекста
неявно приводится к типу jsonb
, если передаётся не этого типа. Однако обратите внимание, что любые ошибки разбора, возникающие во время этого преобразования, выдаются безусловно, то есть не обрабатываются в соответствии с (указанным или неявным) предложением ON ERROR
.
Примечание
Функция JSON_VALUE()
возвращает SQL NULL, если выражение_пути
возвращает JSON null
, в отличие от функции JSON_QUERY()
, возвращающей JSON null
как есть.
9.16.4. JSON_TABLE #
JSON_TABLE
— это функция SQL/JSON, которая обрабатывает данные JSON и выдаёт результаты в виде реляционного представления, к которому можно обращаться как к обычной таблице SQL. Использовать JSON_TABLE
можно внутри предложения FROM
оператора SELECT
, UPDATE
или DELETE
и как источник данных в операторе MERGE
.
Принимая данные JSON, функция JSON_TABLE
обрабатывает выражение пути и извлекает часть представленных данных, которая будет использоваться в качестве шаблона строк для создаваемого представления. Каждое значение SQL/JSON, выдаваемое шаблоном строк, служит источником для отдельной строки в создаваемом представлении.
Чтобы разбить шаблон строк на столбцы, в функции JSON_TABLE
применяется предложение COLUMNS
, определяющее схему создаваемого представления. В этом предложении для каждого создаваемого столбца задаётся отдельное выражение пути, обрабатывающее шаблон строк, извлекающее элемент JSON и возвращающее его в виде отдельного значения SQL для данного столбца.
Данные JSON, хранящиеся на вложенном уровне шаблона строк, можно извлечь с помощью предложения NESTED PATH
. Каждое предложение NESTED PATH
можно использовать для генерации одного или нескольких столбцов с использованием данных из вложенного уровня шаблона строк. Эти столбцы можно указать в предложении COLUMNS
, которое аналогично предложению COLUMNS верхнего уровня. Строки, сгенерированные из NESTED COLUMNS, называются дочерними строками и объединяются со строкой, созданной из столбцов, которые указаны в родительском предложении COLUMNS
, чтобы получить строку в конечном представлении. Сами дочерние столбцы могут содержать спецификацию NESTED PATH
, что позволяет извлекать данные, расположенные на произвольных уровнях вложенности. Столбцы, созданные несколькими NESTED PATH
на одном уровне, считаются соседними, а их строки после соединения с родительской строкой объединяются с помощью UNION.
Строки, формируемые функцией JSON_TABLE
, соединяются как последующие (LATERAL
) со строкой, из которой они сформированы, поэтому нет необходимости явно соединять создаваемое представление с исходной таблицей, содержащей данные JSON.
Синтаксис:
JSON_TABLE (элемент_контекста
,выражение_пути
[ASимя_пути_json
] [PASSING {значение
ASимя_переменной
} [, ...]] COLUMNS (столбец_таблицы_json
[, ...] ) [{ERROR
|EMPTY
[ARRAY]}ON ERROR
] ) Здесьстолбец_таблицы_json
:имя
FOR ORDINALITY |имя
тип
[FORMAT JSON [ENCODINGUTF8
]] [PATHвыражение_пути
] [{ WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ARRAY] WRAPPER] [{ KEEP | OMIT } QUOTES [ON SCALAR STRING]] [{ ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULTвыражение
} ON EMPTY] [{ ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULTвыражение
} ON ERROR] |имя
тип
EXISTS [PATHвыражение_пути
] [{ ERROR | TRUE | FALSE | UNKNOWN } ON ERROR] | NESTED [PATH]выражение_пути
[ASимя_пути_json
] COLUMNS (столбец_таблицы_json
[, ...] )
Каждый элемент синтаксиса описан ниже более подробно.
-
элемент_контекста
,выражение_пути
[AS
имя_пути_json
] [PASSING
{значение
AS
имя_переменной
} [, ...]] Выражение
элемент_контекста
указывает входной документ для запроса,выражение_пути
— это выражение пути SQL/JSON, определяющее запрос, аимя_пути_json
— необязательное имя длявыражения_пути
. Необязательное предложениеPASSING
может предоставлять значения данных для переменных, указанных ввыражении_пути
. Результат обработки входных данных с использованием вышеупомянутых элементов называется шаблоном строк, который используется в качестве источника для значений строк в создаваемом представлении.COLUMNS
(столбец_таблицы_json
[, ...] )Предложение
COLUMNS
, определяющее схему сконструированного представления. В этом предложении можно указать, что каждый столбец должен быть заполнен значением SQL/JSON, полученным путём применения выражения пути JSON к шаблону строк. Есть следующие вариантыстолбца_таблицы_json
:-
имя
FOR ORDINALITY
Добавляет столбец нумерации, который обеспечивает последовательную нумерацию строк, начиная с 1. Каждое выражение
NESTED PATH
(см. ниже) получает собственный счётчик для любых вложенных столбцов нумерации.-
имя
тип
[FORMAT JSON
[ENCODINGUTF8
]] [PATH
выражение_пути
] Вставляет значение SQL/JSON, полученное путём применения
выражения_пути
к шаблону строк, в выходную строку представления после приведения его к указанномутипу
.FORMAT JSON
явно указывает, что ожидается допустимый объектjson
. УказыватьFORMAT JSON
имеет смысл только в том случае, еслитип
— этоbpchar
,bytea
,character variationing
,name
,json
,jsonb
илиtext
или домен над этими типами.Чтобы форматировать вывод, также можно указать предложения
WRAPPER
иQUOTES
. Обратите внимание, что указаниеOMIT QUOTES
переопределяетFORMAT JSON
, поскольку не заключённые в кавычки литералы не являются допустимыми значениямиjson
.Также можно использовать предложения
ON EMPTY
иON ERROR
, чтобы выдавалась ошибка или возвращалось указанное значение при пустом результате вычисления пути JSON и при возникновении ошибки во время вычисления пути JSON или приведения значения SQL/JSON к указанному типу соответственно. По умолчанию для обоих случаев возвращается значениеNULL
.Примечание
Это предложение внутри преобразуется в
JSON_VALUE
илиJSON_QUERY
с соответствующей семантикой. Предложение преобразуется вJSON_QUERY
, если указанный тип не является скалярным типом или присутствует одно из следующих предложений:FORMAT JSON
,WRAPPER
илиQUOTES
.-
имя
тип
EXISTS
[PATH
выражение_пути
] Вставляет логическое значение, полученное путём применения
выражения_пути
к шаблону строк, в выходную строку представления после приведения её к указанномутипу
.Значение показывает, будут ли получены какие-либо значения в результате применения выражения
PATH
к шаблону строк.Указанный
тип
должен иметь приведение к типуboolean
.Также можно использовать предложение
ON ERROR
, чтобы выдавалась ошибка или возвращалось указанное значение при возникновении ошибки во время вычисления пути JSON или приведения значения SQL/JSON к указанному типу. По умолчанию в обоих случаях возвращается значениеNULL
.Примечание
Это предложение внутри преобразуется в
JSON_EXISTS
с соответствующей семантикой.NESTED [ PATH ]
выражение_пути
[AS
имя_пути_json
]COLUMNS
(столбец_таблицы_json
[, ...] )Извлекает значения SQL/JSON из вложенных уровней шаблона строк, создаёт один или несколько столбцов, как определено во вложенном предложении
COLUMNS
, и вставляет извлечённые значения SQL/JSON в эти столбцы. Выражениестолбец_таблицы_json
во вложенном предложенииCOLUMNS
имеет тот же синтаксис, что и в родительском предложенииCOLUMNS
.Синтаксис
NESTED PATH
является рекурсивным, поэтому вкладывая одно предложениеNESTED PATH
в другое, можно опускаться ниже от уровня к уровню. Это позволяет развернуть иерархию объектов и массивов JSON в одном вызове функции, а не связывать несколько выраженийJSON_TABLE
в операторе SQL.
Примечание
Во всех вариантах
столбца_таблицы_json
, описанных выше, если не указано предложениеPATH
, используется выражение пути$.
, гдеимя
имя
— указанное имя столбца.-
-
AS
имя_пути_json
Необязательный параметр
имя_пути_json
служит идентификатором заданноговыражения_пути
. Имя пути должно быть уникальным и отличаться от имён столбцов.- {
ERROR
|EMPTY
}ON ERROR
Необязательное предложение
ON ERROR
можно использовать для указания способа обработки ошибок при вычислениивыражения_пути
верхнего уровня. Используйте предложениеERROR
, чтобы выдавались ошибки, иEMPTY
, чтобы возвращалась пустая таблица (то есть таблица, содержащая 0 строк). Обратите внимание, что это предложение не влияет на ошибки, возникающие при вычислении столбцов, поведение которых зависит от указания для них предложенияON ERROR
.
Примеры
В приведённых примерах будет использоваться следующая таблица, содержащая данные JSON:
CREATE TABLE my_films ( js jsonb ); INSERT INTO my_films VALUES ( '{ "favorites" : [ { "kind" : "comedy", "films" : [ { "title" : "Bananas", "director" : "Woody Allen"}, { "title" : "The Dinner Game", "director" : "Francis Veber" } ] }, { "kind" : "horror", "films" : [ { "title" : "Psycho", "director" : "Alfred Hitchcock" } ] }, { "kind" : "thriller", "films" : [ { "title" : "Vertigo", "director" : "Alfred Hitchcock" } ] }, { "kind" : "drama", "films" : [ { "title" : "Yojimbo", "director" : "Akira Kurosawa" } ] } ] }');
В следующем запросе показано, как использовать функцию JSON_TABLE
для преобразования объектов JSON-таблицы my_films
в представление, содержащее столбцы для ключей kind
, title
и director
в исходных данных JSON, а также столбец нумерации:
SELECT jt.* FROM my_films, JSON_TABLE (js, '$.favorites[*]' COLUMNS ( id FOR ORDINALITY, kind text PATH '$.kind', title text PATH '$.films[*].title' WITH WRAPPER, director text PATH '$.films[*].director' WITH WRAPPER)) AS jt;
id | kind | title | director ----+----------+--------------------------------+---------------------------------- 1 | comedy | ["Bananas", "The Dinner Game"] | ["Woody Allen", "Francis Veber"] 2 | horror | ["Psycho"] | ["Alfred Hitchcock"] 3 | thriller | ["Vertigo"] | ["Alfred Hitchcock"] 4 | drama | ["Yojimbo"] | ["Akira Kurosawa"] (4 rows)
Ниже представлена изменённая версия запроса с использованием аргументов PASSING
в фильтре, указанном в выражении пути JSON верхнего уровня, и различными параметрами для отдельных столбцов:
SELECT jt.* FROM my_films, JSON_TABLE (js, '$.favorites[*] ? (@.films[*].director == $filter)' PASSING 'Alfred Hitchcock' AS filter, 'Vertigo' AS filter2 COLUMNS ( id FOR ORDINALITY, kind text PATH '$.kind', title text FORMAT JSON PATH '$.films[*].title' OMIT QUOTES, director text PATH '$.films[*].director' KEEP QUOTES)) AS jt;
id | kind | title | director ----+----------+---------+-------------------- 1 | horror | Psycho | "Alfred Hitchcock" 2 | thriller | Vertigo | "Alfred Hitchcock" (2 строки)
Ниже представлена изменённая версия запроса с использованием NESTED PATH
для заполнения столбцов title и director, демонстрирующая их соединение с родительскими столбцами id и kind:
SELECT jt.* FROM my_films, JSON_TABLE ( js, '$.favorites[*] ? (@.films[*].director == $filter)' PASSING 'Alfred Hitchcock' AS filter COLUMNS ( id FOR ORDINALITY, kind text PATH '$.kind', NESTED PATH '$.films[*]' COLUMNS ( title text FORMAT JSON PATH '$.title' OMIT QUOTES, director text PATH '$.director' KEEP QUOTES))) AS jt;
id | kind | title | director ----+----------+---------+-------------------- 1 | horror | Psycho | "Alfred Hitchcock" 2 | thriller | Vertigo | "Alfred Hitchcock" (2 rows)
Ниже представлен тот же запрос, но без фильтра в корневом пути:
SELECT jt.* FROM my_films, JSON_TABLE ( js, '$.favorites[*]' COLUMNS ( id FOR ORDINALITY, kind text PATH '$.kind', NESTED PATH '$.films[*]' COLUMNS ( title text FORMAT JSON PATH '$.title' OMIT QUOTES, director text PATH '$.director' KEEP QUOTES))) AS jt;
id | kind | title | director ----+----------+-----------------+-------------------- 1 | comedy | Bananas | "Woody Allen" 1 | comedy | The Dinner Game | "Francis Veber" 2 | horror | Psycho | "Alfred Hitchcock" 3 | thriller | Vertigo | "Alfred Hitchcock" 4 | drama | Yojimbo | "Akira Kurosawa" (5 rows)
Ниже представлен ещё один запрос, использующий в качестве входных данных другой объект JSON
. В нём показано объединение «соседей» между путями NESTED
$.movies[*]
и $.books[*]
, а также использование столбца FOR ORDINALITY
на уровнях NESTED
(столбцы movie_id
, book_id
и author_id
):
SELECT * FROM JSON_TABLE ( '{"favorites": {"movies": [{"name": "One", "director": "John Doe"}, {"name": "Two", "director": "Don Joe"}], "books": [{"name": "Mystery", "authors": [{"name": "Brown Dan"}]}, {"name": "Wonder", "authors": [{"name": "Jun Murakami"}, {"name":"Craig Doe"}]}] }}'::json, '$.favorites[*]' COLUMNS ( user_id FOR ORDINALITY, NESTED '$.movies[*]' COLUMNS ( movie_id FOR ORDINALITY, mname text PATH '$.name', director text), NESTED '$.books[*]' COLUMNS ( book_id FOR ORDINALITY, bname text PATH '$.name', NESTED '$.authors[*]' COLUMNS ( author_id FOR ORDINALITY, author_name text PATH '$.name'))));
user_id | movie_id | mname | director | book_id | bname | author_id | author_name ---------+----------+-------+----------+---------+---------+-----------+-------------- 1 | 1 | One | John Doe | | | | 1 | 2 | Two | Don Joe | | | | 1 | | | | 1 | Mystery | 1 | Brown Dan 1 | | | | 2 | Wonder | 1 | Jun Murakami 1 | | | | 2 | Wonder | 2 | Craig Doe (5 rows)