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

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

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

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

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

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

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

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

Примечание

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

В Таблице 9.44 перечислены операторы, которые можно использовать с типами данных JSON (см. Раздел 8.14).

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

ОператорТип правого операндаТип результатаОписаниеПримерРезультат примера
->intjson или jsonbВыдаёт элемент массива JSON (по номеру от 0, отрицательные числа задают позиции с конца)'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2{"c":"baz"}
->textjson или jsonbВыдаёт поле объекта JSON по ключу'{"a": {"b":"foo"}}'::json->'a'{"b":"foo"}
->>inttextВыдаёт элемент массива JSON в типе text'[1,2,3]'::json->>23
->>texttextВыдаёт поле объекта JSON в типе text'{"a":1,"b":2}'::json->>'b'2
#>text[]json или jsonbВыдаёт объект JSON по заданному пути'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'{"c": "foo"}
#>>text[]textВыдаёт объект JSON по заданному пути в типе text'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'3


Примечание

Эти операторы существуют в двух вариациях для типов json и jsonb. Операторы извлечения поля/элемента/пути возвращают тот же тип, что у операнда слева (json или jsonb), за исключением тех, что возвращают тип text (они возвращают значение как текстовое). Если входные данные JSON не содержат структуры, удовлетворяющей запросу, например в них нет искомого элемента, то операторы извлечения поля/элемента/пути не выдают ошибку, а возвращают NULL. Все операторы извлечения поля/элемента/пути, принимающие целочисленные позиции в массивах JSON, поддерживают и отсчёт от конца массива по отрицательной позиции.

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

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

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

ОператорТип правого операндаОписаниеПример
@>jsonbЛевое значение JSON содержит на верхнем уровне путь/значение JSON справа?'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@jsonbПуть/значение JSON слева содержится на верхнем уровне в правом значении JSON?'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
?textПрисутствует ли строка в качестве ключа верхнего уровня в значении JSON?'{"a":1, "b":2}'::jsonb ? 'b'
?|text[]Какие-либо строки массива присутствуют в качестве ключей верхнего уровня?'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
?&text[]Все строки массива присутствуют в качестве ключей верхнего уровня?'["a", "b"]'::jsonb ?& array['a', 'b']
||jsonbСоединяет два значения jsonb в новое значение jsonb'["a", "b"]'::jsonb || '["c", "d"]'::jsonb
-textУдаляет пару ключ/значение или элемент-строку из левого операнда. Пары ключ/значение выбираются по значению ключа.'{"a": "b"}'::jsonb - 'a'
-text[]Удаляет множество пар ключ/значение или элементы-строки из левого операнда. Пары ключ/значение выбираются по значению ключа.'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
-integerУдаляет из массива элемент в заданной позиции (отрицательные номера позиций отсчитываются от конца). Выдаёт ошибку, если контейнер верхнего уровня — не массив.'["a", "b"]'::jsonb - 1
#-text[]Удаляет поле или элемент с заданным путём (для массивов JSON отрицательные номера позиций отсчитываются от конца)'["a", {"b":1}]'::jsonb #- '{1,b}'
@?jsonpathВыдаёт ли путь JSON какой-либо элемент для заданного значения JSON?'{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'
@@jsonpathВозвращает результат проверки предиката пути JSON для заданного значения JSON. При этом учитывается только первый элемент результата. Если результат не является логическим, возвращается null.'{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'


Примечание

Оператор || соединяет два объекта JSON вместе, формируя объект с объединённым набором ключей и значений, при этом в случае совпадения ключей выбирается значение из второго объекта. С любыми другими аргументами формируется массив JSON: сначала любой аргумент, отличный от массива, преобразуется в одноэлементный массив, а затем два полученных массива соединяются. Эта операция не рекурсивна — объединение производится только на верхнем уровне структуры объекта или массива.

Примечание

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

В Таблице 9.46 показаны функции, позволяющие создавать значения типов json и jsonb. (Для типа jsonb нет аналогов функций row_to_json и array_to_json, но практически тот же результат можно получить с помощью to_jsonb.)

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

ФункцияОписаниеПримерРезультат примера

to_json(anyelement)

to_jsonb(anyelement)

Возвращает значение в виде json или jsonb. Массивы и составные структуры преобразуются (рекурсивно) в массивы и объекты; для других типов, для которых определено приведение к json, применяется эта функция приведения, а для всех остальных выдаётся скалярное значение. Значения всех скалярных типов, кроме числового, логического и NULL, представляются в текстовом виде, в стиле, допустимом для значений json или jsonb.to_json('Fred said "Hi."'::text)"Fred said \"Hi.\""
array_to_json(anyarray [, pretty_bool])Возвращает массив в виде массива JSON. Многомерный массив Postgres Pro становится массивом массивов JSON. Если параметр pretty_bool равен true, между элементами 1-ой размерности вставляются разрывы строк.array_to_json('{{1,5},{99,100}}'::int[])[[1,5],[99,100]]
row_to_json(record [, pretty_bool])Возвращает кортеж в виде объекта JSON. Если параметр pretty_bool равен true, между элементами 1-ой размерности вставляются разрывы строк.row_to_json(row(1,'foo')){"f1":1,"f2":"foo"}

