9.16. Функции и операторы JSON#

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 -> integerjson

jsonb -> integerjsonb

Извлекает n-й элемент массива JSON (элементы массива нумеруются с нуля, но отрицательные целые числа считаются с конца).

'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> 2{"c":"baz"}

'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> -3{"a":"foo"}

json -> textjson

jsonb -> textjsonb

Извлекает поле объекта JSON с заданным ключом.

'{"a": {"b":"foo"}}'::json -> 'a'{"b":"foo"}

json ->> integertext

jsonb ->> integertext

Извлекает n-й элемент массива JSON в виде текста.

'[1,2,3]'::json ->> 23

json ->> texttext

jsonb ->> texttext

Извлекает поле объекта JSON с заданным ключом, как text.

'{"a":1,"b":2}'::json ->> 'b'2

json #> text[]json

jsonb #> text[]jsonb

Извлекает подобъект JSON по указанному пути, где элементы пути могут быть ключами полей или индексами массива.

'{"a": {"b": ["foo","bar"]}}'::json #> '{a,b,1}'"bar"

json #>> text[]text

jsonb #>> text[]text

Извлекает подобъект JSON по указанному пути в виде текста.

'{"a": {"b": ["foo","bar"]}}'::json #>> '{a,b,1}'bar


Примечание

Операторы извлечения полей/элементов/путей возвращают NULL, а не вызывают ошибку, если JSON-ввод не имеет правильной структуры для соответствия запросу; например, если такого ключа или элемента массива не существует.

Существуют дополнительные операторы только для типа jsonb, как показано в Таблица 9.46. Раздел 8.14.4 описывает, как эти операторы могут быть использованы для эффективного поиска индексированных данных типа jsonb.

Таблица 9.46. Дополнительные операторы jsonb

Оператор

Описание

Пример(ы)

jsonb @> jsonbboolean

Содержит ли первое значение JSON второе? (См. Раздел 8.14.3 для получения подробной информации о содержании).

'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonbt

jsonb <@ jsonbboolean

Содержится ли первое значение JSON во втором?

'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonbt

jsonb ? textboolean

Существует ли текстовая строка в качестве ключа верхнего уровня или элемента массива в значении JSON?

'{"a":1, "b":2}'::jsonb ? 'b't

'["a", "b", "c"]'::jsonb ? 'b't

jsonb ?| text[]boolean

Существуют ли какие-либо строки в массиве текста в качестве ключей верхнего уровня или элементов массива?

'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd']t

jsonb ?& text[]boolean

Существуют ли все строки в массиве текста в качестве ключей верхнего уровня или элементов массива?

'["a", "b", "c"]'::jsonb ?& array['a', 'b']t

jsonb || jsonbjsonb

Конкатенирует два значения jsonb. Конкатенация двух массивов создает массив, содержащий все элементы каждого входного массива. Конкатенация двух объектов создает объект, содержащий объединение их ключей, принимая значение второго объекта при наличии дублирующихся ключей. Все остальные случаи обрабатываются путем преобразования не массивного ввода в одноэлементный массив, а затем продолжения работы как для двух массивов. Не работает рекурсивно: объединяется только структура массива или объекта верхнего уровня.

'["a", "b"]'::jsonb || '["a", "d"]'::jsonb["a", "b", "a", "d"]

'{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb{"a": "b", "c": "d"}

'[1, 2]'::jsonb || '3'::jsonb[1, 2, 3]

'{"a": "b"}'::jsonb || '42'::jsonb[{"a": "b"}, 42]

Чтобы добавить массив в другой массив как один элемент, оберните его в дополнительный слой массива, например:

'[1, 2]'::jsonb || jsonb_build_array('[3, 4]'::jsonb)[1, 2, [3, 4]]

jsonb - textjsonb

Удаляет ключ (и его значение) из JSON-объекта или соответствующие строковые значения из JSON-массива.

'{"a": "b", "c": "d"}'::jsonb - 'a'{"c": "d"}

'["a", "b", "c", "b"]'::jsonb - 'b'["a", "c"]

jsonb - text[]jsonb

Удаляет все совпадающие ключи или элементы массива из левого операнда.

'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]{}

jsonb - integerjsonb

Удаляет элемент массива с указанным индексом (отрицательные целые числа считаются с конца). Выдает ошибку, если значение JSON не является массивом.

'["a", "b"]'::jsonb - 1 ["a"]

jsonb #- text[]jsonb

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

'["a", {"b":1}]'::jsonb #- '{1,b}'["a", {}]

jsonb @? jsonpathboolean

Возвращает ли JSON путь какой-либо элемент для указанного значения JSON? (Это полезно только с SQL-стандартными выражениями JSON пути, а не выражениями проверки предикатов, так как те всегда возвращают значение.)

'{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'t

jsonb @@ jsonpathboolean

Возвращает результат проверки предиката JSON path для указанного JSON значения. (Это полезно только с выражениями проверки предикатов, а не с JSON path выражениями стандарта SQL, так как вернет NULL, если результат пути не является единственным логическим значением.)

'{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2't


Примечание

Операторы jsonpath @? и @@ подавляют следующие ошибки: отсутствие поля объекта или элемента массива, неожиданный тип элемента JSON, ошибки даты и числа. Описанные ниже функции, связанные с jsonpath, также могут быть настроены на подавление этих типов ошибок. Это поведение может быть полезным при поиске коллекций JSON-документов с различной структурой.

Таблица 9.47 показывает функции, которые доступны для создания значений json и jsonb. Некоторые функции в этой таблице имеют клаузу RETURNING, которая указывает возвращаемый тип данных. Он должен быть одним из json, jsonb, bytea, строкового типа символов (text, char или varchar), или типа, который может быть приведен к json. По умолчанию возвращается тип json.

