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

9.16. Функции и операторы JSON
Назад	Наверх	Глава 9. Функции и операторы	Начало	Далее

---

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

9.16.1. Обработка и создание данных JSON

9.16.2. Язык SQL/JSON Path

Этот раздел описывает:

• функции и операторы для обработки и создания данных 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 -> integer → json jsonb -> integer → jsonb Извлекает n-й элемент массива JSON (элементы массива нумеруются с нуля, но отрицательные целые числа считаются с конца). '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> 2 → {"c":"baz"} '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> -3 → {"a":"foo"}
json -> text → json jsonb -> text → jsonb Извлекает поле объекта JSON с заданным ключом. '{"a": {"b":"foo"}}'::json -> 'a' → {"b":"foo"}
json ->> integer → text jsonb ->> integer → text Извлекает n-й элемент массива JSON в виде текста. '[1,2,3]'::json ->> 2 → 3
json ->> text → text jsonb ->> text → text Извлекает поле объекта 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 @> jsonb → boolean Содержит ли первое значение JSON второе? (См. Раздел 8.14.3 для получения подробной информации о содержании). '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb → t
jsonb <@ jsonb → boolean Содержится ли первое значение JSON во втором? '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb → t
jsonb ? text → boolean Существует ли текстовая строка в качестве ключа верхнего уровня или элемента массива в значении 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 || jsonb → jsonb Конкатенирует два значения 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 - text → jsonb Удаляет ключ (и его значение) из 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 - integer → jsonb Удаляет элемент массива с указанным индексом (отрицательные целые числа считаются с конца). Выдает ошибку, если значение JSON не является массивом. '["a", "b"]'::jsonb - 1 → ["a"]
jsonb #- text[] → jsonb Удаляет поле или элемент массива по указанному пути, где элементы пути могут быть ключами полей или индексами массива. '["a", {"b":1}]'::jsonb #- '{1,b}' → ["a", {}]
jsonb @? jsonpath → boolean Возвращает ли JSON-путь какой-либо элемент для указанного значения JSON? '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' → t
jsonb @@ jsonpath → boolean Возвращает результат проверки предиката JSON-пути для указанного значения JSON. Учитывается только первый элемент результата. Если результат не является логическим значением, то возвращается NULL. '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' → t

Примечание

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

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

Таблица 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]]
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 ( 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"}
[a] Например, расширение hstore имеет приведение типа из hstore в json, так что значения hstore, преобразованные с помощью функций создания JSON, будут представлены в виде объектов JSON, а не примитивных строковых значений.

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

Таблица 9.48. Функции обработки 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")
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-значения. Если указан аргумент 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 Возвращает результат проверки предиката JSON-пути для указанного значения JSON. Учитывается только первый элемент результата. Если результат не является логическим значением, то возвращается 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-значения. Необязательные аргументы 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-массива. Необязательные аргументы vars и silent действуют так же, как и для функции jsonb_path_exists. 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, если результатов нет. Опциональные аргументы vars и silent действуют так же, как для функции jsonb_path_exists. 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 NULL → t

9.16.2. Язык SQL/JSON Path

SQL/JSON путь выражения определяют элементы, которые должны быть извлечены из JSON данных, аналогично выражениям XPath, используемым для доступа к XML в SQL. В Tantor SE путь выражения реализованы в виде типа данных jsonpath и могут использовать любые элементы, описанные в Раздел 8.14.7.

Функции и операторы запросов JSON передают предоставленное выражение пути в движок пути для оценки. Если выражение соответствует запрашиваемым данным JSON, возвращается соответствующий элемент JSON или набор элементов. Выражения пути записываются на языке SQL/JSON path и могут включать арифметические выражения и функции.

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

Для ссылки на запрашиваемое значение JSON (текущий элемент контекста), используйте переменную $ в выражении пути. За ней может следовать один или несколько операторов доступа,, которые спускаются по уровням структуры JSON для извлечения подэлементов текущего элемента. Каждый оператор, следующий за предыдущим шагом вычисления, работает с результатом предыдущего шага.

Например, предположим, у вас есть некоторые данные JSON от GPS-трекера, которые вы хотели бы разобрать, например:

{
  "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
      }
    ]
  }
}

