9.16. Функции и операторы JSON#
9.16. Функции и операторы JSON #
Этот раздел описывает:
функции и операторы для обработки и создания данных JSON
язык SQL/JSON пути
функции SQL/JSON запросов
Для обеспечения нативной поддержки типов данных JSON в среде SQL, Tantor SE реализует модель данных SQL/JSON. Эта модель включает последовательности элементов. Каждый элемент может содержать скалярные значения 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, поддерживаемых в Tantor SE, см. Раздел 8.14.
9.16.1. Обработка и создание данных JSON #
Таблица 9.45 показывает операторы, которые доступны для использования с типами данных JSON (см. Раздел 8.14).
Кроме того, обычные операторы сравнения, показанные в Таблица 9.1, доступны для типа jsonb
, но не для типа json
. Операторы сравнения следуют правилам упорядочивания для операций 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 по указанному пути в виде
|
Примечание
Операторы извлечения полей/элементов/путей возвращают NULL, а не вызывают ошибку, если JSON-ввод не имеет правильной структуры для соответствия запросу; например, если такого ключа или элемента массива не существует.
Существуют дополнительные операторы только для типа jsonb
, как показано в Таблица 9.46.
Раздел 8.14.4 описывает, как эти операторы могут быть использованы для эффективного поиска индексированных данных типа jsonb
.
Таблица 9.46. Дополнительные операторы jsonb
Оператор Описание Пример(ы) |
---|
Содержит ли первое значение JSON второе? (См. Раздел 8.14.3 для получения подробной информации о содержании).
|
Содержится ли первое значение JSON во втором?
|
Существует ли текстовая строка в качестве ключа верхнего уровня или элемента массива в значении JSON?
|
Существуют ли какие-либо строки в массиве текста в качестве ключей верхнего уровня или элементов массива?
|
Существуют ли все строки в массиве текста в качестве ключей верхнего уровня или элементов массива?
|
Конкатенирует два значения
Чтобы добавить массив в другой массив как один элемент, оберните его в дополнительный слой массива, например:
|
Удаляет ключ (и его значение) из JSON-объекта или соответствующие строковые значения из JSON-массива.
|
Удаляет все совпадающие ключи или элементы массива из левого операнда.
|
Удаляет элемент массива с указанным индексом (отрицательные целые числа считаются с конца). Выдает ошибку, если значение JSON не является массивом.
|
Удаляет поле или элемент массива по указанному пути, где элементы пути могут быть ключами полей или индексами массива.
|
Возвращает ли JSON путь какой-либо элемент для указанного значения JSON? (Это полезно только с SQL-стандартными выражениями JSON пути, а не выражениями проверки предикатов, так как те всегда возвращают значение.)
|
Возвращает результат проверки предиката JSON path для
указанного JSON значения.
(Это полезно только
с выражениями проверки
предикатов, а не с JSON path выражениями стандарта 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-объект из списка переменных аргументов. По соглашению,
список аргументов состоит из чередующихся ключей и значений. Ключевые
аргументы приводятся к типу text; аргументы значений преобразуются
согласно функциям
|
Создает 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 -----+------- a | "foo" b | "bar"
|
Расширяет верхний уровень JSON-объекта в набор пар ключ/значение.
Возвращаемые значения
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)
ERROR: value too long for type 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 |
|
Возвращает
|
Если
|
Возвращает
|
Удаляет все поля объекта, которые имеют значение null из заданного JSON значения, рекурсивно. Значения null, которые не являются полями объекта, остаются нетронутыми.
|
Проверяет, возвращает ли JSON путь какой-либо элемент для указанного JSON
значения.
(Это полезно только с JSON-путями в формате SQL-стандарта, а не
выражениями проверки
предикатов, так как те всегда возвращают значение.)
Если аргумент
|
Возвращает SQL-логический результат проверки предиката JSON path для указанного значения JSON. (Это полезно только с выражениями проверки предикатов, а не с JSON path выражениями по стандарту SQL, так как в противном случае это либо завершится с ошибкой, либо вернет
|
Возвращает все элементы JSON, возвращенные JSON-путем для указанного
JSON-значения.
Для стандартных SQL выражений JSON-путей возвращает JSON
значения, выбранные из
jsonb_path_query ------------------ 2 3 4
|
Возвращает все элементы JSON, возвращенные JSON-путем для указанного
JSON-значения, в виде JSON-массива.
Параметры такие же, как и
для
|
Возвращает первый элемент JSON, возвращаемый JSON-путем для
указанного JSON-значения, или
|
Эти функции действуют так же, как и их аналоги, описанные выше без суффикса
|
Преобразует заданное значение JSON в красиво отформатированный текст с отступами.
[ { "f1": 1, "f2": null }, 2 ]
|
Возвращает тип верхнего уровня значения JSON в виде текстовой строки.
Возможные типы:
|
9.16.2. Язык SQL/JSON Path #
SQL/JSON пути выражений указывают элемент(ы), которые нужно извлечь
из JSON значения, аналогично выражениям XPath, используемым
для доступа к содержимому XML. В Tantor SE,
пути выражений реализованы как тип данных 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-объекты, например:
key
=>
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. Выражение фильтра начинается с
вопросительного знака и содержит условие в скобках:
? (condition
)
Фильтры выражений должны быть написаны сразу после шага оценки пути, к которому они должны применяться. Результат этого шага фильтруется, чтобы включать только те элементы, которые удовлетворяют предоставленному условию. SQL/JSON определяет трехзначную логику, поэтому условие может давать true
, false
или unknown
. Значение unknown
играет ту же роль, что и SQL NULL
, и может быть проверено с помощью предиката 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 #
Tantor SE реализация языка SQL/JSON path имеет следующие отклонения от стандарта SQL/JSON.
9.16.2.1.1. Проверка выражений логического предиката #
В качестве расширения стандарта SQL,
выражение пути Tantor SE может быть
булевым предикатом, тогда как стандарт SQL позволяет использовать предикаты только в фильтрах. В то время как выражения пути по стандарту SQL возвращают соответствующий
элемент(ы) запрашиваемого значения JSON, выражения проверки предикатов
возвращают единственный трехзначный результат jsonb
предиката: true
,
false
или null
.
Например, мы могли бы написать это выражение фильтра по стандарту 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 — если происходит структурная ошибка, возникает ошибка.
Режим Lax облегчает сопоставление JSON-документа и выражения пути, когда данные JSON не соответствуют ожидаемой схеме. Если операнд не соответствует требованиям определенной операции, он может быть автоматически обернут как массив SQL/JSON или развернут путем преобразования его элементов в последовательность SQL/JSON перед выполнением операции. Также операторы сравнения автоматически разворачивают свои операнды в режиме lax, так что вы можете сравнивать массивы SQL/JSON "из коробки". Массив размером 1 считается равным своему единственному элементу. Автоматическое разворачивание не выполняется, когда:
Выражение пути содержит методы
type()
илиsize()
, которые возвращают тип и количество элементов в массиве соответственно.Запрошенные данные JSON содержат вложенные массивы. В этом случае, только внешний массив распаковывается, в то время как все внутренние массивы остаются неизменными. Таким образом, неявное распаковывание может происходить только на один уровень вниз в каждом шаге оценки пути.
Например, при запросе данных GPS, перечисленных выше, вы можете абстрагироваться от того факта, что он хранит массив сегментов, используя режим lax:
=>
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');
ERROR: jsonpath member accessor can only be applied to an object
Чтобы получить тот же результат, что и в режиме lax, вам нужно явно развернуть массив segments
:
=>
select jsonb_path_query(:'json', 'strict $.track.segments[*].location');
jsonb_path_query ------------------- [47.763, 13.4034] [47.706, 13.2635]
Поведение в режиме lax может привести к неожиданным результатам. Например, следующий запрос с использованием аксессора .**
выбирает каждое значение HR
дважды:
=>
select jsonb_path_query(:'json', 'lax $.**.HR');
jsonb_path_query ------------------ 73 135 73 135
Это происходит потому, что аксессор .**
выбирает как
массив segments
, так и каждый из его элементов, в то время как
аксессор .HR
автоматически разворачивает массивы при
использовании в lax режиме. Чтобы избежать неожиданных результатов, мы рекомендуем использовать
аксессор .**
только в строгом режиме. Следующий запрос выбирает каждое значение 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 Path #
Таблица 9.50 показывает операторы и методы, доступные в jsonpath
. Обратите внимание, что унарные операторы и методы могут применяться к нескольким значениям, полученным из предыдущего шага пути, тогда как бинарные операторы (сложение и т. д.) могут применяться только к одиночным значениям. В режиме lax методы, применяемые к массиву, будут выполняться для каждого значения в массиве. Исключениями являются .type()
и .size()
, которые применяются к самому массиву.
Таблица 9.50. jsonpath
Операторы и методы
Оператор/Метод Описание Пример(ы) |
---|
Дополнение
|
Унарный плюс (без операции); в отличие от сложения, он может выполняться для нескольких значений
|
Вычитание
|
Отрицание; в отличие от вычитания, это может выполняться для нескольких значений
|
Умножение
|
Раздел
|
Модуло (остаток)
|
Тип элемента JSON (см. функцию
|
Размер элемента JSON (количество элементов массива или 1, если это не массив)
|
Логическое значение, преобразованное из JSON логического, числового значения или строки
|
Строковое значение, преобразованное из JSON логического значения, числа, строки или даты и времени
|
Приблизительное число с плавающей запятой, преобразованное из числа или строки JSON
|
Ближайшее целое число, большее или равное заданному числу
|
Ближайшее целое число, меньшее или равное данному числу
|
Абсолютное значение данного числа
|
Большое целое число, преобразованное из JSON числа или строки
|
Округленное десятичное значение, преобразованное из числа или строки JSON
(
|
Целочисленное значение, преобразованное из числа или строки JSON
|
Числовое значение, преобразованное из числа или строки JSON
|
Значение даты/времени, преобразованное из строки
|
Значение даты/времени, преобразованное из строки с использованием указанного шаблона
|
Значение даты, преобразованное из строки
|
Значение времени без часового пояса, преобразованное из строки
|
Значение времени без часового пояса, преобразованное из строки, с дробными секундами, округленными до заданной точности
|
Значение времени с часовым поясом, преобразованное из строки
|
Значение времени с часовым поясом, преобразованное из строки, с дробными секундами, округленными до заданной точности
|
Значение временной метки без часового пояса, преобразованное из строки
|
Значение временной метки без часового пояса, преобразованное из строки, с дробными секундами, приведенными к заданной точности
|
Значение временной метки с часовым поясом, преобразованное из строки
|
Значение временной метки с часовым поясом, преобразованное из строки, с дробными секундами, округленными до заданной точности
|
Массив пар ключ-значение объекта, представленный в виде массива объектов, содержащих три поля:
|
Примечание
Тип результата методов datetime()
и
datetime(
может быть template
)date
, timetz
, time
,
timestamptz
или timestamp
.
Оба метода определяют свой тип результата динамически.
Метод datetime()
последовательно пытается сопоставить входную строку с форматами ISO для типов данных date
, timetz
, time
, timestamptz
и timestamp
. Он останавливается на первом совпадающем формате и возвращает соответствующий тип данных.
Метод datetime(
определяет тип результата в соответствии с полями, используемыми в предоставленной строке шаблона.
template
)
Методы datetime()
и
datetime(
используют те же правила разбора, что и функция SQL template
)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, так как он преобразует буквы флагов 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-документов. Каждая из этих функций применяет
path_expression
(запрос SQL/JSON пути) к
context_item
(документу). См.
Раздел 9.16.2 для получения более подробной информации о том,
что может содержать path_expression
.
path_expression
также может ссылаться на переменные,
значения которых указываются с их соответствующими именами в
предложении PASSING
, поддерживаемом каждой функцией.
context_item
может быть значением jsonb
или строкой символов, которая может быть успешно преобразована в jsonb
.
Таблица 9.52. Функции SQL/JSON запроса
Сигнатура функции Описание Пример(ы) |
---|
Примеры:
ERROR: jsonpath array subscript is out of bounds
|
Примеры:
ERROR: malformed array literal: "[1, 2]" DETAIL: Missing "]" after array dimensions.
|
Примеры:
|
Примечание
Выражение context_item
преобразуется в
jsonb
с помощью неявного приведения, если выражение еще не имеет
тип jsonb
. Однако обратите внимание, что любые ошибки разбора, которые возникают
во время этого преобразования, выбрасываются безусловно, то есть не
обрабатываются в соответствии с (указанным или неявным) клаузой ON ERROR
.
Примечание
JSON_VALUE()
возвращает SQL NULL, если
path_expression
возвращает 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
использует выражение пути JSON для извлечения части предоставленных данных, чтобы использовать их в качестве шаблона строки для построенного представления. Каждое значение SQL/JSON, заданное шаблоном строки, служит источником для отдельной строки в построенном представлении.
Чтобы разделить шаблон строки на столбцы, JSON_TABLE
предоставляет предложение COLUMNS
, которое определяет
схему создаваемого представления. Для каждого столбца можно указать отдельное выражение JSON path,
которое будет оцениваться относительно шаблона строки, чтобы получить значение SQL/JSON,
которое станет значением для указанного столбца в данной выходной строке.
Данные JSON, хранящиеся на вложенном уровне шаблона строки, могут быть извлечены с помощью
предложения NESTED PATH
. Каждое
предложение NESTED PATH
может использоваться для генерации одного или нескольких
столбцов, используя данные из вложенного уровня шаблона строки. Эти
столбцы могут быть указаны с помощью предложения COLUMNS
, которое
выглядит аналогично предложению COLUMNS верхнего уровня. Строки, построенные из
NESTED COLUMNS, называются дочерними строками и соединяются
со строкой, построенной из столбцов, указанных в родительском
предложении COLUMNS
, чтобы получить строку в конечном представлении. Дочерние
столбцы сами по себе могут содержать спецификацию NESTED PATH
,
что позволяет извлекать данные, расположенные на произвольных уровнях вложенности.
Столбцы, созданные несколькими NESTED PATH
на
одном уровне, считаются братьями и сестрами друг друга, и их строки после соединения с родительской строкой объединяются с помощью
UNION.
Строки, создаваемые JSON_TABLE
, соединяются латерально
со строкой, которая их сгенерировала, поэтому вам не нужно явно соединять
построенное представление с исходной таблицей, содержащей данные JSON.
Синтаксис:
JSON_TABLE (context_item
,path_expression
[ ASjson_path_name
] [PASSING {значение
ASимя_переменной
} [, ...]] COLUMNS (json_table_column
[, ...] ) [{ОШИБКА
|ПУСТОЙ
[МАССИВ]}ПРИ ОШИБКЕ
] ) wherejson_table_column
is:name
FOR ORDINALITY |name
type
[FORMAT JSON [КОДИРОВКАUTF8
]] [ PATHpath_expression
] [{ БЕЗ | С { УСЛОВНЫМ | [БЕЗУСЛОВНЫМ] } } [ МАССИВНЫМ ] ОБОЛОЧКОЙ] [{ СОХРАНИТЬ | ИСКЛЮЧИТЬ } КАВЫЧКИ [ НА СКАЛЯРНОЙ СТРОКЕ ]] [{ ОШИБКА | NULL | ПУСТОЙ { [Массив] | ОБЪЕКТ } | ПО УМОЛЧАНИЮвыражение
} ПРИ ПУСТОМ] [{ ОШИБКА | NULL | ПУСТОЙ { [Массив] | ОБЪЕКТ } | ПО УМОЛЧАНИЮвыражение
} ПРИ ОШИБКЕ] |name
type
EXISTS [ PATHpath_expression
] [ { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR ] | NESTED [ PATH ]path_expression
[ ASjson_path_name
] COLUMNS (json_table_column
[, ...] )
Каждый элемент синтаксиса описан ниже более подробно.
-
context_item
,path_expression
[AS
json_path_name
] [PASSING
{value
AS
varname
} [, ...]] context_item
указывает входной документ для запроса,path_expression
является SQL/JSON выражением пути, определяющим запрос, аjson_path_name
является необязательным именем дляpath_expression
. Необязательная частьPASSING
предоставляет значения данных для переменных, упомянутых вpath_expression
. Результат оценки входных данных с использованием вышеупомянутых элементов называется шаблоном строки, который используется в качестве источника для значений строк в построенном представлении.-
COLUMNS
(json_table_column
[, ...] ) Клаузула
COLUMNS
, определяющая схему создаваемого представления. В этой клаузуле вы можете указать каждый столбец, который будет заполнен значением SQL/JSON, полученным путем применения выражения JSON path к шаблону строки.json_table_column
имеет следующие варианты:-
name
FOR ORDINALITY
Добавляет столбец порядковости, который обеспечивает последовательную нумерацию строк, начиная с 1. Каждый
NESTED PATH
(см. ниже) получает свой собственный счетчик для любых вложенных столбцов порядковости.-
name
type
[ФОРМАТ JSON
[КОДИРОВКАUTF8
]] [PATH
path_expression
] Вставляет значение SQL/JSON, полученное путем применения
path_expression
к шаблону строки, в выходную строку представления после приведения его к указанномуtype
.Указание
FORMAT JSON
делает явным, что вы ожидаете, что значение будет допустимым объектомjson
. Это имеет смысл указыватьFORMAT JSON
только еслиtype
является одним изbpchar
,bytea
,character varying
,name
,json
,jsonb
,text
или доменом над этими типами.При необходимости, вы можете указать
WRAPPER
иQUOTES
для форматирования вывода. Обратите внимание, что указаниеOMIT QUOTES
отменяетFORMAT JSON
, если также указано, потому что не заключенные в кавычки литералы не являются допустимыми значениямиjson
.При необходимости, вы можете использовать
ON EMPTY
иON ERROR
выражения, чтобы указать, следует ли выбрасывать ошибку или возвращать указанное значение, когда результат выполнения JSON пути пуст, и когда возникает ошибка во время выполнения JSON пути или при приведении значения SQL/JSON к указанному типу, соответственно. По умолчанию для обоих случаев возвращается значениеNULL
.Примечание
Этот клауз превращается внутренне и имеет ту же семантику, что и
JSON_VALUE
илиJSON_QUERY
. Последний, если указанный тип не является скалярным типом или если присутствует любой изFORMAT JSON
,WRAPPER
илиQUOTES
клауз.-
name
type
EXISTS
[PATH
path_expression
] Вставляет логическое значение, полученное путем применения
path_expression
к шаблону строки в выходную строку представления после приведения его к указанномуtype
.Значение соответствует тому, приводит ли применение
PATH
выражения к шаблону строки к каким-либо значениям.Указанный
тип
должен иметь приведение из типаboolean
.При необходимости, вы можете использовать
ON ERROR
, чтобы указать, следует ли выбрасывать ошибку или возвращать указанное значение, когда возникает ошибка во время оценки JSON пути или при приведении SQL/JSON значения к указанному типу. По умолчанию возвращается булево значениеFALSE
.Примечание
Этот клауз превращается внутренне и имеет ту же семантику, что и
JSON_EXISTS
.-
NESTED [ PATH ]
path_expression
[AS
json_path_name
]COLUMNS
(json_table_column
[, ...] ) Извлекает значения SQL/JSON из вложенных уровней шаблона строки, генерирует один или несколько столбцов, как определено в подклаузе
COLUMNS
, и вставляет извлеченные значения SQL/JSON в эти столбцы. Выражениеjson_table_column
в подклаузеCOLUMNS
использует тот же синтаксис, что и в родительской клаузеCOLUMNS
.Синтаксис
NESTED PATH
является рекурсивным, поэтому вы можете пройти по нескольким вложенным уровням, указывая несколько подклаузNESTED PATH
друг в друге. Это позволяет развернуть иерархию JSON объектов и массивов в одном вызове функции, а не связывать несколько выраженийJSON_TABLE
в SQL-запросе.
Примечание
В каждом варианте
json_table_column
, описанном выше, если опущено предложениеPATH
, используется выражение пути$.
, гдеname
name
— это предоставленное имя столбца.-
-
AS
json_path_name
Необязательный
json_path_name
служит идентификатором предоставленногоpath_expression
. Имя должно быть уникальным и отличаться от имен столбцов.-
{
ERROR
|EMPTY
}ON ERROR
Необязательный
ON ERROR
может быть использован для указания того, как обрабатывать ошибки при оценке верхнего уровняpath_expression
. Используйте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 path верхнего уровня, и различные варианты для
отдельных столбцов:
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', 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 rows)
Следующее является модифицированной версией вышеуказанного запроса, чтобы показать использование
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
в качестве входных данных. Он демонстрирует объединение UNION "sibling join" между путями 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)