Таблица 9.47. Функции создания JSON

Функция

Описание

Пример(ы)

to_json ( anyelement ) → json

to_jsonb ( anyelement ) → jsonb

Преобразует любое SQL значение в json или jsonb. Массивы и составные типы рекурсивно преобразуются в массивы и объекты (многомерные массивы становятся массивами массивов в JSON). В противном случае, если существует приведение типов из SQL типа данных в json, будет использована функция приведения типов для выполнения преобразования; [a] в противном случае будет создано скалярное значение JSON. Для любого скаляра, кроме числа, логического значения или значения null, будет использована текстовая представление, с необходимым экранированием, чтобы сделать его допустимым значением строки JSON.

to_json('Fred said "Hi."'::text)"Fred said \"Hi.\""

to_jsonb(row(42, 'Fred said "Hi."'::text)){"f1": 42, "f2": "Fred said \"Hi.\""}

array_to_json ( anyarray [, boolean ] ) → json

Преобразует SQL-массив в JSON-массив. Поведение аналогично функции to_json, за исключением того, что между элементами массива верхнего уровня будут добавлены переводы строк, если опциональный логический параметр истинный.

array_to_json('{{1,5},{99,100}}'::int[])[[1,5],[99,100]]

json_array ( [{ value_expression [ FORMAT JSON ] } [, ...]] [ { NULL | ABSENT } ON NULL ] [ RETURNING data_type [FORMAT JSON [ ENCODING UTF8 ]] ])

json_array ( [ query_expression ] [ RETURNING data_type [FORMAT JSON [ ENCODING UTF8 ]] ])

Создает JSON массив либо из серии параметров value_expression, либо из результатов query_expression, который должен быть запросом SELECT, возвращающим один столбец. Если указано ABSENT ON NULL, значения NULL игнорируются. Это всегда так, если используется query_expression.

json_array(1,true,json '{"a":null}')[1, true, {"a":null}]

json_array(SELECT * FROM (VALUES(1),(2)) t)[1, 2]

row_to_json ( record [, boolean ] ) → json

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

row_to_json(row(1,'foo')){"f1":1,"f2":"foo"}

json_build_array ( VARIADIC "any" ) → json

jsonb_build_array ( VARIADIC "any" ) → jsonb

Создает, возможно, гетерогенный JSON-массив из списка аргументов переменной длины. Каждый аргумент преобразуется согласно функции to_json или to_jsonb.

json_build_array(1, 2, 'foo', 4, 5)[1, 2, "foo", 4, 5]

json_build_object ( VARIADIC "any" ) → json

jsonb_build_object ( VARIADIC "any" ) → jsonb

Создает JSON-объект из списка переменных аргументов. По соглашению, список аргументов состоит из чередующихся ключей и значений. Ключевые аргументы приводятся к типу text; аргументы значений преобразуются согласно функциям to_json или to_jsonb.

json_build_object('foo', 1, 2, row(3,'bar')){"foo" : 1, "2" : {"f1":3,"f2":"bar"}}

json_object ( [{ key_expression { VALUE | ':' } value_expression}[FORMAT JSON [ ENCODING UTF8 ]]}[, ...]] [ { NULL | ABSENT } ON NULL ] [{ WITH | WITHOUT } UNIQUE [ KEYS ]] [ RETURNING data_type [FORMAT JSON [ ENCODING UTF8 ]] ])

Создает JSON-объект из всех данных пар ключ/значение или пустой объект, если пары не заданы. key_expression — это скалярное выражение, определяющее ключ JSON, который преобразуется в тип text. Он не может быть NULL и не может принадлежать к типу, который имеет приведение к типу json. Если указано WITH UNIQUE KEYS, не должно быть никаких дублирующихся key_expression. Любая пара, для которой value_expression оценивается как NULL, исключается из вывода, если указано ABSENT ON NULL; если указано NULL ON NULL или этот пункт опущен, ключ включается со значением NULL.

json_object('code' VALUE 'P123', 'title': 'Челюсти'){"code" : "P123", "title" : "Jaws"}

json_object ( text[] ) → json

jsonb_object ( text[] ) → jsonb

Создает JSON-объект из текстового массива. Массив должен иметь либо ровно одно измерение с четным количеством элементов, в этом случае они рассматриваются как чередующиеся пары ключ/значение, либо два измерения, так что каждый внутренний массив имеет ровно два элемента, которые рассматриваются как пара ключ/значение. Все значения преобразуются в JSON-строки.

json_object('{a, 1, b, "def", c, 3.5}'){"a" : "1", "b" : "def", "c" : "3.5"}

json_object('{{a, 1}, {b, "def"}, {c, 3.5}}'){"a" : "1", "b" : "def", "c" : "3.5"}

json_object ( keys text[], values text[] ) → json

jsonb_object ( keys text[], values text[] ) → jsonb

Эта форма функции json_object принимает ключи и значения парами из отдельных текстовых массивов. В остальном она идентична форме с одним аргументом.

json_object('{a,b}', '{1,2}'){"a": "1", "b": "2"}

json ( выражение [FORMAT JSON [ ENCODING UTF8 ]] [{ WITH | WITHOUT } UNIQUE [ KEYS ]] ) → json

Преобразует заданное выражение, указанное как строка типа text или bytea (в кодировке UTF8), в значение JSON. Если expression имеет значение NULL, возвращается значение NULL SQL. Если указано WITH UNIQUE, то expression не должно содержать дублирующихся ключей объекта.

json('{"a":123, "b":[true,"foo"], "a":"bar"}'){"a":123, "b":[true,"foo"], "a":"bar"}

