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

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

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

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

Чтобы узнать больше о стандарте SQL/JSON, обратитесь к стандарту ISO/IEC TR 19075-6. Подробную информацию о типах JSON, поддерживаемых в QHB, см. в разделе Типы JSON.

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

В Таблице 44 приведены имеющиеся операторы для использования с типами данных JSON (см. раздел Типы JSON). Кроме того, для типа jsonb (но не для json) имеются обычные операторы сравнения, приведенные в таблице Операторы сравнения. Эти операторы сравнения следуют правилам упорядочивания для операций B-дерева, изложенным в подразделе Индексация jsonb.

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

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

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

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

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

В Таблице 46 приведены имеющиеся функции для создания значений json и jsonb.

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

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

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

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

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

select * from json_each('{"a":"foo", "b":"bar"}') →
     key | value
    -----+-------
     a   | "foo"
     b   | "bar"

select * from json_each_text('{"a":"foo", "b":"bar"}') →
     key | value
    -----+-------
     a   | foo
     b   | bar

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

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

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

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

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

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_pretty('[{"f1":1,"f2":null}, 2]') →
    [
        {
            "f1": 1,
            "f2": null
        },
        2
    ]

См. также раздел Агрегатные функции, где описывается агрегатная функция json_agg, агрегирующая значения записей в виде JSON, и агрегатная функция json_object_agg, агрегирующая пары значений в объект JSON, а также их аналоги для типа jsonb: jsonb_agg и jsonb_object_agg.

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

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

Функции и операторы запросов 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, перечисленными в подразделе Операторы и методы пути SQL/JSON. Каждому имени метода должна предшествовать точка. Например, так можно получить размер массива:

$.track.segments.size()

Дополнительные примеры использования операторов и методов jsonpath в выражениях пути см. ниже в подразделе Операторы и методы пути SQL/JSON.

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

? (условие)

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

Функции и операторы, которые можно использовать в выражениях фильтра, перечислены в Таблице 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 в QHB имеет следующие отклонения от стандарта SQL/JSON:

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

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

• Существуют небольшие различия в интерпретации шаблонов регулярных выражений, используемых в фильтрах like_regex; эти различия описаны в подразделе Регулярные выражения SQL/JSON.

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

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

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

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

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

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

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

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

lax $.track.segments.location

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

strict $.track.segments\[\*\].location

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

lax $.**.HR

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

strict $.**.HR

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

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

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

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

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

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

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

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

В Таблице 49 приведены имеющиеся элементы выражения фильтра.

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

Регулярные выражения SQL/JSON

Выражения путей SQL/JSON позволяют сопоставлять текст с регулярным выражением при помощи фильтра like_regex. Например, следующий запрос пути SQL/JSON будет соответствовать всем строкам в массиве, которые начинаются с английской гласной, без учета регистра:

$[*] ? (@ like_regex "^[aeiou]" flag "i")

Необязательная строка flag может включать один или несколько следующих символов: i — сопоставления без учета регистра, m — допускается сопоставление ^ и $ с переводами строк, s — допускается сопоставление . с переводом строки, и q — заключить в кавычки весь шаблон (сводя поиск к простому сопоставлению подстроки).

Стандарт SQL/JSON заимствует свое определение для регулярных выражений из оператора LIKE_REGEX, в котором, в свою очередь, используется стандарт XQuery. В настоящее время в QHB оператор LIKE_REGEX не поддерживается. Поэтому фильтр like_regex реализован с использованием механизма регулярных выражений POSIX, описанного в подразделе Регулярные выражения POSIX. Это приводит к различным незначительным несоответствиям стандартному поведению SQL/JSON, которые каталогизированы в подразделе Отличия от XQuery (LIKE_REGEX). Тем не менее, обратите внимание, что описанные там несовместимости флаговых букв не применяются к SQL/JSON, поскольку в SQL/JSON флаговые буквы XQuery переводятся во флаги, подходящие механизму POSIX.

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

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