Функции для работы с JSON
JSON - слабо схематизированный формат данных, представленный в YQL типом Json
. В отличии от реляционных таблиц, JSON может хранить данные, для которых схема не определена. Вот пример валидного JSON:
[
{
"name": "Jim Holden",
"age": 30
},
{
"name": "Naomi Nagata",
"age": "twenty years old"
}
]
Несмотря на то, что в первом объекте поле age
имеет тип Number
("age": 21
), а во втором String
("age": "twenty years old"
), это полностью валидный JSON.
Для работы с JSON, YQL реализует подмножество стандарта SQL support for JavaScript Object Notation (JSON), являющегося частью общепринятого стандарта ANSI SQL.
Cookbook
$json = CAST(@@{
"friends": [
{
"name": "James Holden",
"age": 35
},
{
"name": "Naomi Nagata",
"age": 30
}
]
}@@ AS Json);
SELECT
JSON_EXISTS($json, "$.friends[*].name"), -- True,
JSON_VALUE($json, "$.friends[0].age"), -- "35" (тип Utf8?)
JSON_QUERY($json, "$.friends[0]"); -- {"name": "James Holden", "age": 35}
Обращение к полю в базе данных
В таблицах данные могут хранить в JSON или в строковом представлении. Функции JSON_*
для работы ожидают на вход JSON. Для преобразования String->JSON
нужно воспользоваться функцией CAST
, например CAST (my_string AS JSON)
.
JsonPath
Для обращения к значениям внутри JSON используется язык запросов JsonPath. Все функции для работы с JSON принимают JsonPath запрос в качестве аргумента.
Сразу разберем пример. Пусть у нас есть JSON:
{
"comments": [
{
"id": 123,
"text": "A whisper will do, if it's all that you can manage."
},
{
"id": 456,
"text": "My life has become a single, ongoing revelation that I haven’t been cynical enough."
}
]
}
Тогда чтобы получить текст второго комментария можно написать JsonPath запрос:
$.comments[1].text
В этом запросе:
$
- это способ обратиться ко всему JSON$.comments
- обращение к ключуcomments
объекта JSON$.comments[1]
- обращение ко второму элементу массива JSON (нумерация с элементов начинается с 0)$.comments[1].text
- обращение к ключуtext
объекта JSON- Результат выполнения:
"My life has become a single, ongoing revelation that I haven’t been cynical enough."
Quick reference
Операция | Пример |
---|---|
Извлечь ключ JSON объекта | $.key |
Извлечь все ключи JSON объекта | $.* |
Обращение к элементу массива | $[25] |
Извлечение подотрезка массива | $[2 to 5] |
Обращение к последнему элементу массива | $[last] |
Обращение ко всем элементам массива | $[*] |
Унарные операции | - 1 |
Бинарные операции | (12 * 3) % 4 + 8 |
Обращение к переменной | $variable |
Логические операции | `(1 > 2) || (3 <= 4) && ("string" == "another")| |
Соответствие регулярному выражению | $.name like_regex "^[A-Za-z]+$" |
Проверка префикса строки | $.name starts with "Bobbie" |
Проверка существования пути | exists ($.profile.name) |
Проверка булевского выражения на null | ($.age > 20) is unknown |
Фильтрация значений | $.friends ? (@.age >= 18 && @.gender == "male") |
Получение типа значения | $.name.type() |
Получение размера массива | $.friends.size() |
Преобразование строки в число | $.number.double() |
Округление числа вверх | $.number.ceiling() |
Округление числа вниз | $.number.floor() |
Абсолютное значение числа | $.number.abs() |
Получение пар ключ-значения из объекта | $.profile.keyvalue() |
Модель данных
Результат выполнения всех JsonPath выражений - это последовательность JSON значений. Например:
- Результат выражения
"Bobbie"
- это последовательность длины 1 с единственным элементом"Bobbie"
. - Результат выражения
$
(взятие всего JSON объекта) на JSON[1, 2, 3]
- это[1, 2, 3]
. Последовательность из 1 элемента, массива[1, 2, 3]
- Результат выражения
$[*]
(извлечение всех элементов массива) на JSON[1, 2, 3]
- это1, 2, 3
. Последовательность из трех элементов1
,2
и3
Если входная последовательность состоит из нескольких значений, некоторые операции исполнятся для каждого элемента (например, доступ к ключу JSON объекта). При этом другие операции требуют последовательности из одного элемента на вход (например, бинарные арифметические операции).
Поведение конкретной операции описано в соответствующей секции документации.
Режим выполнения
JsonPath поддерживает два режима выполнения - lax
и strict
. Указание режима не обязательно, по умолчанию используется lax
. Режим указывается в начале запроса, например: strict $.key
.
Поведение при каждом режиме описано в разделе соответствующих JsonPath операций.
Автоматическая распаковка массива
При обращении к ключу JSON объекта в lax
режиме массивы автоматически распаковываются.
Пример
[
{
"key": 123
},
{
"key": 456
}
]
Запрос lax $.key
успешно выполнится с результатом 123, 456
. Поскольку $
является массивом, он автоматически распакуется, и обращение к ключу JSON объекта $.key
будет выполнено для каждого элемента массива.
При этом запрос strict $.key
завершится с ошибкой. В strict
режиме нет автоматической распаковки массивов. $
является массивом, а не объектом, поэтому обращение к ключу объекта $.key
не может быть выполнено. Это можно исправить, написав strict $[*].key
.
Распаковка происходит только на 1 уровень вглубь. В случае вложенных массивов распаковывается только самый внешний из них.
Обертка в массив
При обращении к элементу массива в lax
режиме JSON значения автоматически оборачиваются в массив.
Пример
{
"name": "Avasarala"
}
Запрос lax $[0].name
выполнится успешно с результатом "Avasarala"
. Поскольку $
не является массивом, он будет автоматически обернут в массив длины один. Обращение к первому элементу $[0]
вернет исходный JSON объект, в котором будет взят ключ name
.
При этом запрос strict $[0].name
завершится с ошибкой. В strict
режиме нет автоматической обертки в массив. $
является объектом, а не массивом, поэтому обращение к элементу $[0]
не может быть выполнено. Это можно исправить, написав strict $.name
.
Обработка ошибок
Некоторые ошибки конвертируются в пустой результат при выполнении в lax
режиме.
Литералы
Значения некоторых типов можно указать в JsonPath запросе используя литералы:
Тип | Пример |
---|---|
Числа | 42 , -1.23e-5 |
Булевские значения | false , true |
Null | null |
Строки | "Belt" |
Обращение к ключу JSON объекта
JsonPath поддерживает обращение к ключам JSON объектов: $.session.user.name
.
Примечание
Обращение без кавычек можно использовать только для ключей, которые начинаются с буквы английского алфавита или подчеркивания, и содержат в себе только буквы английского алфавита, подчеркивания, цифры и знак доллара. Для всех остальных ключей необходимо использовать кавычки. Например: $.profile."this string has spaces"
, $.user."42 is the answer"
Для каждого значения из входной последовательности:
- Если значение является массивом, в
lax
режиме происходит автоматическая распаковка массива - Если значение не является JSON объектом или указанный ключ в этом JSON объекте отсутствует, в
strict
режиме запрос завершается ошибкой. Вlax
режиме для этого значения возвращается пустой результат
Результат выражения - конкатенация результатов для каждого значения из входной последовательности.
Пример
{
"name": "Amos",
"friends": [
{
"name": "Jim"
},
{
"name": "Alex"
}
]
}
lax |
strict |
|
---|---|---|
$.name |
"Amos" |
"Amos" |
$.surname |
Пустой результат | Ошибка |
$.friends.name |
"Jim", "Alex" |
Ошибка |
Обращение ко всем ключам JSON объекта
JsonPath поддерживает обращение ко всем ключам JSON объектов сразу: $.*
.
Для каждого значения из входной последовательности:
- Если значение является массивом, в
lax
режиме происходит автоматическая распаковка массива - Если значение не является JSON объектом, в
strict
режиме запрос завершается ошибкой. Вlax
режиме для этого значения возвращается пустой результат
Результат выражения - конкатенация результатов для каждого значения из входной последовательности.
Пример
{
"profile": {
"id": 123,
"name": "Amos"
},
"friends": [
{
"name": "Jim"
},
{
"name": "Alex"
}
]
}
lax |
strict |
|
---|---|---|
$.profile.* |
123, "Amos" |
123, "Amos" |
$.friends.* |
"Jim", "Alex" |
Ошибка |
Обращение к элементу массива
JsonPath поддерживает обращение к элементам массивов: $.friends[1, 3 to last - 1]
.
Для каждого значения из входной последовательности:
- Если значение не является массивом, то в
strict
режиме запрос завершается ошибкой. Вlax
режиме происходит автоматическая обертка в массив - Ключевое слово
last
заменяется на последний индекс массива. Использованиеlast
вне обращения к массиву - это ошибка в обоих режимах - Вычисляются указанные индексы. Каждый из них должен быть единственным числом, иначе запрос завершается ошибкой в обоих режимах
- Если индекс является дробным числом, он округляется вниз
- Если индекс выходит за границы массива, то в
strict
режиме запрос завершается ошибкой. Вlax
режиме такой индекс игнорируется - Если указан отрезок и его стартовый индекс больше конечного индекса (например
$[20 to 1]
), то вstrict
режиме запрос завершается ошибкой. Вlax
режиме такой отрезок игнорируется. - К результату добавляются все элементы по указанным индексам. Отрезки включают в себя оба конца
Примеры
[
{
"name": "Camina",
"surname": "Drummer"
},
{
"name": "Josephus",
"surname": "Miller"
},
{
"name": "Bobbie",
"surname": "Draper"
},
{
"name": "Julie",
"surname": "Mao"
}
]
lax |
strict |
|
---|---|---|
$[0].name |
"Camina" |
"Camina" |
$[1, 2 to 3].name |
"Josephus", "Bobbie", "Julie" |
"Josephus", "Bobbie", "Julie" |
$[last - 2].name |
"Josephus" |
"Josephus" |
$[2, last + 200 to 50].name |
"Bobbie" |
Ошибка |
$[50].name |
Пустой результат | Ошибка |
Обращение ко всем элементам массива
JsonPath поддерживает обращение ко всем элементам массива сразу: $[*]
.
Для каждого значения из входной последовательности:
- Если значение не является массивом, то в
strict
режиме запрос завершается ошибкой. Вlax
режиме происходит автоматическая обертка в массив - К результату добавляются все элементы текущего массива
Примеры
[
{
"class": "Station",
"title": "Medina"
},
{
"class": "Corvette",
"title": "Rocinante"
}
]
lax |
strict |
|
---|---|---|
$[*].title |
"Medina", "Rocinante" |
"Medina", "Rocinante" |
lax $[0][*].class |
"Station" |
Ошибка |
Разберем последний пример по шагам:
$[0]
возвращает первый элемент массива, то есть{"class": "Station", "title": "Medina"}
$[0][*]
ожидает массив на вход, но был дан объект. Происходит автоматическая обертка в массив, получается[ {"class": "Station", "title": "Medina"} ]
- Теперь
$[0][*]
может выполниться и возвращает все элементы массива, то есть{"class": "Station", "title": "Medina"}
$[0][*].class
возвращает полеclass
, то есть"Station"
.
Арифметические операции
Примечание
Все арифметические операции работают с числами как с Double. Возможна потеря точности при вычислениях.
Унарные операции
JsonPath поддерживает унарный +
и -
.
Унарная операция применяется ко всем значениям из входной последовательности. Если унарной операции подать на вход не число, запрос завершится ошибкой в обоих режимах.
Пример
[1, 2, 3, 4]
Запрос strict -$[*]
завершится успешно с результатом -1, -2, -3, -4
.
Запрос lax -$
завершится с ошибкой, потому что $
является массивом, а не числом.
Бинарные операции
JsonPath поддерживает бинарные арифметические операции (в порядке убывания приоритета):
- Умножение
*
, деление чисел с плавающей точкой/
, взятие остатка%
(работает как функцияMOD
вSQL
) - Сложение
+
, вычитание-
Порядок выполнения операций можно поменять используя скобочки.
В случае если каждый аргумент бинарной операции не является единственным числом или происходит деление на ноль, запрос завершается ошибкой в обоих режимах.
Примеры
(1 + 2) * 3
даст9
1 / 2
даст0.5
5 % 2
даст1
1 / 0
завершится ошибкой- При JSON
[-32.4, 5.2]
запрос$[0] % $[1]
даст-1.2
- При JSON
[1, 2, 3, 4]
запросlax $[*] + $[*]
завершится ошибкой, так как результат выражения$[*]
- это1, 2, 3, 4
, несколько чисел. Бинарная операция требует только одно число для каждого своего аргумента.
Булевские значения
В отличии от некоторых языков программирования, в JsonPath булевскими значениями считаются не только true
(истина) и false
(ложь), но и null
(неопределенность).
JsonPath считает все значения, полученные из JSON документа, не булевскими. Например, запрос ! $.is_valid_user
(логическое отрицание, примененное к полю is_valid_user
), является синтаксически неверным поскольку поле is_valid_user
не является булевским значением (даже если по факту в нем хранится true
или false
). Правильный способ написать такой запрос - это явно использовать сравнение с булевским значением, например $.is_valid_user == false
.
Логические операции
JsonPath поддерживает некоторые логические операции для работы с булевскими значениями.
Аргументы всех логических операций должны являться единственным булевским значением.
Все логические операции возвращают булевское значение.
Логическое отрицание !
Таблица истинности:
x |
!x |
---|---|
true |
false |
false |
true |
null |
null |
Логическое И &&
Таблица истинности, первый столбец это левый аргумент, первая строка это правый аргумент, каждая клетка это результат применения логического И с левым и правым аргументами:
&& |
true |
false |
null |
---|---|---|---|
true |
true |
false |
null |
false |
false |
false |
false |
null |
null |
false |
null |
Логическое ИЛИ ||
Таблица истинности, первый столбец это левый аргумент, первая строка это правый аргумент, каждая клетка это результат применения логического ИЛИ с левым и правым аргументами:
|| |
true |
false |
null |
---|---|---|---|
true |
true |
true |
true |
false |
true |
false |
null |
null |
true |
null |
null |
Примеры
! (true == true)
, результатfalse
(true == true) && (true == false)
, результатfalse
(true == true) || (true == false)
, результатtrue
Операторы сравнения
JsonPath реализует операторы сравнения для значений:
- Равенство,
==
- Неравенство,
!=
и<>
- Меньше и меньше либо равно,
<
и<=
- Больше и больше либо равно,
>
и>=
Все операторы сравнения возвращают булевское значение. Оба аргумента оператора поддерживают наличие нескольких значений.
Если при вычислении аргументов оператора возникла ошибка, оператор возвращает null
. При этом выполнение JsonPath запроса продолжается.
Для каждого из аргументов производится автоматическая распаковка массивов. После этого для каждой пары, где первый элемент берется из последовательности левого аргумента, а второй элемент из последовательности правого аргумента:
- Выполняется сравнение элементов пары
- Если при сравнении возникла ошибка, устанавливается флаг
ERROR
- Если результат сравнения это истина, устанавливается флаг
FOUND
- Если один из флагов
ERROR
илиFOUND
установлен и запрос исполняется вlax
режиме, больше никакие пары не рассматриваются
Если после рассмотрения пар:
- Установлен флаг
ERROR
, оператор возвращаетnull
- Установлен флаг
FOUND
, оператор возвращаетtrue
- Иначе оператор возвращает
false
Можно сказать что данный алгоритм рассматривает все пары из декартового произведения левого и правого аргумента, пытаясь найти ту, сравнение которой вернет истину.
Сравнение элементов в паре производится по следующим правилам:
- Если левый или правый аргумент являются массивом или объектом, сравнение завершается с ошибкой
null == null
возвращает истину- Во всех остальных случаях если один из аргументов
null
, возвращается ложь - Если левый и правый аргумент разных типов, сравнение завершается с ошибкой
- Строки сравниваются побайтово
true
считается большеfalse
- Числа сравниваются с точностью
1e-20
Пример
Для примера рассмотрим JSON документ
{
"left": [1, 2],
"right": [4, "Inaros"]
}
и разберем по шагам выполнение запроса lax $.left < $.right
:
- Автоматическая распаковка массивов в левом и правом аргументе. В качестве левого аргумента получаем последовательность
1, 2
, в качестве правого4, "Iranos"
- Рассматриваем пару
(1, 4)
. Сравнение проходит успешно,1 < 4
это истина. Устанавливаем флагFOUND
- Поскольку выполнение происходит в
lax
режиме и установлен флагFOUND
, больше никакие пары мы не рассматриваем - Поскольку установлен флаг
FOUND
, оператор возвращает истину
Разберем тот же запрос, но в другом режиме выполнения: strict $.left < $.right
:
- Автоматическая распаковка массивов в левом и правом аргументе. В качестве левого аргумента получаем последовательность
1, 2
, в качестве правого4, "Iranos"
- Рассматриваем пару
(1, 4)
. Сравнение проходит успешно,1 < 4
это истина. Устанавливаем флагFOUND
- Рассматриваем пару
(2, 4)
. Сравнение проходит успешно,2 < 4
это истина. Устанавливаем флагFOUND
- Рассматриваем пару
(1, "Iranos")
. Сравнение завершается ошибкой (число нельзя сравнить со строкой). Устанавливаем флагERROR
- Рассматриваем пару
(2, "Iranos")
. Сравнение завершается ошибкой (число нельзя сравнить со строкой). Устанавливаем флагERROR
- Поскольку установлен флаг
ERROR
, оператор возвращаетnull
Предикаты
JsonPath поддерживает предикаты - выражения, возвращающие булевское значение, и проверяющие некоторое условие. Их можно использовать, например, в фильтрах.
like_regex
Предикат like_regex
позволяет проверить строку на соответствие регулярному выражению. Синтаксис регулярных выражений такой же как в Hyperscan UDF и REGEXP.
Синтаксис
<expression> like_regex <regexp string> [flag <flag string>]
Где:
<expression>
- это JsonPath выражение, содержащее строки, которые следует проверить на соответствие регулярному выражению<regexp string>
- это строка, содержащая регулярное выражениеflag <flag string>
- это опциональная секция, в которой<flag string>
является строкой с флагами для исполнения регулярного выражения
Поддерживаемые флаги:
i
- отключение чувствительности к регистру
Исполнение
Перед проверкой производится автоматическая распаковка массивов во входной последовательности.
После этого для каждого элемента входной последовательности:
- Выполняется проверка на соответствие элемента регулярному выражению
- Если элемент не является строкой, устанавливается флаг
ERROR
- Если результат проверки это истина, устанавливается флаг
FOUND
- Если один из флагов
ERROR
илиFOUND
установлен и запрос исполняется вlax
режиме, больше никакие пары не рассматриваются
Если после рассмотрения пар:
- Установлен флаг
ERROR
, предикат возвращаетnull
- Установлен флаг
FOUND
, предикат возвращаетtrue
- Иначе предикат возвращает
false
Примеры
"123456" like_regex "^[0-9]+$"
возвращаетtrue
"123abcd456" like_regex "^[0-9]+$"
возвращаетfalse
"Naomi Nagata" like_regex "nag"
возвращаетfalse
"Naomi Nagata" like_regex "nag" flag "i"
возвращаетtrue
starts with
Предикат starts with
позволяет проверить является ли одна строка префиксом другой.
Синтаксис
<string expression> starts with <prefix expression>
Где:
<string expression>
- это JsonPath выражение, содержащее строку, которую нужно проверить<prefix expression>
- это JsonPath выражение, содержащее строку-префикс
То есть предикат будет проверять что <string expression>
начинается со строки <prefix expression>
.
Исполнение
Первый аргумент предиката должен быть единственной строкой.
Второй аргумент предиката должен быть последовательностью (возможно, из нескольких) строк.
Для каждого элемента из последовательности строк-префиксов:
- Выполняется проверка "является ли элемент префиксом входной строки"
- Если элемент не является строкой, устанавливается флаг
ERROR
- Если результат проверки это истина, устанавливается флаг
FOUND
- Если один из флагов
ERROR
илиFOUND
установлен и запрос исполняется вlax
режиме, больше никакие пары не рассматриваются
Если после рассмотрения пар:
- Установлен флаг
ERROR
, предикат возвращаетnull
- Установлен флаг
FOUND
, предикат возвращаетtrue
- Иначе предикат возвращает
false
Примеры
"James Holden" starts with "James"
возвращаетtrue
"James Holden" starts with "Amos"
возвращаетfalse
exists
Предикат exists
позволяет проверить, возвращает ли JsonPath выражение хотя бы один элемент.
Синтаксис
exists (<expression>)
Где <expression>
- это JsonPath выражение, которое нужно проверить. Скобки вокруг выражения обязательны.
Исполнение
- Исполняется переданное JsonPath выражение
- Если в результате исполнения возникла ошибка, предикат возвращает
null
- Если в результате исполнения была получена пустая последовательность, предикат возвращает
false
- Иначе предикат возвращает
true
Примеры
Рассмотрим JSON документ:
{
"profile": {
"name": "Josephus",
"surname": "Miller"
}
}
exists ($.profile.name)
возвращаетtrue
exists ($.friends.profile.name)
возвращаетfalse
strict exists ($.friends.profile.name)
возвращаетnull
, потому что вstrict
режиме обращение к несуществующим ключам объекта это ошибка
is unknown
Предикат is unknown
позволяет проверить, является ли булевское значение null
.
Синтаксис
(<expression>) is unknown
Где <expression>
- это JsonPath выражение, которое нужно проверить. Допускаются только выражения, возвращающие булевское значение. Скобки вокруг выражения обязательны.
Исполнение
- Если переданное выражение возвращает
null
, предикат возвращаетtrue
- Иначе предикат возвращает
false
Примеры
(1 == 2) is unknown
возвращаетfalse
. Выражение1 == 2
вернулоfalse
, что не являетсяnull
(1 == "string") is unknown
возвращаетtrue
. Выражение1 == "string"
вернулоnull
, поскольку в JsonPath строки и числа не сравнимы
Фильтры
JsonPath позволяет фильтровать значения, полученные в ходе выполнения запроса.
Выражение в фильтре должно возвращать булевское значение.
Перед фильтрацией производится автоматическая распаковка массивов во входной последовательности.
Для каждого элемента входной последовательности:
- Значение текущего фильтруемого объекта
@
становится равным текущему элементу входной последовательности - Исполняется выражение в фильтре
- Если в ходе выполнения выражения возникла ошибка, текущий элемент входной последовательности пропускается
- Если результат исполнения выражения это единственное значение
true
, текущий элемент добавляется в результат фильтра
Пример
Пусть у нас есть JSON документ, описывающий друзей пользователя
{
"friends": [
{
"name": "James Holden",
"age": 35,
"money": 500
},
{
"name": "Naomi Nagata",
"age": 30,
"money": 345
}
]
}
и мы хотим получить с помощью JsonPath запроса друзей которые старше 32 лет. Для этого можно написать следующий запрос:
$.friends ? (@.age > 32)
Разберем запрос по частям:
$.friends
- обращение к массивуfriends
в JSON документе? ( ... )
- синтаксис фильтра. Выражение внутри скобок называется предикатом@
- обращение к текущему фильтруемому объекту. В нашем примере это объект, описывающий друга пользователя@.age
- обращение к полюage
текущего фильтруемого объекта@.age > 32
- сравнение поляage
со значением 32. В результате выполнения запроса останутся только те значения, для которых данный предикат вернул истину
В результате выполнения этого запроса будет получен только первый друг из массива друзей пользователя.
Как и многие другие операторы языка JsonPath, фильтры можно выстраивать в цепочки. Разберем более сложный запрос, который отбирает имена друзей которые старше 20 лет и которые имеют меньше чем 400 единиц валюты:
$.friends ? (@.age > 20) ? (@.money < 400) . name
Разберем запрос по частям:
$.friends
- обращение к массивуfriends
в JSON документе? (@.age > 20)
- первый фильтр. Поскольку все друзья старше 20, он просто вернет все элементы массиваfriends
? (@.money < 400)
- второй фильтр. Вернет только второй элемент массиваfriends
, поскольку только у него полеmoney
имеет значение меньше 400.name
- обращение к полюname
у отфильтрованных объектов
В результате выполнения этого запроса будет получена последовательность из одного элемента - "Naomi Nagata"
.
На практике рекомендуется объединять несколько фильтров в один если есть такая возможность. Рассмотренный запрос эквивалентен $.friends ? (@.age > 20 && @.money < 400) . name
.
Методы
JsonPath поддерживает методы - функции, которые преобразуют одни последовательности значений в другие. Синтаксис вызова метода похож на обращение к ключу объекта:
$.friends.size()
Также как и обращение к ключам объекта, вызовы методов можно выстраивать в цепочки:
$.numbers.double().floor()
type
Метод type
возвращает строчку с названием типа переданного значения.
Для каждого элемента входной последовательности метод добавляет строчку в выходную последовательность в соответствии с табличкой:
Тип значения | Строка с названием типа |
---|---|
Null | "null" |
Булевское значение | "boolean" |
Число | "number" |
Строка | "string" |
Массив | "array" |
Объект | "object" |
Примеры
"Naomi".type()
возвращает"string"
false.type()
возвращает"boolean"
size
Метод size
возвращает размер массива.
Для каждого элемента входной последовательности метод добавляет в выходную последовательность:
- Размер массива, если тип элемента это массив
- Для всех остальных типов (включая объекты)
1
Примеры
Рассмотрим JSON документ:
{
"array": [1, 2, 3],
"object": {
"a": 1,
"b": 2
},
"scalar": "string"
}
И запросы к нему:
$.array.size()
возвращает3
$.object.size()
возвращает1
$.scalar.size()
возвращает1
double
Метод double
конвертирует строки в числа.
Перед выполнением производится автоматическая распаковка массивов во входной последовательности.
Все элементы во входной последовательности должны быть строками, которые содержат числа в десятичной записи. Допускается указание дробной части и экспоненты.
Примеры
"125".double()
возвращает125
"125.456".double()
возвращает125.456
"125.456e-3".double()
возвращает0.125456
ceiling
Метод ceiling
округляет числа вверх.
Перед выполнением производится автоматическая распаковка массивов во входной последовательности.
Все элементы во входной последовательности должны быть числами.
Примеры
(1.3).ceiling()
возвращает2
(1.8).ceiling()
возвращает2
(1.5).ceiling()
возвращает2
(1.0).ceiling()
возвращает1
floor
Метод floor
округляет числа вниз.
Перед выполнением производится автоматическая распаковка массивов во входной последовательности.
Все элементы во входной последовательности должны быть числами.
Примеры
(1.3).floor()
возвращает1
(1.8).floor()
возвращает1
(1.5).floor()
возвращает1
(1.0).floor()
возвращает1
abs
Метод abs
вычисляет абсолютное значение числа (убирает знак).
Перед выполнением производится автоматическая распаковка массивов во входной последовательности.
Все элементы во входной последовательности должны быть числами.
Примеры
(0.0).abs()
возвращает0
(1.0).abs()
возвращает1
(-1.0).abs()
возвращает1
keyvalue
Метод keyvalue
конвертирует объект в последовательность пар ключ-значение.
Перед выполнением производится автоматическая распаковка массивов во входной последовательности.
Все элементы во входной последовательности должны быть объектами.
Для каждого элемента входной последовательности:
- Рассматривается каждая пара ключ-значение в элементе
- Для каждой такой пары формируется объект с ключами
name
иvalue
name
хранит строку с названием ключа из парыvalue
хранит значение из пары- Все объекты для этого элемента добавляются в выходную последовательность
Примеры
Рассмотрим JSON документ:
{
"name": "Chrisjen",
"surname": "Avasarala",
"age": 70
}
Для него запрос $.keyvalue()
вернет последовательность:
{
"name": "age",
"value": 70
},
{
"name": "name",
"value": "Chrisjen"
},
{
"name": "surname",
"value": "Avasarala"
}
Переменные
Функции использующие JsonPath могут передавать значения внутрь запроса, они называются переменными. Чтобы обратиться к переменной, нужно написать символ $
и название переменной: $variable
.
Пример
Пусть переменная planet
равна
{
"name": "Mars",
"gravity": 0.376
}
Тогда запрос strict $planet.name
даст "Mars"
.
В отличии от многих языков программирования, JsonPath не поддерживает создание новых переменных или изменение существующих.
Общие аргументы
Все функции для работы с JSON принимают:
- JSON значение (может быть произвольным выражением типа
Json
илиJson?
) - JsonPath запрос (должен быть явно указан строковым литералом)
- (Опционально)
PASSING
секция
PASSING секция
Позволяет передавать значения в JsonPath запрос в качестве переменных.
Синтаксис
PASSING
<expression 1> AS <variable name 1>,
<expression 2> AS <variable name 2>,
...
<expression>
может быть следующих типов:
- Числа,
Date
,DateTime
иTimestamp
(будет произведенCAST
в типDouble
перед передачей в JsonPath) Utf8
,Bool
иJson
Имя переменной <variable name>
можно указать несколькими способами:
- Как SQL имя, например
variable
- В кавычках, например
"variable"
Пример
JSON_VALUE(
$json,
"$.timestamp - $Now + $Hour"
PASSING
24 * 60 as Hour,
CurrentUtcTimestamp() as "Now"
)
JSON_EXISTS
Функция JSON_EXISTS
позволяет проверить, удовлетворяет ли JSON значение указанному JsonPath.
Синтаксис
JSON_EXISTS(
<JSON expression>,
<JsonPath query>,
[<PASSING clause>]
[{TRUE | FALSE | UNKNOWN | ERROR} ON ERROR]
)
Возвращаемое значение: Bool?
Значения по умолчанию: если ON ERROR
секция не указана, используется FALSE ON ERROR
Поведение:
-
Если
<JSON expression>
этоNULL
или пустойJson?
, то возвращается пустойBool?
-
Если при выполнении JsonPath возникла ошибка, то возвращаемое значение зависит от
ON ERROR
секции:TRUE
- вернутьTrue
FALSE
- вернутьFalse
UNKNOWN
- вернуть пустойBool?
ERROR
- завершить весь запрос с ошибкой
-
Если результат выполнения JsonPath - это одно или несколько значений, возвращается
True
-
Иначе возвращается
False
Примеры
$json = CAST(@@{
"title": "Rocinante",
"crew": [
"James Holden",
"Naomi Nagata",
"Alex Kamai",
"Amos Burton"
]
}@@ as Json);
SELECT
JSON_EXISTS($json, "$.title"), -- True
JSON_EXISTS($json, "$.crew[*]"), -- True
JSON_EXISTS($json, "$.nonexistent"); -- False, так как JsonPath вернет пустой результат
SELECT
-- Ошибка JsonPath, вернет False, так как по умолчанию используется FALSE ON ERROR
JSON_EXISTS($json, "strict $.nonexistent");
SELECT
-- Ошибка JsonPath, весь YQL запрос завершится ошибкой
JSON_EXISTS($json, "strict $.nonexistent" ERROR ON ERROR);
JSON_VALUE
Функция JSON_VALUE
позволяет извлечь из JSON скалярное значение (все что не массив и не объект).
Синтаксис
JSON_VALUE(
<JSON expression>,
<JsonPath query>,
[<PASSING clause>]
[RETURNING <type>]
[{ERROR | NULL | DEFAULT <expr>} ON EMPTY]
[{ERROR | NULL | DEFAULT <expr>} ON ERROR]
)
Возвращаемое значение: <type>?
Значения по умолчанию:
- Если
ON EMPTY
секция не указана, используетсяNULL ON EMPTY
- Если
ON ERROR
секция не указана, используетсяNULL ON ERROR
- Если
RETURNING
секция не указана, в качестве<type>
используетсяUtf8
Поведение:
-
Если
<JSON expression>
этоNULL
или пустойJson?
, то возвращается пустой<type>?
-
Если возникла ошибка, то возвращаемое значение зависит от
ON ERROR
секции:NULL
- вернуть пустой<type>?
ERROR
- завершить весь запрос с ошибкойDEFAULT <expr>
- вернуть<expr>
, предварительно сделавCAST
в тип<type>?
. ЕслиCAST
не удастся, весь запрос завершится с ошибкой
-
Если в результате выполнения JsonPath получился пустой результат, то возвращаемое значение зависит от
ON EMPTY
секции:NULL
- вернуть пустой<type>?
ERROR
- завершить весь запрос с ошибкойDEFAULT <expr>
- вернуть<expr>
, предварительно сделавCAST
в тип<type>?
. ЕслиCAST
не удастся, то поведение соответствуетON ERROR
секции
-
Если результат выполнения JsonPath - это одно значение, то:
- Если
RETURNING
секция не была указана, значение конвертируется вUtf8
- Иначе производится
CAST
значения в<type>
. ЕслиCAST
не удастся, то поведение соответствуетON ERROR
секции. При этом значение из JSON должно "соответствовать" типу<type>
.
- Если
-
Вернуть полученный результат
Определим соответствие JSON типов YQL типам:
- JSON Number - числовые типы,
Date
,DateTime
иTimestamp
- JSON Bool -
Bool
- JSON String -
Utf8
иString
Ошибками при выполнении JSON_VALUE
считаются:
- Ошибки во время вычисления JsonPath
- Результат выполнения JsonPath - это несколько значений или не скалярное значение
- Тип полученного из JSON значения не совпадает с ожидаемым
RETURNING
секция поддерживает типы чисел, Date
, DateTime
, Timestamp
, Utf8
, String
и Bool
.
Примеры
$json = CAST(@@{
"friends": [
{
"name": "James Holden",
"age": 35
},
{
"name": "Naomi Nagata",
"age": 30
}
]
}@@ as Json);
SELECT
JSON_VALUE($json, "$.friends[0].age"), -- "35" (тип Utf8?)
JSON_VALUE($json, "$.friends[0].age" RETURNING Uint64), -- 35 (тип Uint64?)
JSON_VALUE($json, "$.friends[0].age" RETURNING Utf8); -- пустой Utf8?, так как произошла ошибка. Тип JSON значения Number не соответствует строковому типу Utf8
SELECT
-- "empty" (тип String?)
JSON_VALUE(
$json,
"$.friends[50].name"
RETURNING String
DEFAULT "empty" ON EMPTY
);
SELECT
-- 20 (тип Uint64?). Результат выполнения JsonPath пустой, но значение
-- по умолчанию из секции ON EMPTY не может быть приведено к Uint64.
-- Поэтому используется значение из ON ERROR
JSON_VALUE(
$json,
"$.friends[50].age"
RETURNING Uint64
DEFAULT -1 ON EMPTY
DEFAULT 20 ON ERROR
);
JSON_QUERY
Функция JSON_QUERY
позволяет извлекать из JSON массивы и объекты.
Синтаксис
JSON_QUERY(
<JSON expression>,
<JsonPath query>,
[<PASSING clause>]
[WITHOUT [ARRAY] | WITH [CONDITIONAL | UNCONDITIONAL] [ARRAY] WRAPPER]
[{ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT} ON EMPTY]
[{ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT} ON ERROR]
)
Возвращаемое значение Json?
Значения по умолчанию
- Если
ON EMPTY
секция не указана, используетсяNULL ON EMPTY
- Если
ON ERROR
секция не указана, используетсяNULL ON ERROR
- Если
WRAPPER
секция не указана, используетсяWITHOUT WRAPPER
- Если указана секция
WITH WRAPPER
, но не указаноCONDITIONAL
илиUNCONDITIONAL
, используетсяUNCONDITIONAL
Поведение:
Примечание
Указывать секции WITH ... WRAPPER
и ON EMPTY
одновременно нельзя
-
Если
<JSON expression>
этоNULL
или пустойJson?
, то возвращается пустойJson?
-
Если указана
WRAPPER
секция, то:WITHOUT WRAPPER
илиWITHOUT ARRAY WRAPPER
- никак не преобразовывать результат выполнения JsonPathWITH UNCONDITIONAL WRAPPER
илиWITH UNCONDITIONAL ARRAY WRAPPER
- обернуть результат выполнения JsonPath в массивWITH CONDITIONAL WRAPPER
илиWITH CONDITIONAL ARRAY WRAPPER
- обернуть результат выполнения JsonPath в массив если он не является единственным массивом или объектом
-
Если в результате выполнения JsonPath получился пустой результат, то возвращаемое значение зависит от
ON EMPTY
секции:NULL
- вернуть пустойJson?
ERROR
- завершить весь запрос с ошибкойEMPTY ARRAY
- вернуть пустой JSON массив,[]
EMPTY OBJECT
- вернуть пустой JSON объект,{}
-
Если возникла ошибка, то возвращаемое значение зависит от
ON ERROR
секции:NULL
- вернуть пустойJson?
ERROR
- завершить весь запрос с ошибкойEMPTY ARRAY
- вернуть пустой JSON массив,[]
EMPTY OBJECT
- вернуть пустой JSON объект,{}
-
Вернуть полученный результат
Ошибками при выполнении JSON_QUERY
считаются:
- Ошибки во время вычисления JsonPath
- Результат выполнения JsonPath - это несколько значений (даже после применения секции
WRAPPER
) или скалярное значение
Примеры
$json = CAST(@@{
"friends": [
{
"name": "James Holden",
"age": 35
},
{
"name": "Naomi Nagata",
"age": 30
}
]
}@@ as Json);
SELECT
JSON_QUERY($json, "$.friends[0]"); -- {"name": "James Holden", "age": 35}
SELECT
JSON_QUERY($json, "$.friends.name" WITH UNCONDITIONAL WRAPPER); -- ["James Holden", "Naomi Nagata"]
SELECT
JSON_QUERY($json, "$.friends[0]" WITH CONDITIONAL WRAPPER), -- {"name": "James Holden", "age": 35}
JSON_QUERY($json, "$.friends.name" WITH CONDITIONAL WRAPPER); -- ["James Holden", "Naomi Nagata"]