json_scalar ( expression )

Преобразует заданное скалярное значение SQL в скалярное значение JSON. Если входное значение NULL, возвращается SQL null. Если входное значение является числом или логическим значением, возвращается соответствующее число или логическое значение JSON. Для любого другого значения возвращается строка JSON.

json_scalar(123.45)123.45

json_scalar(CURRENT_TIMESTAMP)"2022-05-10T10:51:04.62128-04:00"

json_serialize ( выражение [FORMAT JSON [ ENCODING UTF8 ]] [ RETURNING data_type [FORMAT JSON [ ENCODING UTF8 ]] ] )

Преобразует SQL/JSON выражение в строку символов или двоичную строку. выражение может быть любого типа JSON, любого типа строк символов или bytea в кодировке UTF8. Возвращаемый тип, используемый в RETURNING, может быть любого типа строк символов или bytea. По умолчанию это text.

json_serialize('{ "a" : 1 } ' RETURNING bytea)\x7b20226122203a2031207d20

[a] Например, расширение hstore имеет приведение типа из hstore в json, так что значения hstore, преобразованные с помощью функций создания JSON, будут представлены в виде объектов JSON, а не примитивных строковых значений.


Таблица 9.48 описывает возможности SQL/JSON для тестирования JSON.

Таблица 9.48. Функции тестирования SQL/JSON

Сигнатура функции

Описание

Пример(ы)

expression IS [ NOT ] JSON [ { VALUE | SCALAR | ARRAY | OBJECT } ] [{ WITH | WITHOUT } UNIQUE [ KEYS ]]

Этот предикат проверяет, может ли expression быть разобран как JSON, возможно, указанного типа. Если указано SCALAR или ARRAY или OBJECT, то проверка заключается в том, является ли JSON этого конкретного типа. Если указано WITH UNIQUE KEYS, то любой объект в expression также проверяется на наличие дублирующихся ключей.

SELECT js,
  js IS JSON "json?",
  js IS JSON SCALAR "scalar?",
  js IS JSON OBJECT "object?",
  js IS JSON ARRAY "array?"
FROM (VALUES
      ('123'), ('"abc"'), ('{"a": "b"}'), ('[1,2]'),('abc')) foo(js);
     js     | json? | scalar? | object? | array?
------------+-------+---------+---------+--------
 123        | t     | t       | f       | f
 "abc"      | t     | t       | f       | f
 {"a": "b"} | t     | f       | t       | f
 [1,2]      | t     | f       | f       | t
 abc        | f     | f       | f       | f

SELECT js,
  js IS JSON OBJECT "object?",
  js IS JSON ARRAY "array?",
  js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
  js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