json_build_array(VARIADIC "any")

jsonb_build_array(VARIADIC "any")

Формирует массив JSON (возможно, разнородный) из переменного списка аргументов.json_build_array(1,2,'3',4,5)[1, 2, "3", 4, 5]

json_build_object(VARIADIC "any")

jsonb_build_object(VARIADIC "any")

Формирует объект JSON из переменного списка аргументов. По соглашению в этом списке перечисляются по очереди ключи и значения.json_build_object('foo',1,'bar',2){"foo": 1, "bar": 2}

json_object(text[])

jsonb_object(text[])

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

json_object('{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[])

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

Эта форма json_object принимает ключи и значения по парам из двух отдельных массивов. Во всех остальных отношениях она не отличается от формы с одним аргументом.json_object('{a, b}', '{1,2}'){"a": "1", "b": "2"}


Примечание

Функции array_to_json и row_to_json подобны to_json, но предлагают возможность улучшенного вывода. Действие to_json, описанное выше, распространяется на каждое отдельное значение, преобразуемое этими функциями.

Примечание

В расширении hstore определено преобразование из hstore в json, так что значения hstore, преобразуемые функциями создания JSON, будут представлены в виде объектов JSON, а не как примитивные строковые значения.

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

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

ФункцияТип результатаОписаниеПримерРезультат примера

json_array_length(json)

jsonb_array_length(jsonb)

intВозвращает число элементов во внешнем массиве JSON.json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')5

json_each(json)

jsonb_each(jsonb)

setof key text, value json

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

jsonb_each_text(jsonb)

setof 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[])

jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])

json

jsonb

Возвращает значение JSON по пути, заданному элементами пути (path_elems) (равнозначно оператору #> operator).json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4'){"f5":99,"f6":"foo"}

json_extract_path_text(from_json json, VARIADIC path_elems text[])

jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])

textВозвращает значение JSON по пути, заданному элементами пути path_elems, как text (равнозначно оператору #>>).json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')foo

json_object_keys(json)

jsonb_object_keys(jsonb)

setof textВозвращает набор ключей во внешнем объекте JSON.json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')
 json_object_keys
-----------------
 f1
 f2

json_populate_record(base anyelement, from_json json)

jsonb_populate_record(base anyelement, from_json jsonb)

anyelementРазворачивает объект из from_json в табличную строку, в которой столбцы соответствуют типу строки, заданному параметром base (см. примечания ниже).

create type myrowtype as (a int, b json, c json);

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

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

json_populate_recordset(base anyelement, from_json json)

jsonb_populate_recordset(base anyelement, from_json jsonb)

setof anyelementРазворачивает внешний массив объектов из from_json в набор табличных строк, в котором столбцы соответствуют типу строки, заданному параметром base (см. примечания ниже).

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

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

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

json_array_elements(json)

jsonb_array_elements(jsonb)

setof json

setof jsonb

Разворачивает массив JSON в набор значений JSON.select * from json_array_elements('[1,true, [2,false]]')
   value
-----------
 1
 true
 [2,false]

json_array_elements_text(json)

jsonb_array_elements_text(jsonb)

setof textРазворачивает массив JSON в набор значений text.select * from json_array_elements_text('["foo", "bar"]')
   value
-----------
 foo
 bar

json_typeof(json)

jsonb_typeof(jsonb)

textВозвращает тип внешнего значения JSON в виде текстовой строки. Возможные типы: object, array, string, number, boolean и null.json_typeof('-123.4')number

json_to_record(json)

jsonb_to_record(jsonb)

recordФормирует обычную запись из объекта JSON (см. примечания ниже). Как и со всеми функциями, возвращающими record, при вызове необходимо явно определить структуру записи с помощью предложения AS.

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)

jsonb_to_recordset(jsonb)

setof recordФормирует обычный набор записей из массива объекта JSON (см. примечания ниже). Как и со всеми функциями, возвращающими record, при вызове необходимо явно определить структуру записи с помощью предложения AS.select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);
 a |  b
---+-----
 1 | foo
 2 |

json_strip_nulls(from_json json)

jsonb_strip_nulls(from_json jsonb)

json

jsonb

