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

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

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

В этом разделе описываются:

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

  • язык путей SQL/JSON

Чтобы узнать больше о стандарте SQL/JSON, обратитесь к [sqltr-19075-6]. Типы JSON, поддерживаемые в Postgres Pro, описаны в Разделе 8.14.

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

Примечание

Функции, работающие с JSONB, не принимают символы '\u0000'. Чтобы избежать ошибок и заменять их на лету, необходимо указать символ Unicode в параметре конфигурации unicode_nul_character_replacement_in_jsonb.

В Таблице 9.44 показаны имеющиеся операторы для работы с данными JSON (см. Раздел 8.14). Кроме них для типа jsonb, но не для json, определены обычные операторы сравнения, показанные в Таблице 9.1. Они следуют правилам упорядочивания для операций B-дерева, описанным в Подразделе 8.14.4. В Разделе 9.21 вы также можете узнать об агрегатной функции json_agg, которая агрегирует значения записи в виде JSON, и агрегатной функции json_object_agg, агрегирующей пары значений в объект JSON, а также их аналогах для jsonb, функциях jsonb_agg и jsonb_object_agg.

Таблица 9.44. Операторы для типов json и jsonb

Оператор

Описание

Пример(ы)

json -> integer → json

jsonb -> integer → jsonb

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

'[{"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-массива, в виде значения text.

'[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-объект по заданному пути в виде значения text.

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


Примечание

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

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

Таблица 9.45. Дополнительные операторы 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.46 показаны функции, предназначенные для создания значений json и jsonb.

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

Функция

Описание

Пример(ы)

to_json ( anyelement ) → json

to_jsonb ( anyelement ) → jsonb

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

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, но если в необязательном логическом параметре передаётся true, между элементами массива верхнего уровня дополнительно добавляются переводы строк.

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

row_to_json ( record [, boolean] ) → json

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

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-объект из переменного списка аргументов. По соглашению в этом списке перечисляются по очереди ключи и значения. Аргументы, задающие ключи, приводятся к текстовому типу, а аргументы-значения преобразуются методом 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.47 показаны функции, предназначенные для работы со значениями json и jsonb.

Таблица 9.47. Функции для обработки 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-объект верхнего уровня в набор пар ключ/значение (key/value).

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-объект верхнего уровня в набор пар ключ/значение (key/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-объект по заданному пути в виде значения text. (То же самое делает оператор #>>.)

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-тип выходного столбца последовательно применяются следующие правила:

  • Значение NULL в JSON всегда преобразуется в SQL NULL.

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

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

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

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

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

В следующем примере значение JSON фиксировано, но обычно такая функция обращается с использованием LATERAL к столбцу 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 равен true (это значение по умолчанию) и элемент, на который указывает path, не существует. Чтобы это изменение произошло, все предыдущие элементы, на которые указывает путь, должны существовать. В противном случае target возвращается без изменений. Как и с операторами, принимающими пути, отрицательные целые числа, фигурирующие в path, отсчитывают элементы с конца JSON-массива. Если на последнем шаге пути указывается индекс, выходящий за границы массива, и параметр create_if_missing равен true, новое значение добавляется в начало массива, когда индекс отрицательный, или в конец, когда он положительный.

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

Удаляет из данного значения JSON все поля объектов, имеющие значения null, на всех уровнях вложенности. Значения 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. Дополнительные аргументы 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-массива. Дополнительные аргументы 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, либо 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

Выражения путей SQL/JSON определяют элементы, извлекаемые из данных JSON, подобно тому, как выражения XPath позволяют обращаться из SQL к XML. В Postgres Pro выражения путей представляются в виде типа данных jsonpath и могут использовать любые элементы, описанные в Подразделе 8.14.6.

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

Выражение пути состоит из последовательности элементов, допустимых для типа 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
      }
    ]
  }
}

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

? (условие)

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

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

Например, предположим, что вы хотите получить все показатели пульса, превышающие 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()

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