FROM (VALUES ('[{"a":"1"},
 {"b":"2","b":"3"}]')) foo(js);
-[ RECORD 1 ]-+--------------------
js            | [{"a":"1"},        +
              |  {"b":"2","b":"3"}]
object?       | f
array?        | t
array w. UK?  | f
array w/o UK? | t


Таблица 9.49 показывает функции, доступные для обработки значений json и jsonb.

Таблица 9.49. Функции обработки JSON

Функция

Описание

Пример(ы)

json_array_elements ( json ) → setof json

jsonb_array_elements ( jsonb ) → setof jsonb

Расширяет верхний уровень JSON-массива в набор JSON-значений.

select * from json_array_elements('[1,true, [2,false]]')

   value
-----------
 1
 true
 [2,false]

json_array_elements_text ( json ) → setof text

jsonb_array_elements_text ( jsonb ) → setof text

Расширяет верхний уровень JSON-массива в набор значений text.

select * from json_array_elements_text('["foo", "bar"]')

   value
-----------
 foo
 bar

json_array_length ( json ) → integer

jsonb_array_length ( jsonb ) → integer

Возвращает количество элементов в массиве JSON верхнего уровня.

json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')5

jsonb_array_length('[]')0

json_each ( json ) → setof record ( key text, value json )

jsonb_each ( jsonb ) → setof record ( key text, value jsonb )

Расширяет верхний уровень JSON-объекта в набор пар ключ/значение.

select * from json_each('{"a":"foo", "b":"bar"}')

 key | value
-----+-------
 a   | "foo"
 b   | "bar"

json_each_text ( json ) → setof record ( key text, value text )

jsonb_each_text ( jsonb ) → setof record ( key text, value text )

Расширяет верхний уровень JSON-объекта в набор пар ключ/значение. Возвращаемые значения value будут иметь тип text.

select * from json_each_text('{"a":"foo", "b":"bar"}')

 key | value
-----+-------
 a   | foo
 b   | bar

json_extract_path ( from_json json, VARIADIC path_elems text[] ) → json

jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb

Извлекает подобъект JSON по указанному пути. (Это функционально эквивалентно оператору #>, но запись пути в виде вариативного списка может быть удобнее в некоторых случаях).

json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')"foo"

json_extract_path_text ( from_json json, VARIADIC path_elems text[] ) → text

jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text

Извлекает подобъект JSON по указанному пути в виде текста. (Это функционально эквивалентно оператору #>>).

json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')foo

json_object_keys ( json ) → setof text

jsonb_object_keys ( jsonb ) → setof text

Возвращает набор ключей в объекте JSON верхнего уровня.

select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')

 json_object_keys
------------------
 f1
 f2

json_populate_record ( base anyelement, from_json json ) → anyelement

jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement

Расширяет объект JSON верхнего уровня до строки, имеющей составной тип аргумента base. Объект JSON сканируется на наличие полей, имена которых совпадают с именами столбцов выходной строки, и их значения вставляются в эти столбцы выхода. (Поля, не соответствующие ни одному имени столбца выхода, игнорируются). В типичном использовании значение base просто NULL, что означает, что любые столбцы выхода, не соответствующие ни одному полю объекта, будут заполнены значениями null. Однако, если base не является NULL, то значения, содержащиеся в нем, будут использоваться для несоответствующих столбцов.

Для преобразования значения JSON в SQL тип выходного столбца применяются следующие правила последовательно:

  • Значение JSON null преобразуется в SQL null во всех случаях.

  • Если выходной столбец имеет тип json или jsonb, JSON-значение просто воспроизводится точно так же.

  • Если выходной столбец является составным (строковым) типом, а значение JSON является объектом JSON, поля объекта преобразуются в столбцы выходного типа строки путем рекурсивного применения этих правил.

  • Точно так же, если выходной столбец является массивным типом, а значение JSON - это JSON-массив, элементы JSON-массива преобразуются в элементы выходного массива путем рекурсивного применения этих правил.

  • В противном случае, если значение JSON является строкой, содержимое строки передается функции преобразования ввода для типа данных столбца.

  • В противном случае, обычное текстовое представление значения JSON передается в функцию преобразования ввода для типа данных столбца.

В то время как приведенный ниже пример использует постоянное значение JSON, типичное использование будет заключаться в ссылке на столбец json или jsonb боковым образом из другой таблицы в предложении FROM запроса. Запись json_populate_record в предложении FROM является хорошей практикой, поскольку все извлеченные столбцы доступны для использования без дублирующих вызовов функций.

create type subrowtype as (d int, e text); create type myrowtype as (a int, b text[], c subrowtype);

select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}, "x": "foo"}')

 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")

jsonb_populate_record_valid ( base anyelement, from_json json ) → boolean

Функция для тестирования jsonb_populate_record. Возвращает true, если ввод jsonb_populate_record завершится без ошибки для данного входного JSON объекта; то есть, это допустимый ввод, false в противном случае.

создать тип jsb_char2 как (a char(2));

select jsonb_populate_record_valid(NULL::jsb_char2, '{"a": "aaa"}');

 jsonb_populate_record_valid
-----------------------------
 f
(1 row)

select * from jsonb_populate_record(NULL::jsb_char2, '{"a": "aaa"}') q;

ERROR:  value too long for type character(2)

select jsonb_populate_record_valid(NULL::jsb_char2, '{"a": "aa"}');

 jsonb_populate_record_valid
-----------------------------
 t
(1 row)

select * from jsonb_populate_record(NULL::jsb_char2, '{"a": "aa"}') q;

 a
----
 aa
(1 row)

json_populate_recordset ( base anyelement, from_json json ) → setof anyelement

jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof anyelement

Расширяет верхний уровень JSON-массива объектов до набора строк, имеющих составной тип аргумента base. Каждый элемент JSON-массива обрабатывается, как описано выше для json[b]_populate_record.

create type twoints as (a int, b int);

select * from json_populate_recordset(null::twoints, '[{"a":1,"b":2}, {"a":3,"b":4}]')

 a | b
---+---
 1 | 2
 3 | 4

json_to_record ( json ) → record

jsonb_to_record ( jsonb ) → record

Расширяет объект JSON верхнего уровня до строки, имеющей составной тип, определенный с помощью предложения AS. (Как и все функции, возвращающие record, вызывающий запрос должен явно определить структуру записи с помощью предложения AS). Выходная запись заполняется из полей объекта JSON таким же образом, как описано выше для json[b]_populate_record. Поскольку нет значения входной записи, несоответствующие столбцы всегда заполняются значениями null.

create type myrowtype as (a int, b text);

select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)

 a |    b    |    c    | d |       r
---+---------+---------+---+---------------
 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")

json_to_recordset ( json ) → setof record

jsonb_to_recordset ( jsonb ) → setof record

Расширяет верхний уровень JSON-массива объектов до набора строк, имеющих составной тип, определенный с помощью предложения AS. (Как и с любыми функциями, возвращающими record, вызывающий запрос должен явно определить структуру записи с помощью предложения AS). Каждый элемент JSON-массива обрабатывается как описано выше для json[b]_populate_record.

select * from json_to_recordset('[{"a":1,"b":"foo"}, {"a":"2","c":"bar"}]') as x(a int, b text)

 a |  b
---+-----
 1 | foo
 2 |

jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ) → jsonb

Возвращает target с элементом, обозначенным path, замененным на new_value, или с добавленным new_value, если create_if_missing истинно (что является значением по умолчанию), и элемент, обозначенный path, не существует. Все предыдущие шаги в пути должны существовать, иначе target возвращается без изменений. Как и в случае с операторами, ориентированными на путь, отрицательные целые числа, которые появляются в path, считаются с конца массивов JSON. Если последний шаг пути является индексом массива, выходящим за пределы диапазона, и create_if_missing истинно, новое значение добавляется в начало массива, если индекс отрицательный, или в конец массива, если он положительный.

jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', '[2,3,4]', false)[{"f1": [2, 3, 4], "f2": null}, 2, null, 3]

jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}', '[2,3,4]')[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]

jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text ]] ) → jsonb

Если new_value не равно NULL, поведение идентично функции jsonb_set. В противном случае поведение определяется значением параметра null_value_treatment, который должен быть одним из значений 'raise_exception', 'use_json_null', 'delete_key' или 'return_target'. По умолчанию используется значение 'use_json_null'.

jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', null)[{"f1": null, "f2": null}, 2, null, 3]

jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target')[{"f1": 99, "f2": null}, 2]