Возвращает значение from_json, из которого исключаются все поля объекта, содержащие значения NULL. Другие значения NULL остаются нетронутыми.json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')[{"f1":1},2,null,3]

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

jsonb

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

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

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

[{"f1":[2,3,4],"f2":null},2,null,3]

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

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

jsonb

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

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

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

{"a": [0, "new_value", 1, 2]}

{"a": [0, 1, "new_value", 2]}

jsonb_pretty(from_json jsonb)

text

Возвращает значение from_json в виде текста JSON с отступами.jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')
[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

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

booleanОпределяет, выдаёт ли путь JSON какой-либо элемент при заданном значении JSON.

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

true

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

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

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

true

jsonb_path_query(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

setof jsonbВозвращает все элементы JSON, которые выдаёт путь JSON для заданного значения JSON.

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 bool]])

jsonbВозвращает все элементы JSON, которые выдаёт путь JSON для заданного значения JSON, оборачивая их в массив.

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 bool]])

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

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

2


Примечание

Многие из этих функций и операторов преобразуют спецпоследовательности Unicode в JSON-строках в соответствующие одиночные символы. Для входных данных типа jsonb это ничем не грозит, так как преобразование уже выполнено; однако для типа json в результате может произойти ошибка, как отмечено в Разделе 8.14.

Примечание

Функции json[b]_populate_record, json[b]_populate_recordset, json[b]_to_record и json[b]_to_recordset принимают объект JSON или массив объектов и извлекают значения, связанные с ключами, по именам, соответствующим именам столбцов выходного типа. Поля объектов, не соответствующие никаким именам выходных столбцов, игнорируются, а выходные столбцы, для которых не находятся поля в объекте, заполняются значениями NULL. Для преобразования значения JSON в тип SQL выходного столбца последовательно применяются следующие правила:

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

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

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

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

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

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

Хотя в примерах использования перечисленных функций применяются константы, обычно эти функции обращаются к таблицам в предложении FROM, а в качестве аргумента указывается один из столбцов типа json или jsonb. Извлечённые значения затем могут использоваться в других частях запроса, например, в предложениях WHERE и результирующих списках. Извлекая множество значений подобным образом, можно значительно увеличить производительность по сравнению с использованием операторов, работающих с отдельными ключами.

Примечание

В target должны присутствовать все элементы пути, заданного параметром path функций jsonb_set и jsonb_insert, за исключением последнего. Если create_missing равен false, должны присутствовать абсолютно все элементы пути path, переданного функции jsonb_set. Если это условие не выполняется, значение target возвращается неизменённым.

Если последним элементом пути оказывается ключ объекта, он будет создан в случае отсутствия и получит новое значение. Если последний элемент пути — позиция в массиве, то когда она положительна, целевой элемент отсчитывается слева, а когда отрицательна — справа, то есть -1 указывает на самый правый элемент и т. д. Если позиция лежит вне диапазона -длина_массива .. длина_массива -1, и параметр create_missing равен true, новое значение добавляется в начало массива, если позиция отрицательна, и в конец, если положительна.

Примечание

Значение null, возвращаемое функцией json_typeof, не следует путать с SQL NULL. Тогда как при вызове json_typeof('null'::json) возвращается null, при вызове json_typeof(NULL::json) будет возвращено значение SQL NULL.

Примечание

Если аргумент функции json_strip_nulls содержит повторяющиеся имена полей в любом объекте, в результате могут проявиться семантические различия, в зависимости от порядка этих полей. Это не проблема для функции jsonb_strip_nulls, так как в значениях jsonb имена полей не могут дублироваться.

Примечание

Функции jsonb_path_exists, jsonb_path_match, jsonb_path_query, jsonb_path_query_array и jsonb_path_query_first принимают дополнительно необязательные аргументы vars и silent.

В аргументе vars можно передать объект, содержащий именованные переменные, которые будут подставляться в выражение jsonpath.

Если передаётся аргумент silent и он имеет значение true, эти функции подавляют те же ошибки, что и операторы @? и @@.


9.15.2. Язык путей SQL/JSON

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

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