Реализация языка путей SQL/JSON в Postgres Pro имеет следующие отличия от стандарта SQL/JSON:

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

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

  • Есть небольшие различия в интерпретации шаблонов регулярных выражений, используемых в фильтрах like_regex; имеющиеся особенности описаны в Подразделе 9.16.2.3.

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

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

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

  • strict — строгий режим, в котором структурные ошибки выдаются как есть.

Нестрогий режим упрощает сопоставление структуры документа JSON с выражением пути в случаях, когда данные JSON не соответствуют ожидаемой схеме. Если операнд не удовлетворяет требованиям определённой операции, он может перед выполнением этой операции автоматически оборачиваться в массив SQL/JSON или наоборот, разворачиваться так, чтобы его элементы образовали последовательность SQL/JSON. Помимо этого, в нестрогом режиме операторы сравнения автоматически разворачивают свои операнды, что позволяет легко сравнивать массивы SQL/JSON. Массив с одним элементом в таком режиме считается равным своему элементу. Автоматическое разворачивание не выполняется только в следующих случаях:

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

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

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

lax $.track.segments.location

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

strict $.track.segments[*].location

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

lax $.**.HR

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

strict $.**.HR

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

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

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

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

Описание

Пример(ы)

число + число → число

Сложение

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

+ число → число

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

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

число - число → число

Вычитание

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

- число → число

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

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

число * число → число

Умножение

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

число / число → число

Деление

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

число % число → число

Остаток от деления

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

значение . type() → строка

Тип элемента JSON (см. json_typeof)

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

значение . size() → число

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

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

значение . double() → число

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

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

число . ceiling() → число

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

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

число . floor() → число

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

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

число . abs() → число

Модуль заданного числа (абсолютное значение)

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

строка . datetime() → тип_даты_времени (см. примечание)

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

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

string . datetime(шаблон) → тип_даты_времени (см. примечание)

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

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

object . keyvalue() → массив

Пары ключ-значение, представленные в виде массива объектов со следующими тремя полями: "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(шаблон) может быть date, timetz, time, timestamptz или timestamp. Эти два метода определяют тип своего результата автоматически.

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

Метод datetime(шаблон) определяет результирующий тип в соответствии с полями заданного шаблона.

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

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

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

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

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

Описание

Пример(ы)

значение == значение → boolean

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

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

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

значение != значение → boolean

значение <> значение → boolean

Проверка неравенства

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

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

значение < значение → boolean

Проверка «меньше»

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

значение <= значение → boolean

Проверка «меньше или равно»

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

значение > значение → boolean

Проверка «больше»

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

значение >= значение → 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 → значение

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

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

логическое_значение && логическое_значение → boolean

Логическое И

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

логическое_значение || логическое_значение → boolean

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

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

! логическое_значение → boolean

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

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

логическое_значение is unknown → boolean

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

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

строка like_regex строка [ flag строка ] → 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"]

строка starts with строка → boolean

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

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

exists ( выражение_пути ) → 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. Однако в Postgres Pro оператор LIKE_REGEX в настоящее время отсутствует. Поэтому фильтр like_regex реализован с использованием механизма регулярных выражений POSIX, который описан в Подразделе 9.7.3. Вследствие этого наблюдается ряд небольших отклонений от описанного в стандарте поведения SQL/JSON, о которых рассказывается в Подразделе 9.7.3.8. Заметьте однако, что описанная там несовместимость букв флагов не проявляется на уровне SQL/JSON, так как заданные в SQL/JSON флаги XQuery переводятся во флаги, воспринимаемые механизмом POSIX.

Помните, что аргумент, задающий шаблон для like_regex, является строкой пути JSON и записывается по правилам, описанным в Подразделе 8.14.6. Это в частности означает, что каждую косую черту в регулярном выражении надо дублировать. Например, чтобы отобрать строковые значения, находящиеся на уровне корня документа и содержащие только цифры, нужно написать: 

$.* ? (@ like_regex "^\\d+$")

Пред. Наверх След.
9.15. XML-функции Начало 9.17. Функции для работы с последовательностями

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.