jsonb_insert ( target jsonb, path text[], new_value jsonb [, insert_after boolean ] ) → jsonb

Возвращает target с вставленным new_value. Если элемент, обозначенный path, является элементом массива, new_value будет вставлен перед этим элементом, если insert_after равно false (что является значением по умолчанию), или после него, если insert_after равно true. Если элемент, обозначенный path, является полем объекта, new_value будет вставлен только в том случае, если объект еще не содержит этот ключ. Все предыдущие шаги в пути должны существовать, иначе target возвращается без изменений. Как и в случае с операторами, ориентированными на путь, отрицательные целые числа, которые появляются в path, считаются с конца массивов JSON. Если последний шаг пути является индексом массива, выходящим за пределы диапазона, новое значение добавляется в начало массива, если индекс отрицательный, или в конец массива, если он положительный.

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"'){"a": [0, "new_value", 1, 2]}

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true){"a": [0, 1, "new_value", 2]}

json_strip_nulls ( json ) → json

jsonb_strip_nulls ( jsonb ) → jsonb

Удаляет все поля объекта, которые имеют значение null из заданного JSON значения, рекурсивно. Значения null, которые не являются полями объекта, остаются нетронутыми.

json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]')[{"f1":1},2,null,3]

jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

Проверяет, возвращает ли JSON путь какой-либо элемент для указанного JSON значения. (Это полезно только с JSON-путями в формате SQL-стандарта, а не выражениями проверки предикатов, так как те всегда возвращают значение.) Если аргумент vars указан, он должен быть JSON-объектом, и его поля предоставляют именованные значения для подстановки в выражение jsonpath. Если аргумент silent указан и равен true, функция подавляет те же ошибки, что и операторы @? и @@.

jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')t

jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

Возвращает SQL-логический результат проверки предиката JSON path для указанного значения JSON. (Это полезно только с выражениями проверки предикатов, а не с JSON path выражениями по стандарту SQL, так как в противном случае это либо завершится с ошибкой, либо вернет NULL, если результат пути не является единственным логическим значением.) Необязательные аргументы vars и silent действуют так же, как и для jsonb_path_exists.

jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2, "max":4}')t

jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

Возвращает все элементы JSON, возвращенные JSON-путем для указанного JSON-значения. Для стандартных SQL выражений JSON-путей возвращает JSON значения, выбранные из target. Для выражений проверки предикатов возвращает результат проверки предиката: true, false, или null. Необязательные аргументы vars и silent действуют так же, как для jsonb_path_exists.

select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')

 jsonb_path_query
------------------
 2
 3
 4

jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

Возвращает все элементы JSON, возвращенные JSON-путем для указанного JSON-значения, в виде JSON-массива. Параметры такие же, как и для jsonb_path_query.

jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')[2, 3, 4]

jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

Возвращает первый элемент JSON, возвращаемый JSON-путем для указанного JSON-значения, или NULL, если результатов нет. Параметры такие же, как для jsonb_path_query.

jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')2

jsonb_path_exists_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

jsonb_path_match_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

jsonb_path_query_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

jsonb_path_query_array_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

jsonb_path_query_first_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

Эти функции действуют так же, как и их аналоги, описанные выше без суффикса _tz, за исключением того, что эти функции поддерживают сравнения значений даты/времени, требующие преобразования с учетом часового пояса. В приведенном ниже примере требуется интерпретация значения только даты 2015-08-02 как метки времени с учетом часового пояса, поэтому результат зависит от текущей настройки TimeZone. Из-за этой зависимости эти функции помечены как стабильные, что означает, что они не могут использоваться в индексах. Их аналоги являются постоянными, поэтому их можно использовать в индексах; но они будут выдавать ошибки, если попросят выполнить такие сравнения.

jsonb_path_exists_tz('["2015-08-01 12:00:00-05"]', '$[*] ? (@.datetime() < "2015-08-02".datetime())')t

jsonb_pretty ( jsonb ) → text

Преобразует заданное значение JSON в красиво отформатированный текст с отступами.

jsonb_pretty('[{"f1":1,"f2":null}, 2]')

[
    {
        "f1": 1,
        "f2": null
    },
    2
]

json_typeof ( json ) → text

jsonb_typeof ( jsonb ) → text

Возвращает тип верхнего уровня значения JSON в виде текстовой строки. Возможные типы: object, array, string, number, boolean и null. (Результат null не следует путать с SQL NULL; см. примеры).

json_typeof('-123.4')number

json_typeof('null'::json)null

json_typeof(NULL::json) IS NULLt


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.)

Чтобы получить доступные сегменты трека, вам нужно использовать оператор доступа .key для спуска через окружающие 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. Выражение фильтра начинается с вопросительного знака и содержит условие в скобках:

? (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 Операторы и методы

Оператор/Метод

Описание

Пример(ы)

number + numbernumber

Дополнение

jsonb_path_query('[2]', '$[0] + 3')5

+ numbernumber

Унарный плюс (без операции); в отличие от сложения, он может выполняться для нескольких значений

jsonb_path_query_array('{"x": [2,3,4]}', '+ $.x')[2, 3, 4]

number - numbernumber

Вычитание

jsonb_path_query('[2]', '7 - $[0]')5

- numbernumber

Отрицание; в отличие от вычитания, это может выполняться для нескольких значений

jsonb_path_query_array('{"x": [2,3,4]}', '- $.x')[-2, -3, -4]

number * numbernumber

Умножение

jsonb_path_query('[4]', '2 * $[0]')8

number / numbernumber

Раздел

jsonb_path_query('[8.5]', '$[0] / 2')4.2500000000000000

number % numbernumber

Модуло (остаток)

jsonb_path_query('[32]', '$[0] % 10')2

value . type()string

Тип элемента JSON (см. функцию json_typeof)

jsonb_path_query_array('[1, "2", {}]', '$[*].type()')["number", "string", "object"]

value . size()number

Размер элемента JSON (количество элементов массива или 1, если это не массив)

jsonb_path_query('{"m": [11, 15]}', '$.m.size()')2

value . boolean()boolean

Логическое значение, преобразованное из JSON логического, числового значения или строки

jsonb_path_query_array('[1, "yes", false]', '$[*].boolean()')[true, true, false]

value . string()string

Строковое значение, преобразованное из JSON логического значения, числа, строки или даты и времени

jsonb_path_query_array('[1.23, "xyz", false]', '$[*].string()')["1.23", "xyz", "false"]

jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()')"2023-08-15T12:34:56"

value . double()number

Приблизительное число с плавающей запятой, преобразованное из числа или строки JSON

jsonb_path_query('{"len": "1.9"}', '$.len.double() * 2')3.8

number . ceiling()number

Ближайшее целое число, большее или равное заданному числу

jsonb_path_query('{"h": 1.3}', '$.h.ceiling()')2

number . floor()number

Ближайшее целое число, меньшее или равное данному числу

jsonb_path_query('{"h": 1.7}', '$.h.floor()')1

number . abs()number

Абсолютное значение данного числа

jsonb_path_query('{"z": -0.3}', '$.z.abs()')0.3

value . bigint()bigint

Большое целое число, преобразованное из JSON числа или строки

jsonb_path_query('{"len": "9876543219"}', '$.len.bigint()')9876543219

value . decimal( [ precision [ , scale ] ] )decimal

Округленное десятичное значение, преобразованное из числа или строки JSON (precision и scale должны быть целыми значениями)

jsonb_path_query('1234.5678', '$.decimal(6, 2)')1234.57

value . integer()integer

Целочисленное значение, преобразованное из числа или строки JSON

jsonb_path_query('{"len": "12345"}', '$.len.integer()')12345

value . number()numeric

Числовое значение, преобразованное из числа или строки JSON

jsonb_path_query('{"len": "123.45"}', '$.len.number()')123.45

string . datetime()datetime_type (см. примечание)

Значение даты/времени, преобразованное из строки

jsonb_path_query('["2015-8-1", "2015-08-12"]', '$[*] ? (@.datetime() < "2015-08-2".datetime())')"2015-8-1"

string . datetime(template)datetime_type (см. примечание)

Значение даты/времени, преобразованное из строки с использованием указанного шаблона to_timestamp

jsonb_path_query_array('["12:30", "18:40"]', '$[*].datetime("HH24:MI")')["12:30:00", "18:40:00"]

string . date()date

Значение даты, преобразованное из строки

jsonb_path_query('"2023-08-15"', '$.date()')"2023-08-15"

string . time()time without time zone

Значение времени без часового пояса, преобразованное из строки

jsonb_path_query('"12:34:56"', '$.time()')"12:34:56"

string . time(precision)time without time zone

Значение времени без часового пояса, преобразованное из строки, с дробными секундами, округленными до заданной точности

jsonb_path_query('"12:34:56.789"', '$.time(2)')"12:34:56.79"

string . time_tz()time with time zone

Значение времени с часовым поясом, преобразованное из строки

jsonb_path_query('"12:34:56 +05:30"', '$.time_tz()')"12:34:56+05:30"

string . time_tz(precision)time with time zone

Значение времени с часовым поясом, преобразованное из строки, с дробными секундами, округленными до заданной точности

jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2)')"12:34:56.79+05:30"

string . timestamp()timestamp without time zone

Значение временной метки без часового пояса, преобразованное из строки

jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp()')"2023-08-15T12:34:56"

string . timestamp(precision)timestamp without time zone

Значение временной метки без часового пояса, преобразованное из строки, с дробными секундами, приведенными к заданной точности

jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2)')"2023-08-15T12:34:56.79"

string . timestamp_tz()timestamp with time zone

Значение временной метки с часовым поясом, преобразованное из строки

jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()')"2023-08-15T12:34:56+05:30"

string . timestamp_tz(precision)timestamp with time zone

Значение временной метки с часовым поясом, преобразованное из строки, с дробными секундами, округленными до заданной точности

jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2)')"2023-08-15T12:34:56.79+05:30"

object . keyvalue()array

Массив пар ключ-значение объекта, представленный в виде массива объектов, содержащих три поля: "key", "value" и "id"; "id" является уникальным идентификатором объекта, к которому принадлежит пара ключ-значение.

jsonb_path_query_array('{"x": "20", "y": 32}', '$.keyvalue()')[{"id": 0, "key": "x", "value": "20"}, {"id": 0, "key": "y", "value": 32}]


Примечание

Тип результата методов datetime() и datetime(template) может быть date, timetz, time, timestamptz или timestamp. Оба метода определяют свой тип результата динамически.

Метод datetime() последовательно пытается сопоставить входную строку с форматами ISO для типов данных date, timetz, time, timestamptz и timestamp. Он останавливается на первом совпадающем формате и возвращает соответствующий тип данных.

Метод datetime(template) определяет тип результата в соответствии с полями, используемыми в предоставленной строке шаблона.

Методы datetime() и datetime(template) используют те же правила разбора, что и функция SQL to_timestamp (см. Раздел 9.8), с тремя исключениями. Во-первых, эти методы не позволяют несоответствующие шаблону образцы. Во-вторых, в строке шаблона разрешены только следующие разделители: знак минуса, точка, косая черта (слэш), запятая, апостроф, точка с запятой, двоеточие и пробел. В-третьих, разделители в строке шаблона должны точно соответствовать входной строке.