Для получения доступных сегментов трека необходимо использовать оператор доступа .key для спуска по окружающим JSON-объектам:

$.track.segments

Для получения содержимого массива обычно используется оператор [*]. Например, следующий путь вернет координаты местоположения для всех доступных сегментов трека:

$.track.segments[*].location

Чтобы вернуть координаты только первого сегмента, вы можете указать соответствующий индекс в операторе доступа []. Помните, что индексы массива JSON начинаются с 0:

$.track.segments[0].location

Результат каждого шага вычисления пути может быть обработан одним или несколькими операторами и методами jsonpath, перечисленными в Раздел 9.16.2.2. Каждое имя метода должно предшествовать точке. Например, вы можете получить размер массива:

$.track.segments.size()

Больше примеров использования операторов и методов jsonpath внутри выражений пути приведены ниже в Раздел 9.16.2.2.

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

? (condition)

Выражения фильтрации должны быть написаны сразу после шага оценки пути, к которому они должны применяться. Результат этого шага фильтруется таким образом, чтобы включать только те элементы, которые удовлетворяют заданному условию. SQL/JSON определяет трехзначную логику, поэтому условие может быть true, false или unknown. Значение unknown играет ту же роль, что и SQL NULL и может быть проверено с помощью предиката is unknown. Дальнейшие шаги оценки пути используют только те элементы, для которых выражение фильтрации вернуло true.

Все функции и операторы, которые могут использоваться в выражениях фильтра, перечислены в Таблица 9.50. Внутри выражения фильтра переменная @ обозначает значение, которое фильтруется (т.е. один результат предыдущего шага пути). Вы можете использовать операторы доступа после @ для извлечения компонентных элементов.

Например, предположим, что вы хотите получить все значения пульса, превышающие 130. Вы можете достичь этого с помощью следующего выражения:

$.track.segments[*].HR ? (@ > 130)

Чтобы получить времена начала сегментов с такими значениями, необходимо отфильтровать нерелевантные сегменты перед возвратом времен начала, поэтому выражение фильтра применяется к предыдущему шагу, и путь, используемый в условии, отличается:

$.track.segments[*] ? (@.HR > 130)."start time"

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

$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"

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

$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)

Вы также можете вкладывать выражения фильтрации друг в друга:

$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()

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

Реализация Tantor SE языка SQL/JSON path имеет следующие отклонения от стандарта SQL/JSON:

• Выражение пути может быть логическим предикатом, хотя стандарт SQL/JSON разрешает предикаты только в фильтрах. Это необходимо для реализации оператора @@. Например, следующее выражение jsonpath является допустимым в Tantor SE:

  $.track.segments[*].HR < 70
  

• В like_regex фильтрах есть небольшие различия в интерпретации шаблонов регулярных выражений, как описано в Раздел 9.16.2.3.

9.16.2.1. Строгий и Нестрогий режимы

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

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

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

Режим lax облегчает сопоставление структуры JSON-документа и пути выражения, если данные JSON не соответствуют ожидаемой схеме. Если операнд не соответствует требованиям определенной операции, он может быть автоматически обернут в SQL/JSON-массив или распакован путем преобразования его элементов в SQL/JSON-последовательность перед выполнением этой операции. Кроме того, операторы сравнения автоматически распаковывают свои операнды в режиме lax, поэтому вы можете сравнивать SQL/JSON-массивы "из коробки". Массив размером 1 считается равным своему единственному элементу. Автоматическое распаковывание не выполняется только в случае:

• Выражение пути содержит методы type() или size(), которые возвращают тип и количество элементов в массиве соответственно.

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

Например, при запросе данных GPS, перечисленных выше, вы можете абстрагироваться от того факта, что они хранят массив сегментов при использовании режима "lax":

lax $.track.segments.location

В строгом режиме указанный путь должен точно соответствовать структуре запрашиваемого JSON-документа, чтобы вернуть элемент SQL/JSON, поэтому использование этого выражения пути вызовет ошибку. Чтобы получить тот же результат, что и в режиме неполной проверки, необходимо явно распаковать массив segments:

strict $.track.segments[*].location

Доступ к .** может привести к неожиданным результатам при использовании режима lax. Например, следующий запрос выбирает каждое значение HR дважды:

lax $.**.HR

Это происходит потому, что доступ к .** выбирает как массив segments, так и каждый из его элементов, в то время как доступ к .HR автоматически распаковывает массивы при использовании режима lax. Чтобы избежать неожиданных результатов, рекомендуется использовать доступ .** только в строгом режиме. Следующий запрос выбирает каждое значение HR только один раз:

strict $.**.HR

9.16.2.2. Операторы и методы SQL/JSON Path

Таблица 9.49 показывает операторы и методы, доступные в jsonpath. Обратите внимание, что унарные операторы и методы могут применяться к нескольким значениям, полученным из предыдущего шага пути, в то время как бинарные операторы (сложение и т. д.) могут применяться только к одному значению.

Таблица 9.49. jsonpath Операторы и методы

Оператор/Метод Описание Пример(ы)
number + number → number Дополнение jsonb_path_query('[2]', '$[0] + 3') → 5
+ number → number Унарный плюс (без операции); в отличие от сложения, он может выполняться для нескольких значений jsonb_path_query_array('{"x": [2,3,4]}', '+ $.x') → [2, 3, 4]
number - number → number Вычитание jsonb_path_query('[2]', '7 - $[0]') → 5
- number → number Отрицание; в отличие от вычитания, это может выполняться для нескольких значений jsonb_path_query_array('{"x": [2,3,4]}', '- $.x') → [-2, -3, -4]
number * number → number Умножение jsonb_path_query('[4]', '2 * $[0]') → 8
number / number → number Раздел jsonb_path_query('[8.5]', '$[0] / 2') → 4.2500000000000000
number % number → number Модуло (остаток) 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 . 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
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"]
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, поддерживающих часовые пояса.

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

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

Предикат/Значение Описание Пример(ы)
value == value → boolean Сравнение на равенство (этот оператор, а также другие операторы сравнения, работают со всеми скалярными значениями JSON) jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == 1)') → [1, 1] jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == "a")') → ["a"]
value != value → boolean value <> value → boolean Сравнение неравенства jsonb_path_query_array('[1, 2, 1, 3]', '$[*] ? (@ != 1)') → [2, 3] jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <> "b")') → ["a", "c"]
value < value → boolean Сравнение меньше чем jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ < 2)') → [1]
value <= value → boolean Сравнение "меньше или равно" jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <= "b")') → ["a", "b"]
value > value → boolean Сравнение больше чем jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ > 2)') → [3]
value >= value → boolean Сравнение "больше или равно" jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ >= 2)') → [2, 3]
true → boolean Константа JSON true jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == true)') → {"name": "Chris", "parent": true}
false → boolean JSON константа false jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == false)') → {"name": "John", "parent": false}
null → value JSON константа null (обратите внимание, что, в отличие от SQL, сравнение с null работает нормально) jsonb_path_query('[{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}]', '$[*] ? (@.job == null) .name') → "Mary"
boolean && boolean → boolean Логическое И jsonb_path_query('[1, 3, 7]', '$[*] ? (@ > 1 && @ < 5)') → 3
boolean || boolean → boolean Логическое ИЛИ jsonb_path_query('[1, 3, 7]', '$[*] ? (@ < 1 || @ > 5)') → 7
! boolean → boolean Логическое НЕТ jsonb_path_query('[1, 3, 7]', '$[*] ? (!(@ < 5))') → 7
boolean is unknown → boolean Проверяет, является ли логическое условие unknown. jsonb_path_query('[-1, 2, 7, "foo"]', '$[*] ? ((@ > 0) is unknown)') → "foo"
string like_regex string [ flag string ] → boolean Проверяет, соответствует ли первый операнд регулярному выражению, заданному вторым операндом, с возможными модификациями, описанными строкой символов flag (см. Раздел 9.16.2.3). 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 string → boolean Проверяет, является ли второй операнд начальным подстрокой первого операнда. 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.3. 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.15. Функции XML	Начало	9.17. Функции манипуляции последовательностями