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

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

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

9.16.1. Обработка и создание данных JSON
9.16.2. Язык SQL/JSON Path

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

  • функции и операторы для обработки и создания данных JSON

  • язык SQL/JSON пути

Для обеспечения нативной поддержки типов данных JSON в среде SQL, Tantor BE реализует модель данных 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 BE, см. Раздел 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. Некоторые функции в этой таблице имеют предложение 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"}

[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")

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 BE путь выражения реализованы в виде типа данных 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.51. Внутри выражения фильтра переменная @ обозначает значение, которое фильтруется (т.е. один результат предыдущего шага пути). Вы можете использовать операторы доступа после @ для извлечения компонентных элементов.

Например, предположим, что нужно получить все значения пульса, превышающие 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 BE языка SQL/JSON path имеет следующие отклонения от стандарта SQL/JSON:

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

    $.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.50 показывает операторы и методы, доступные в jsonpath. Обратите внимание, что унарные операторы и методы могут применяться к нескольким значениям, полученным из предыдущего шага пути, в то время как бинарные операторы (сложение и т. д.) могут применяться только к одному значению.

Таблица 9.50. 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.51 показывает доступные элементы выражения фильтра.

Таблица 9.51. 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. Функции манипуляции последовательностями