Если необходимо сравнить разные типы даты/времени, применяется неявное приведение. Значение date может быть приведено к timestamp или timestamptz, timestamp может быть приведено к timestamptz, а time к timetz. Однако все, кроме первого из этих преобразований, зависят от текущей настройки TimeZone, и, следовательно, могут выполняться только в функциях jsonpath, учитывающих часовой пояс. Аналогично, другие методы, связанные с датой/временем, которые преобразуют строки в типы даты/времени, также выполняют это приведение, что может включать текущую настройку TimeZone. Поэтому эти преобразования также могут выполняться только в функциях jsonpath, учитывающих часовой пояс.

Таблица 9.51 показывает доступные элементы выражения фильтра.

Таблица 9.51. jsonpath Элементы выражения фильтрации

Предикат/Значение

Описание

Пример(ы)

value == valueboolean

Сравнение на равенство (этот оператор, а также другие операторы сравнения, работают со всеми скалярными значениями JSON)

jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == 1)')[1, 1]

jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == "a")')["a"]

value != valueboolean

value <> valueboolean

Сравнение неравенства

jsonb_path_query_array('[1, 2, 1, 3]', '$[*] ? (@ != 1)')[2, 3]

jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <> "b")')["a", "c"]

value < valueboolean

Сравнение меньше чем

jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ < 2)')[1]

value <= valueboolean

Сравнение "меньше или равно"

jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <= "b")')["a", "b"]

value > valueboolean

Сравнение больше чем

jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ > 2)')[3]

value >= valueboolean

Сравнение "больше или равно"

jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ >= 2)')[2, 3]

trueboolean

Константа JSON true

jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == true)'){"name": "Chris", "parent": true}

falseboolean

JSON константа false

jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == false)'){"name": "John", "parent": false}

nullvalue

JSON константа null (обратите внимание, что, в отличие от SQL, сравнение с null работает нормально)

jsonb_path_query('[{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}]', '$[*] ? (@.job == null) .name')"Mary"

boolean && booleanboolean

Логическое И

jsonb_path_query('[1, 3, 7]', '$[*] ? (@ > 1 && @ < 5)')3

boolean || booleanboolean

Логическое ИЛИ

jsonb_path_query('[1, 3, 7]', '$[*] ? (@ < 1 || @ > 5)')7

! booleanboolean

Логическое НЕТ

jsonb_path_query('[1, 3, 7]', '$[*] ? (!(@ < 5))')7

boolean is unknownboolean

Проверяет, является ли логическое условие unknown.

jsonb_path_query('[-1, 2, 7, "foo"]', '$[*] ? ((@ > 0) is unknown)')"foo"

string like_regex string [ flag string ] → boolean

Проверяет, соответствует ли первый операнд регулярному выражению, заданному вторым операндом, с возможными модификациями, описанными строкой символов flag (см. Раздел 9.16.2.4).

jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[*] ? (@ like_regex "^ab.*c")')["abc", "abdacb"]

jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[*] ? (@ like_regex "^ab.*c" flag "i")')["abc", "aBdC", "abdacb"]

string starts with stringboolean

Проверяет, является ли второй операнд начальным подстрокой первого операнда.

jsonb_path_query('["John Smith", "Mary Stone", "Bob Johnson"]', '$[*] ? (@ starts with "John")')"John Smith"

exists ( path_expression )boolean

Проверяет, соответствует ли выражение пути хотя бы одному элементу SQL/JSON. Возвращает unknown, если выражение пути приведет к ошибке; во втором примере это используется для избежания ошибки "нет такого ключа" в строгом режиме.

jsonb_path_query('{"x": [1, 2], "y": [2, 4]}', 'strict $.* ? (exists (@ ? (@[*] > 2)))')[2, 4]

jsonb_path_query_array('{"value": 41}', 'strict $ ? (exists (@.name)) .name')[]


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 запроса

Сигнатура функции

Описание

Пример(ы)

JSON_EXISTS (
context_item, path_expression
[ PASSING { value AS varname } [, ...]]
[{ TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]) → boolean

  • Возвращает true, если SQL/JSON path_expression примененный к context_item дает какие-либо элементы, в противном случае false.

  • Клаузула ON ERROR определяет поведение в случае, если возникает ошибка во время оценки path_expression. Указание ERROR приведет к выбросу ошибки с соответствующим сообщением. Другие варианты включают возврат значений boolean FALSE или TRUE или значения UNKNOWN, которое фактически является SQL NULL. Значение по умолчанию, если клаузула ON ERROR не указана, - это возврат значения boolean FALSE.

Примеры:

JSON_EXISTS(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > $x)' PASSING 2 AS x)t

JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ОШИБКА ПРИ ОШИБКЕ)f

JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR)

ERROR:  jsonpath array subscript is out of bounds