Выражение пути состоит из последовательности элементов, допустимых для типа jsonpath. Вычисляется оно слева направо, но при необходимости порядок операций можно изменить, добавив скобки. В случае успешного вычисления выдаётся последовательность элементов SQL/JSON (последовательность SQL/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'

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

'$.track.segments[0].location'

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

'$.track.segments.size()'

Дополнительные примеры использования операторов и методов jsonpath в выражениях пути приведены в Подразделе 9.15.2.3.

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

? (условие)

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

Функции и операторы, которые можно использовать в выражениях фильтра, перечислены в Таблице 9.49. Результат вычисления пути, проходящий через фильтр, представляется переменной @. Чтобы обратиться к элементу JSON, находящемуся на более низком уровне вложенности, добавьте после @ один или несколько операторов обращения.

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

  • Метод .datetime() ещё не реализован, потому что постоянные функции и операторы jsonpath не могут использовать часовой пояс текущего сеанса, а это необходимо в некоторых операциях с датой/временем. Поддержка дат/времени будет добавлена в jsonpath в будущих версиях Postgres Pro.

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

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

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

9.15.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.15.2.2. Регулярные выражения

Выражения путей 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.2.3. Операторы и методы SQL/JSON

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

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

Оператор/МетодОписаниеПример JSONПример запросаРезультат
+ (унарный)Оператор «плюс», применяемый к элементам последовательности SQL/JSON{"x": [2.85, -14.7, -9.4]}+ $.x.floor()2, -15, -10
- (унарный)Оператор «минус», применяемый к элементам последовательности SQL/JSON{"x": [2.85, -14.7, -9.4]}- $.x.floor()-2, 15, 10
+ (бинарный)Сложение[2]2 + $[0]4
- (бинарный)Вычитание[2]4 - $[0]2
*Умножение[4]2 * $[0]8
/Деление[8]$[0] / 24
%Остаток от деления[32]$[0] % 102
type()Тип элемента SQL/JSON[1, "2", {}]$[*].type()"number", "string", "object"
size()Размер элемента SQL/JSON{"m": [11, 15]}$.m.size()2
double()Приблизительное число с плавающей точкой, преобразованное из строки или числа SQL/JSON{"len": "1.9"}$.len.double() * 23.8
ceiling()Ближайшее целое, большее или равное числу SQL/JSON{"h": 1.3}$.h.ceiling()2
floor()Ближайшее целое, меньшее или равное числу SQL/JSON{"h": 1.3}$.h.floor()1
abs()Модуль числа SQL/JSON (абсолютное значение){"z": -0.3}$.z.abs()0.3
keyvalue()Последовательность пар ключ-значение, представленная в виде массива элементов, содержащих три поля ("key", "value" и "id"). В поле id содержится уникальный идентификатор объекта, к которому относится данная пара ключ-значение.{"x": "20", "y": 32}$.keyvalue(){"key": "x", "value": "20", "id": 0}, {"key": "y", "value": 32, "id": 0}


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

Значение/ПредикатОписаниеПример JSONПример запросаРезультат
==Оператор равенства[1, 2, 1, 3]$[*] ? (@ == 1)1, 1
!=Оператор неравенства[1, 2, 1, 3]$[*] ? (@ != 1)2, 3
<>Оператор неравенства (синоним !=)[1, 2, 1, 3]$[*] ? (@ <> 1)2, 3
<Оператор «меньше»[1, 2, 3]$[*] ? (@ < 2)1
<=Оператор «меньше или равно»[1, 2, 3]$[*] ? (@ <= 2)1, 2
>Оператор «больше»[1, 2, 3]$[*] ? (@ > 2)3
>=Оператор «больше или равно»[1, 2, 3]$[*] ? (@ >= 2)2, 3
trueЗначение, которое можно сравнить с логическим значением JSON true[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]$[*] ? (@.parent == true){"name": "Chris", "parent": true}
falseЗначение, которое можно сравнить с логическим значением JSON false[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]$[*] ? (@.parent == false){"name": "John", "parent": false}
nullЗначение, которое можно сравнить со значением JSON null[{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}]$[*] ? (@.job == null) .name"Mary"
&&Логическое И[1, 3, 7]$[*] ? (@ > 1 && @ < 5)3
||Логическое ИЛИ[1, 3, 7]$[*] ? (@ < 1 || @ > 5)7
!Логическое НЕ[1, 3, 7]$[*] ? (!(@ < 5))7
like_regexПроверяет, соответствует ли первый операнд регулярному выражению, которое задаёт второй операнд с необязательным аргументом flag, влияющим на поведение выражения (см. Подраздел 9.15.2.2)["abc", "abd", "aBdC", "abdacb", "babc"]$[*] ? (@ like_regex "^ab.*c" flag "i")"abc", "aBdC", "abdacb"
starts withПроверяет, является ли второй операнд начальной подстрокой первого["John Smith", "Mary Stone", "Bob Johnson"]$[*] ? (@ starts with "John")"John Smith"
existsПроверяет, соответствует ли выражению пути минимум один элемент SQL/JSON{"x": [1, 2], "y": [2, 4]}strict $.* ? (exists (@ ? (@[*] > 2)))2, 4
is unknownПроверяет, является ли unknown результатом логического условия[-1, 2, 7, "infinity"]$[*] ? ((@ > 0) is unknown)"infinity"


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

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