JSON_QUERY (
context_item, path_expression
[ PASSING { value AS varname } [, ...]]
[ RETURNING data_type [FORMAT JSON [ ENCODING UTF8 ]] ]
[{ БЕЗ | С { УСЛОВНЫЙ | [БЕЗУСЛОВНЫЙ] } } [ МАССИВ ] ОБЕРТКА]
[{ СОХРАНИТЬ | УДАЛИТЬ } КАВЫЧКИ [ НА СКАЛЯРНОЙ СТРОКЕ ]]
[{ ОШИБКА | NULL | ПУСТОЙ { [ Массив ] | ОБЪЕКТ } | ПО УМОЛЧАНИЮ выражение } ПРИ ПУСТОМ]
[{ ОШИБКА | NULL | ПУСТОЙ { [ Массив ] | ОБЪЕКТ } | ПО УМОЛЧАНИЮ выражение } ПРИ ОШИБКЕ]) → jsonb

  • Возвращает результат применения SQL/JSON path_expression к context_item.

  • По умолчанию результат возвращается как значение типа jsonb, хотя предложение RETURNING может быть использовано для возврата в другом типе, к которому он может быть успешно приведен.

  • Если выражение пути может возвращать несколько значений, может потребоваться обернуть эти значения, используя клаузу WITH WRAPPER, чтобы сделать его допустимой строкой JSON, потому что поведение по умолчанию заключается в том, чтобы не оборачивать их, как если бы было указано WITHOUT WRAPPER. Клауза WITH WRAPPER по умолчанию принимается как WITH UNCONDITIONAL WRAPPER, что означает, что даже одно значение результата будет обернуто. Чтобы применять обертку только при наличии нескольких значений, укажите WITH CONDITIONAL WRAPPER. Получение нескольких значений в результате будет рассматриваться как ошибка, если указано WITHOUT WRAPPER.

  • Если результат является скалярной строкой, по умолчанию возвращаемое значение будет окружено кавычками, что делает его допустимым значением JSON. Это можно сделать явным, указав KEEP QUOTES. Напротив, кавычки можно опустить, указав OMIT QUOTES. Чтобы гарантировать, что результат является допустимым значением JSON, OMIT QUOTES не может быть указано, когда также указано WITH WRAPPER.

  • Клаузула ON EMPTY определяет поведение, если вычисление path_expression приводит к пустому множеству. Клаузула ON ERROR определяет поведение в случае возникновения ошибки при вычислении path_expression, при приведении значения результата к типу RETURNING, или при вычислении выражения ON EMPTY, если вычисление path_expression возвращает пустое множество.

  • Для обоих ON EMPTY и ON ERROR, указание ERROR вызовет ошибку с соответствующим сообщением. Другие варианты включают возврат SQL NULL, пустого массива (EMPTY [ARRAY]), пустого объекта (EMPTY OBJECT), или пользовательского выражения (DEFAULT expression), которое может быть приведено к jsonb или типу, указанному в RETURNING. Значение по умолчанию, когда ON EMPTY или ON ERROR не указано, — это возврат значения SQL NULL.

Примеры:

JSON_QUERY(jsonb '[1,[2,3],null]', 'lax $[*][$off]' PASSING 1 AS off WITH CONDITIONAL WRAPPER)3

JSON_QUERY(jsonb '{"a": "[1, 2]"}', 'lax $.a' OMIT QUOTES)[1, 2]

JSON_QUERY(jsonb '{"a": "[1, 2]"}', 'lax $.a' RETURNING int[] OMIT QUOTES ERROR ON ERROR)

ERROR:  malformed array literal: "[1, 2]"
DETAIL:  Missing "]" after array dimensions.

JSON_VALUE (
context_item, path_expression
[ PASSING { value AS varname } [, ...]]
[ RETURNING data_type ]
[ { ERROR | NULL | DEFAULT expression } ON EMPTY ]
[ { ERROR | NULL | DEFAULT expression } ON ERROR ]) → text

  • Возвращает результат применения SQL/JSON path_expression к context_item.

  • Используйте JSON_VALUE() только в том случае, если извлеченное значение ожидается как один скалярный элемент SQL/JSON; получение нескольких значений будет рассматриваться как ошибка. Если вы ожидаете, что извлеченное значение может быть объектом или массивом, используйте функцию JSON_QUERY вместо этого.

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

  • Клаузулы ON ERROR и ON EMPTY имеют схожую семантику, как упомянуто в описании JSON_QUERY, за исключением того, что набор значений, возвращаемых вместо выброса ошибки, отличается.

  • Обратите внимание, что скалярные строки, возвращаемые JSON_VALUE, всегда имеют удаленные кавычки, что эквивалентно указанию OMIT QUOTES в JSON_QUERY.

Примеры:

JSON_VALUE(jsonb '"123.45"', '$' RETURNING float)123.45

JSON_VALUE(jsonb '"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date)2015-02-01

JSON_VALUE(jsonb '[1,2]', 'strict $[$off]' PASSING 1 as off)2

JSON_VALUE(jsonb '[1,2]', 'strict $[*]' DEFAULT 9 ON ERROR)9


Примечание

Выражение 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 [ AS json_path_name ] [PASSING { значение AS имя_переменной } [, ...]]
    COLUMNS ( json_table_column [, ...] )
    [{ ОШИБКА | ПУСТОЙ [МАССИВ]} ПРИ ОШИБКЕ]
)


where json_table_column is:

  name FOR ORDINALITY
  | name type
        [FORMAT JSON [КОДИРОВКА UTF8]]
        [ PATH path_expression ]
        [{ БЕЗ | С { УСЛОВНЫМ | [БЕЗУСЛОВНЫМ] } } [ МАССИВНЫМ ] ОБОЛОЧКОЙ]
[{ СОХРАНИТЬ | ИСКЛЮЧИТЬ } КАВЫЧКИ [ НА СКАЛЯРНОЙ СТРОКЕ ]]
[{ ОШИБКА | NULL | ПУСТОЙ { [Массив] | ОБЪЕКТ } | ПО УМОЛЧАНИЮ выражение } ПРИ ПУСТОМ]
[{ ОШИБКА | NULL | ПУСТОЙ { [Массив] | ОБЪЕКТ } | ПО УМОЛЧАНИЮ выражение } ПРИ ОШИБКЕ]
  | name type EXISTS [ PATH path_expression ]
        [ { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR ]
  | NESTED [ PATH ] path_expression [ AS json_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)