5. JSON

5.1. Введение в SQL/JSON

СУБД РЕД База Данных 5 поддерживает утвержденный ISO стандарт SQL/JSON, который описывает функционал для работы с JSON данными из SQL.

JSON — это текстовый формат хранения данных, который состоит из ключевых элементов и их комбинаций:

Объект (пара или набор пар в формате ключ:значение);
```
{"id": 32}
```
Массив (список значений, заключенных в квадратные скобки);
```
[10, 3, 45, 4, 5]
```
Скаляр (число, строка, заключенная в двойные кавычки, логическое значение);
Значения null.

В качестве элементов массива и значений объектов можно использовать другие массивы и объекты.

В приведенном ниже примере JSON-текст представляет собой объект, который содержит массив и строковое значение:

{
   "RESPONSE": [{"CODE": 404},
                {"CODE": 418},
                null],
   "ELEMENT": "BUTTON"
}

Функционал SQL/JSON включает различные функции для генерации, запроса и валидации данных в JSON:

Функция	Запроса		Генерации		Валидации
Функция	`JSON_VALUE` `JSON_TABLE`	`JSON_QUERY` `JSON_MODIFY`	`JSON_ARRAY` `JSON_OBJECT`	`JSON_ARRAYAGG` `JSON_OBJECTAGG`	`IS JSON` `JSON_EXIST`
Принимает	JSON-текст и JSON-путь		Набор любых аргументов	Селективный столбец	JSON-текст
Возвращает	`VARCHAR`	`VARCHAR (JSON)`			`BOOLEAN`

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

5.2. Язык путей JSON

Для получения данных из объекта в SQL JSON используется Язык путей JSON. В пути указывается, к какому именно элементу нужно получить доступ. Например, '$.ID' обратится к элементу JSON с ключом ID.

Общий синтаксис языка путей:

<выражение пути> ::= '<режим> <путь>'

<путь> ::=  $[<cпособ доступа>...] [<метод элемента>] [<выражение фильтра>]

<способ доступа> ::=  <доступ к элементу объекта>
                    | <доступ к элементу массива>

Язык путей SQL/JSON чувствителен к регистру как в идентификаторах, так и в ключевых словах, нет автоматического преобразования идентификаторов в верхний регистр.

Выражение пути является строкой, поэтому его необходимо заключить в одинарные кавычки. Внутри этой строки можно написать строковый литерал, заключив его в двойные кавычки. Апостроф внутри двойных кавычек нужно экранировать. Это можно сделать, используя либо соглашение SQL о написании символа дважды, либо экранирование JavaScript.

Экранирование методом написания дважды:
```
'$."Name''"'
```
Кавычки в примере интерпретируются следующим образом:
- Внешние одинарные кавычки содержат выражение пути;
- Двойные кавычки содержат символьную строку на языке пути;
- Внутренний апостроф экранируется, поскольку содержится в строковом литерале. Фактически обозначает один символ.
Экранирование по методу JavaScript, то есть \':
```
'$."Name\'"'
```
Экранирование методом записывания символа в unicode, например:
```
'$."Name\ u0027"'
```
Экранирование двойных кавычек:
```
'$."\"Name"'
```

Режимы: lax и strict

Механизм пути имеет два режима: lax и strict.

Предупреждение

На данный момент режим strict не проверяет соответствие JSON указанному JSON Path.

Объявление режима:

<выражение пути> ::= <режим> <путь>

<режим> ::=  strict | lax

По умолчанию используется режим lax.

Режим lax преобразует ошибки в пустые последовательности SQL/JSON.

Режимы пути регулируют три аспекта: разворачивание массивов, оборачивание элементов в массив и обработку ошибок.

В режиме lax массив, содержащий только один элемент, является взаимозаменяемым с этим значением, например, ["hello"] эквивалентно "Hello". Это подкрепляется следующими правилами:

Если для операции требуется массив, но операнд не является массивом, то операнд оборачивается в массив;
Если для операции требуется не массив, но операнд является массивом, то операнд разворачивается в последовательность.

Разворачивание массивов:

В режиме lax – массивы автоматически разворачиваются перед выполнением операции, это означает, что [*] можно опустить ;
В режиме strict – массивы не разворачиваются автоматически (в таком случае нужно написать [*], чтобы развернуть массив).

Оборачивание элементов в массив:

В режиме lax – операции пути, применяющиеся к индексу, такие как $[0] или $[*], могут быть применены к значению, которое не является массивом, но для этого значение неявно оборачивается в массив перед применением операции;
В режиме strict – автоматическое оборачивание перед выполнением операций, применяющихся к индексу, не выполняется.

Обработка ошибок:

В режиме lax – многие ошибки, связанные с тем, являются ли данные массивом или скаляром, обрабатываются функциями автоматического разворачивания и оборачивания. Остальные ошибки классифицируются либо как структурные, либо как неструктурные. Примером структурной ошибки является $.name, если в $ нет элемента, ключом которого является name. Структурные ошибки преобразуются в пустые последовательности SQL/JSON. Примером неструктурной ошибки является деление на ноль;
В режиме strict – ошибки строго определены во всех случаях.

5.3. Доступ к элементам

Синтаксис обращения к элементам:

<выражение пути> ::= <режим> <путь>

<путь> ::=  <доступ к элементу>
         | [<метод элемента>]
         | [<выражение фильтра>]

<доступ к элементу> ::= <контекстная переменная>[<cпособ доступа>...]

<способ доступа> ::=  <доступ к элементу объкта>
                    | <обращение к элементу объекта подстановочным знаком>
                    | <доступ к элементу массива>
                    | <обращение к элементу массива подстановочным знаком>

Обращение к элементу объекта

Синтаксис обращения к элементу объекта:

<доступ к элементу объекта> ::=  .<ключ>
                               | .<строка>
                               | .<*>

Есть три способа определения ключа.

Первый способ — написание имени ключа открытым текстом. Применяется, если имя ключа не начинается со знака доллара ($) и удовлетворяет правилам идентификатора JavaScript. Например:

$.name
$.firstName
$.Phone

Второй способ — написание имени ключа строкой. Это нужно, когда имя ключа начинается со знака доллара ($) или содержит специальные символы. Например:

$."name"
$."$price"
$."home address"

Третий способ — обращение к элементу объекта с помощью подстановочного символа. Например, * запросит все поля объекта:

$.*

Обращение к элементу массива

<доступ к элементу массива> ::= [ <список индексов> ]

<список индексов> ::= <индекс> [ {, <индекс> }... ]

<индекс> ::=  <диапазон номеров>
            | <номер> to <номер>
            | *

<номер> ::= <число> | last | <числовое выражение>

Квадратные скобки содержат список индексов, разделённых запятыми. Список индексов может быть определён двумя способами: числом или диапазоном между двумя числами, указанным с ключевым словом to. Также к элементу массива можно обратиться с помощью подстановочного знака [*]. Например, $[*] развернёт массив в последовательность элементов. В режиме strict операнд должен быть массивом. В режиме lax операнд, не являющийся массивом, будет обёрнут в массив.

Индексы рассчитываюся с 0. Таким образом, [0] — это первый элемент в массиве.

Для доступа к последнему элементу массива неизвестного размера можно использовать ключевое слово last в качестве индекса. Например, $[last] обратится к последнему элементу массива, а $[last-1 to last] вернёт два последних элемента массива.

Например:

$[0, last-1 to last, 5]

В данном случае обратятся к первому элементу массива, двум последним и шестому.

В режиме lax выражение $[*] аналогично $[0 to last]. В режиме strict между такими обращениями есть различие: $[0 to last] требует, чтобы массив содержал хотя бы один элемент, тогда как $[*] не вернёт ошибку, если $ является пустым массивом.

К элементам массива можно обращаться по отрицательным индексам. Например, $[-1] обратится к последнему элементу массива, а $[-2] к предпоследнему.

Индексы могут быть указаны в любой последовательности и могут повторяться. В режиме strict индекс должен быть числовым значением в диапазоне между 0 и last. В режиме lax индексы, выходящие за границы массива игнорируются. Нечисловые значения индексов в любом режиме будут считаться ошибкой.

5.4. Методы элементов

Методы элементов — это методы, которые можно использовать в JSON Path.

Синтаксис методов элементов:

<выражение пути> ::= '<режим> <путь>'

<путь> ::=  $[<доступ к элементу>] [<метод элемента>] [<выражение фильтра>]

<метод элемента> ::= .<метод>

<метод> ::= type()
          | size()
          | double()
          | ceiling()
          | floor()
          | abs()
          | keyvalue()

Методы элементов разворачивают массив в режиме lax. Исключение — методы type() и size(), иначе было бы невозможно узнать их тип или размер.

Метод type()

Метод type() возвращает строку с именем типа элемента SQL/JSON:

Если элемент – null, то вернёт "null";
Если элемент – true или false, то вернёт "boolean";
Если элемент – число, то вернёт "number";
Если элемент – строка, то вернёт "string";
Если элемент – массив, то вернёт "array";
Если элемент – объект, то вернёт "object".

Например, можно вывести только строки:

SELECT JSON_QUERY(
  '{"data":[123,"123","words",false,true,null,[],{}]}', '$.* ? (@.type()=="string")'
  RETURNING VARCHAR(100) WITH ARRAY WRAPPER) FROM RDB$DATABASE;

==================================================
["123","words"]

Или вывести типы элементов:

SELECT JSON_QUERY(
  '{"data":[123,"123","words",false,true,null,[],{}]}', '$.data[*].type()'
  RETURNING VARCHAR(100) WITH ARRAY WRAPPER) FROM RDB$DATABASE;

==================================================
["number","string","string","boolean","boolean","null","array","object"]

Метод size()

Метод size() возвращает размер элемента SQL/JSON:

Размер массива равен количеству элементов в массиве;
Размер объекта или скаляра равен 1.

Например, можно вывести массивы, размер которых больше 1:

SELECT JSON_QUERY('[[1, 2, 3],[1],[1, 2]]', '$ ? (@.type()=="array" && @.size()>1)'
                  RETURNING VARCHAR(100) WITH ARRAY WRAPPER) FROM RDB$DATABASE;

==================================================
[[1,2,3],[1,2]]

Можно посчитать количество элементов в массиве:

SELECT JSON_QUERY('{"data":[1, 2, 3, 4, 5, 6, 7, 8, 9]}', '$.data.size()'
                   RETURNING VARCHAR(100) WITH ARRAY WRAPPER) FROM RDB$DATABASE;

==================================================
[9]

Числовые методы элементов: double(), ceiling(), floor(), abs()

Числовые методы элементов выполняют общие числовые функции:

double() преобразует строку в приблизительное числовое значение;
ceiling(), floor() и abs() выполняют те же операции, что и CEILING, FLOOR и ABS в SQL.

Все эти функции при обработке значения null возвращают "null". Если числовые методы применяются не к числу (например, к объекту), то результатом будет ошибка, но если присутствует оператор сравнения, то результатом будет "Unknown".

Пример применения метода double():

SELECT JSON_VALUE('{"numbers": "555"}', '$.numbers.double()') FROM RDB$DATABASE;

=========================
555

Пример применения метода abs():

SELECT JSON_VALUE('{"numbers": -555.25}', '$.numbers.abs()') FROM RDB$DATABASE;

=========================
555.25

Пример применения метода ceiling():

SELECT JSON_VALUE('{"numbers": 555.25}', '$.numbers.ceiling()') FROM RDB$DATABASE;

=========================
556

Пример применения метода floor():

SELECT JSON_VALUE('{"numbers": 555.25'}', '$.numbers.floor()') FROM RDB$DATABASE;

=========================
555

Метод keyvalue()

Метод keyvalue() преобразовывает поля JSON-объекта в последовательность объектов, описывающих поля исходного.

SELECT JSON_QUERY(
       '{ "who": "Fred", "what": 64 }', '$.keyvalue()'
       RETURNING VARCHAR WITH ARRAY WRAPPER ERROR ON ERROR) FROM RDB$DATABASE;

=======================
[{"name":"who","value":Fred,"id":1},{"name":"what","value":64,"id":1}]

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

name - имя ключа во входном объекте;
value - значение ключа;
id - целое число, являющееся уникальным идентификатором входного JSON-объекта.

В результирующей JSON-последовательности объекты будут в том же порядке, что и поля в исходном объекте, из которых они преобразованы.

В режиме lax вывод keyvalue() разворачивается:

SELECT JSON_QUERY(
       '[{"who":"Fred","what": 64}, {"who":"Fred","what": 64}] ','lax $.keyvalue()'
       RETURNING VARCHAR(100) WITH ARRAY WRAPPER ERROR ON ERROR) FROM RDB$DATABASE;

=======================
[ { name: "who", value: "Fred", id: 1 }, { name: "what", value: 64, id: 1 },
  { name: "who", value: "Moe",  id: 2 }, { name: "how",  value: 22, id: 2 } ]

5.5. Арифметические операции в пути

Язык путей JSON поддерживает следующие арифметические операции:

Унарные: + и -;
Бинарные: +, -, *, /, %.

Модуль, обозначаемый символом %, использует тот же алгоритм, что и функция MOD в SQL.

Унарный плюс и минус

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

Унарные операции выполняются после получения значений из JSON и после выполнения метода. Например:

$ = { readings: [15.2, -22.3, 45.9] }

Тогда выражение 'lax -$.readings.floor()' эквивалентно lax -($.readings.floor())

В режиме lax унарные операции разворачивают массив.

Таблица 5.1 Вычисление `lax -$.readings.floor()`
Шаг	Выражение	Значение
1	$	{ readings: [15.2, -22.3, 45.9] }
2	$.readings	[15.2, -22.3, 45.9 ]
3	$.readings.floor()	15, -23, 45
4	-$.readings.floor()	-15, 23, -45

Для получения другого порядка вычислений необходимо использовать скобки: lax (-$.readings).floor().

Таблица 5.2 Вычисление `lax (-$.readings).floor()`
Шаг	Выражение	Значение
1	$	{ readings: [15.2, -22.3, 45.9] }
2	$.readings	[15.2, -22.3, 45.9 ]
3	-$.readings	-15.2, 22.3, -45.9
4	(-$.readings)	-15.2, 22.3, -45.9
5	(-$.readings).floor()	-16, 22, -46

В режиме strict эти примеры требуют явного [*] для раскрытия массива:

strict -$.readings[*].floor()

или

strict (-$.readings[*]).floor()

Бинарные операции

Бинарные операции не выполняют итерации по последовательности JSON. Они требуют, чтобы их операнд был числом, иначе результатом будет ошибка, даже в режиме lax. Бинарные операторы имеют тот же приоритет, что и в SQL.

Пример сложения:

SELECT JSON_QUERY(
       '{"digits": [15.2, -22, 45, 0]}', '$.digits[*]-5.1'
       RETURNING VARCHAR(50) WITH ARRAY WRAPPER ERROR ON ERROR) FROM RDB$DATABASE;

JSON_QUERY
==================================================
[10.1,-27.1,39.9,-5.1]

Для изменения порядка выполнения нужно использовать скобки:

SELECT JSON_VALUE('{"value": 15}', '(-$.value)+2*3-15/5%2'
                   RETURNING VARCHAR(20) ERROR ON ERROR) FROM RDB$DATABASE;

==============
-10

SELECT JSON_VALUE('{"value": 15}', '-($.value+2*3-15/5%2)'
                   RETURNING VARCHAR(20) ERROR ON ERROR) FROM RDB$DATABASE;

==============
-20

5.6. Фильтры

Выражение фильтра аналогично предложению WHERE в SQL — оно используется для нахождения элементов, удовлетворяющих определённому условию.

Синтаксис выражения фильтра:

<выражение пути> ::= '<режим> <путь>'

<путь> ::=  $[<cпособ доступа>...] [<метод элемента>] [<выражение фильтра>]

<выражение фильтра> ::= ? ( <предикат> )

<предикат> ::= $[<cпособ доступа>...] [<метод элемента>]
             | @[<cпособ доступа>...] [<метод элемента>]
             | (<предикат>)
             | <логический оператор>  <предикат>
             | <оператор сравнения>   <предикат>
             | <оператор exists>
             | <оператор like_regex>
             | <оператор starts with>
             | <оператор is unknown>

При использовании фильтра выполняются следующие действия:

В режиме lax массивы в операнде разворачиваются;
Предикат вычисляется для каждого элемента в последовательности;
В результат попадают элементы, для которых предикат принял значение True.

Переменная @ в фильтре используется для обозначения текущего элемента в последовательности.

Язык путей содержит следующие предикаты:

exists, чтобы проверить, получает ли выражение пути непустой результат;
Операторы сравнения ==, !=, <>, <, <=, >, и >=;
like_regex для сопоставления значения выражения с шаблоном;
starts with чтобы проверить, является ли второй операнд началом первого операнда;
is unknown, чтобы проверить, является ли результат сравнения значением unknown. Предикат возвращает true или false.

Обработка ошибок в фильтрах

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

Если операнды являются выражениями, то вычисление выражений может привести к возникновению ошибки. При вычислении выражений режим lax преобразует структурные ошибки в пустую последовательность. Неструктурные ошибки рассматриваются как необработанные ошибки. В режиме strict все ошибки являются необработанными;
Если после вычисления операнда предикат обнаружил, что значение не подходит. Например, 10 == "ten", в операндах нет ошибок, но они несопоставимы, поэтому в этом предикате все еще есть ошибка.

При любом типе ошибки предикат возвращает значение unknown.

Логические операторы

Операнды && (логическое И) будут вычисляться в любом случае.

Результаты && представлены в таблице:

	True	False	Unknown
True	True	False	Unknown
False	False	False	False
Unknown	Unknown	False	Unknown

Аналогично, операнды || (логическое ИЛИ) будут вычисляться в любом случае.

Возможные результаты || представлены в таблице:

	True	False	Unknown
True	True	True	True
False	True	False	Unknown
Unknown	True	Unknown	Unknown

Результаты ! (логическое отрицание):

P	NOT P
True	False
False	True
Unknown	Unknown

Операторы сравнения

Операторы сравнения — это ==, !=, <, <=, >, >=, <>.

В режиме lax операторы сравнения автоматически разворачивают операнды.

В следующей таблице приведены возможные варианты сравнения:

	null	скаляр	массив	объект
null	сравниваемые	сравниваемые	несравниваемые	несравниваемые
скаляр	сравниваемые	сравниваться могут строка со строкой, число с числом, логическое значение с логическим значением	несравниваемые	несравниваемые
массив	несравниваемые	несравниваемые	несравниваемые	несравниваемые
объект	несравниваемые	несравниваемые	несравниваемые	несравниваемые

Сравнение объектов с чем-либо, даже с самими собой, не поддерживается.

Сравнение выполняется с использованием семантики SQL с дополнительным правилом: SQL/JSON null равен SQL/JSON null, не больше и не меньше чего-либо.

Операнды могут быть последовательностями. В таком случае формируется перекрестное вычисление. Каждый элемент в одной последовательности сравнивается с каждым элементом в другой последовательности. Результат unknown, если какая-либо пара элементов несопоставима. Результат true, если любая пара элементов сравнивается и результат сравнения удовлетворяет условию оператора. Во всех остальных случаях результат false. В режиме lax обработчик пути досрочно прекращает вычисление, если обнаруживает ошибочный или успешный результат. В режиме strict обработчик пути должен проверять все сравнения, и если какое-либо из этих сравнений несопоставимо, то результатом будет unknown.

Предикат like_regex

Предикат сравнивает выражение символьного типа с регулярным выражением.

Синтаксис предиката like_regex:

<предикат like_regex> ::= <предикат> like_regex "<регулярное выражение>"
                     [ FLAGS "<флаг>[ <флаг>...]" ]

<флаг> ::=  i | m | s | u | t

Для регулярных выражений используется синтаксис google re2.

Флаги можно указывать как в верхнем, так и в нижнем регистре. Описание флагов:

i - чувствительность к регистру (по умолчание не чувствительны);
m - многострочный режим: \^ и $ соответствуют началу и концу строки (по умолчанию выключено);
s - символ точки (.) соответствует \ n (по умолчанию выключено);
u - меняет местами значения x* и x*?, x+ и x+? и т.д. (по умолчанию выключено);
t - убрать пробелы справа.

Например:

SELECT JSON_EXISTS('{"name": "Isaac Asimov"}', '$ ? (@.name like_regex "Asimov")')
FROM RDB$DATABASE;

==============================
<true>

Предикат starts with

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

Синтаксис предиката start with:

<предикат starts with> ::= <текстовое значение> starts with <искомая строка>

<текстовое значение> ::= <переменная пути>
                       | <переменная passing>
                       | <скалярное значение>

<искомая строка> ::= <текстовое значение>

Например:

SELECT JSON_EXISTS('{"name": "Isaac Asimov"}', '$ ? (@.name starts with "Isa")')
FROM RDB$DATABASE;

==============================
<true>

Предикат exists

Предикат exists проверяет, содержит ли результат выражения пути хотя бы один элемент.

Синтаксис предиката exists:

<предикат exists> ::= exists (<выражение пути>)

Если результат выражения пути — ошибка, то предикат exists вернёт Unknown. Если результат выражения пути — пустая последовательность, то предикат exists вернёт False. В любом другом случае результатом будет True.

Например:

SELECT JSON_QUERY('{"data": [1, 2, 3]}', '$ ? (exists (@.data))') FROM RDB$DATABASE;

==================================
{"data":[1,2,3]}

Предикат is unknown

Предикат is unknown ппроверяет, является ли результат выражение типом Unknown.

Синтаксис предиката is unknown:

<предикат is unknown> ::= (<выражение пути>) is unknown

Например:

SELECT JSON_EXISTS('{"digits": [1, 2, 3, 4, 5]}', '$.digits ? ((@ < 2) is unknown)')
FROM RDB$DATABASE;

==================================================
<false>

SELECT JSON_EXISTS('{"digits": [1, 2, 3, 4, 5]}', '$.digits ?(("hi">42) is unknown)')
FROM RDB$DATABASE;

JSON_EXISTS
===========
<true>

5.7. Функции SQL/JSON

Функции SQL/JSON разделены на три группы: функции генерации данных (JSON_OBJECT, JSON_OBJECTTAG, JSON_ARRAY и JSON_ARRAYAGG), функции работы с данными (JSON_VALUE, JSON_TABLE, JSON_MODIFY и JSON_QUERY) и функции валидации данных JSON_EXISTS и IS JSON. Функции генерации данных используют значения SQL и создают объекты JSON или массивы JSON, представленные в символьных типах SQL. Функции работы с данными используются для выборки значений JSON по выражению пути. Из результата убираются пробелы, переносы на новую строку и символы табуляции. Функции валидации проверяют существующий JSON на соответствие указанным критериям.

Общий синтаксис функций работы с данными

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

Функции работы с данными используют общий синтаксис:

<общий синтаксис> ::= <значение JSON>,
                      <выражение пути>
                     [<оператор передачи контекстных переменных>]
                     [<выходное значение>]

<значение JSON> ::= <значение> [FORMAT <формат>]

<формат> ::= AUTO
           | SQL
           | JSON

<выражение пути> ::= '<режим> <путь>'

<режим> ::= strict | lax

<путь> ::=   $[<cпособ доступа>...] [<метод элемента>] [<выражение фильтра>]

<оператор передачи контекстных переменных> ::=
         PASSING <параметр JSON> [ {, <параметр JSON> } ]

<параметр JSON> ::= <значение и формат> AS <идентификатор>

<выходное значение> ::= RETURNING <тип данных> [ FORMAT <JSON представление> ]

<JSON представление> :=
  SQL
| JSON

Предупреждение

Выходное значение нельзя указывать для JSON_VALUE и функций валидации (JSON_EXISTS, IS JSON).

Значение JSON

Значение JSON может быть любым типом данных.

<значение JSON> ::= <значение> [FORMAT <формат>]

<формат> ::= AUTO
           | SQL
           | JSON

Формат может принимать три значения: AUTO, SQL и JSON. По умолчанию используется FORMAT AUTO.

FORMAT AUTO — это формат, при котором все значения, созданные функциями генерации данных (JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, JSON_ARRAYAGG) или являющиеся результатом работы функции JSON_QUERY, будут обрабатываться как JSON. Все остальные значения будут преобразованы в JSON скаляр. Двойные кавычки и слеши будут экранированы.
FORMAT SQL — формат, при котором указанное значение будет преобразовано в JSON скаляр, даже если фактически значение является JSON.
FORMAT JSON — формат, при котором указанное значение будет обрабатываться как JSON, даже если фактически значение является строкой.

Выражение пути

Выражение пути определяет, какие значения из исходных данных нужно обработать.

<выражение пути> ::= <режим> <путь>

<путь> ::=  $[<cпособ доступа>...] [<метод элемента>] [<выражение фильтра>]

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

SELECT JSON_QUERY(
   '[{"value":4},{"value":6},{"value":42}]','lax $.value ? (@>4)' WITH ARRAY WRAPPER)
FROM RDB$DATABASE;

========================================
[6,42]

Оператор передачи контекстных переменных

Оператор PASSING используется для передачи параметров в выражение пути. Синтаксически оператор PASSING представляет собой список значений с псевдонимом для каждого:

<оператор передачи контекстных переменных> ::=
         PASSING <параметр JSON> [ , <параметр JSON> ... ]

<параметр JSON> ::= <значение> [FORMAT <формат>] AS <идентификатор>

<формат> ::= AUTO | SQL | JSON

<Идентификатор> является именем переменной, с помощью которого на значение можно ссылаться в выражении пути.

SELECT JSON_QUERY(
   '[{"value":4},{"value":6},{"value":42}]', 'lax $.value ? (@>$TR)' PASSING 5 AS TR
   RETURNING VARCHAR(100) WITH ARRAY WRAPPER) FROM RDB$DATABASE;

========================================
[6,42]

В примере число 5 передаётся в качестве параметра с помощью переменной TR.

Выходное значение

Предупреждение

Выходное значение нельзя указывать для JSON_VALUE и функций валидации (JSON_EXISTS, IS JSON).

Синтаксис предназначен для указания типа данных JSON-текста, возвращающегося JSON функцией:

<выходное значение> ::= RETURNING <тип данных> [ FORMAT <JSON представление> ]

<JSON представление> :=
  SQL
| JSON

Если возвращаемый тип данных не задан, то по умолчанию используется тип входного значения:

если входное значение типа BLOB, то выходное значение будет BLOB TEXT в кодировке UTF8;
если входное значение текстового типа, то выходным значением будет VARCHAR размером длина входной строки + 2 в кодировке UTF8.
если входное текстовое значение больше максимального размера VARCAHR, то для выходного значения будет использован тип BLOB SUB_TYPE TEXT в кодировке UTF8.
если входной значение не является текстовым типом, то выходным значением будет VARCAHR достаточного размера. Если не получится вычислить достаточный размер строки, то будет использован тип BLOB SUB_TYPE TEXT в кодировке UTF8.

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

SELECT JSON_QUERY('[]', '$' RETURNING VARCHAR(30) FORMAT JSON) IS JSON FORMAT
FROM RDB$DATABASE;

========
<true>

5.8. Функции работы с данными

Функции работы с данными:

JSON_VALUE — извлекает скалярное значение;
JSON_QUERY — извлекает объекты JSON и массивы JSON;
JSON_TABLE — извлекает реляционные данные из данных JSON;
JSON_MODIFY — изменяет существующие значения или добавляет новые.

JSON_VALUE

JSON_VALUE — это функция для извлечения скалярного значения SQL JSON.

Синтаксис оператора:

JSON_VALUE ( <значение JSON>,
             <выражение пути>
             [<оператор передачи контекстных переменных>]
             [<выходное значение>]
             [<поведение при пустом значении> ON EMPTY ]
             [<поведение при ошибке> ON ERROR ]
           )

<поведение при пустом значении> ::= ERROR
                                  | NULL
                                  | DEFAULT <пользовательское значение>

<поведение при ошибке> ::= ERROR
                         | NULL
                         | DEFAULT <пользовательское значение>

Функция JSON_VALUE может извлекать только одно значение из JSON. Если есть разворачивание и в массиве один элемент, то функция вернёт его без ошибки:

SELECT JSON_VALUE('{"numbers": [555.25]}', '$.numbers.abs()') FROM RDB$DATABASE;

========================================
555.25

Подробнее авторазворачивание описано в пункте .

<Поведение при пустом значении> определяет поведение, если результат выражения пути пустой:

NULL ON EMPTY означает, что JSON_VALUE вернёт значение null;
ERROR ON EMPTY означает, что будет показана ошибка;
DEFAULT <пользовательское значение> ON EMPTY означает, что JSON_VALUE вернёт указанное значение.

Для ON EMPTY и ON ERROR по умолчанию используется NULL.

JSON_QUERY

JSON_QUERY — это функция для извлечения массива JSON или объекта JSON.

Синтаксис JSON_QUERY:

JSON_QUERY ( <значение JSON>,
             <выражение пути>
             [<оператор передачи контекстных переменных>]
             [<выходное значение>]
             [<разворачивание> WRAPPER ]
             [<отображение кавычек> QUOTES [ ON SCALAR STRING ] ]
             [<поведение при пустом значении> ON EMPTY ]
             [<поведение при ошибке> ON ERROR ]
           )

<разворачивание> ::= WITHOUT [ ARRAY ]
                   | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ]

<отображение кавычек> ::= KEEP | OMIT

<поведение при пустом значении> ::= ERROR
                                  | NULL
                                  | EMPTY ARRAY
                                  | EMPTY OBJECT

<поведение при ошибке> ::= ERROR
                         | NULL
                         | EMPTY ARRAY
                         | EMPTY OBJECT

Предложения ON EMPTY и ON ERROR те же, что в JSON_VALUE. Опция DEFAULT отсутствует, но можно указать EMPTY ARRAY или EMPTY OBJECT в качестве результата.

Поведение при пустом значении по умолчанию установлено NULL ON EMPTY. Поведение при ошибке по умолчанию NULL ON ERROR. Для разворачивания значением по умолчанию является WITHOUT ARRAY. Если входное значение является значением null, то результатом JSON_QUERY будет null.

При использовании WITH с опцией UNCONDITIONAL результат всегда будет заключаться в квадратные скобки. При использовании WITH с опцией CONDITIONAL результат будет заключаться в квадратные скобки, только если возвращаемое функцией значение не является массивом. Для WITH значением по умолчанию является UNCONDITIONAL.

Если в запросе содержится выражение фильтра (знак вопроса) или числовые методы элементов (double(), ceiling(), floor(), abs()), то массив автоматически разворачивается. С методами size() и type() массив автоматически разворачиваться не будет.

Если на выходе у JSON_QUERY получается последовательность элментов (например, после разврачивания массива), то необходимо добавить предикат WITH ARRAY WRAPPER, так как элементы уже являются отдельными значениями. Также WRAPPER требуется в любом запросе с квадратными скобками, иначе будет ошибка.

SELECT JSON_QUERY(
'{"numbers":["555","345.567","0.12355"]}','$.numbers[*].double()' WITH ARRAY WRAPPER)
FROM RDB$DATABASE;

==========================
[555,345.567,0.12355]

JSON_MODIFY

JSON_MODIFY — функция позволяет изменить существующий JSON: вставить новые поля в объект, добавить элементы в массив, изменить поля/элементы массива или удалить их.

JSON_MODIFY ( <значение JSON>,
              <выражение пути>,
               {   DELETE
                 | UPDATE <новое значение>
                 | APPEND <новое значение>
                 | INSERT <новое значение>
               }
               [<выходное значение>]
               [<поведение при пустом значении> ON EMPTY]
               [<поведение при ошибке> ON ERROR ]
            )

<новое значение> ::= <значение> [FORMAT <формат>]

<формат> ::= AUTO | SQL | JSON

<поведение при ошибке> = NULL
                       | ERROR
                       | EMPTY ARRAY
                       | EMPTY OBJECT
                       | DEFAULT <пользовательское значение>

<поведение при пустом значении> = WORK
                                | ERROR
                                | NULL
                                | EMPTY ARRAY
                                | EMPTY OBJECT
                                | DEFAULT <пользовательское значение>

Есть несколько режимов работы функции:

UPDATE - используется для замены значения;
APPEND - используется для добавления нового элемента в конец массива;
DELETE - используется для удаления элемента;
INSERT - для вставки элемента на определённую позицию в массиве.

Режимом работы по умолчанию является UPDATE. При использовании его в режиме lax и указанном WORK ON EMPTY в случае, если по указанному пути значение не найдено, то оно будет добавлено.

SELECT JSON_MODIFY('{"data": "test"}', '$.id', UPDATE 5 RETURNING VARCHAR(30))
FROM RDB$DATABASE;

JSON_MODIFY
=======================
{"data":"test","id":5}

При использовании режима DELETE нужно опустить MODIFY_VALUE и запятую, то есть передавать только два аргумента.

При использовании INSERT значение будет вставлено именно на указанный индекс, а остальные значения сдвинутся. Например:

SELECT JSON_MODIFY('[1,2,3,4]', '$[0]', INSERT 'HI' returning varchar(30))
FROM RDB$DATABASE;

=======================
["HI",1,2,3,4]

При использовании DELETE значение будет удалено:

SELECT JSON_MODIFY('{"data": "test", "id":14}', '$.id', delete)
FROM RDB$DATABASE;

JSON_MODIFY
===========================
{"data":"test"}

<Поведение при ошибке> определяет поведение, если при обработке выражения пути возникла ошибка:

NULL ON ERROR означает, что при возникновении ошибки JSON_MODIFY вернёт значение null;
ERROR ON ERROR означает, что при возникновении ошибки будет показана ошибка;
EMPTY ARRAY ON ERROR означает, что при возникновении ошибки JSON_MODIFY вернёт пустой массив;
EMPTY OBJECT ON ERROR означает, что при возникновении ошибки JSON_MODIFY вернёт пустой объект;
DEFAULT <пользовательское значение> ON ERROR означает, что епри возникновении ошибки JSON_MODIFY вернёт указанное значение.

По умолчанию используется NULL ON ERROR.

<Поведение при пустом значении> определяет поведение, если результат выражения пути пустой:

WORK ON EMPTY означает, что если запрос возвращает пустое значение, то JSON_MODIFY вернёт отформатированный JSON;
ERROR ON EMPTY означает, что если запрос возвращает пустое значение, то будет показана ошибка;
NULL ON EMPTY означает, что если запрос возвращает пустое значение, то JSON_MODIFY вернёт значение null;
ERROR ON EMPTY означает, что при возникновении ошибки будет показана ошибка;
EMPTY ARRAY ON EMPTY означает, что JSON_MODIFY вернёт пустой массив;
DEFAULT <пользовательское значение> ON EMPTY означает, что если запрос возвращает пустое значение, то JSON_MODIFY вернёт указанное значение.

По умолчанию используется WORK ON EMPTY.

JSON_TABLE

JSON_TABLE - это функция, которая принимает JSON в качестве входных данных и извлекает из них реляционные данные. Функция имеет три параметра:

Значение JSON;
Выражение пути SQL/JSON для указания нуля или более строк;
Предложение COLUMNS для определения формы выходной таблицы.

Синтаксис предложения JSON_TABLE:

JSON_TABLE (   <значение JSON>,
               <выражение пути>
              [<оператор передачи контекстных переменных>]
               <предложение COLUMNS>
              [<предложение PLAN>]
              [<поведение при ошибке JSON_TABLE> ON ERROR ]
           )

<предложение COLUMNS> ::= COLUMNS (<определение столбца> [, <определение столбца> ... ])

<определение столбца> ::= <порядковый номер строки>
                        | <определение стандартного столбца>
                        | <определение форматированного столбца>
                        | <вложенные столбцы>

<порядковый номер строки> ::= <название столбца> FOR ORDINALITY

<определение стандартного столбца> ::=
                        <название столбца> <тип данных>
                      [ PATH <путь к столбцу> ]
                      [ <поведение при пустом значении столбца> ON EMPTY ]
                      [ <поведение при ошибке> ON ERROR ]

<поведение при пустом значении столбца> ::= ERROR
                                          | NULL
                                          | DEFAULT <пользовательское значение>

<поведение при ошибке> ::= ERROR
                         | NULL
                         | DEFAULT <пользовательское значение>

<определение форматированного столбца> ::=
        <название столбца> <тип данных>
        FORMAT <представление JSON>
      [ PATH <путь к столбцу> ]
      [ <разворачивание> WRAPPER ]
      [ <отображение кавычек> QUOTES [ ON SCALAR STRING ] ]
      [ <поведение при пустом значении форматированного столбца> ON EMPTY ]
      [ <поведение при ошибке форматированного столбца> ON ERROR ]

<разворачивание> ::= WITHOUT [ ARRAY ]
                   | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ]

<отображение кавычек> ::=  KEEP | OMIT

<поведение при пустом значении форматированного столбца> ::= ERROR
                                                           | NULL
                                                           | EMPTY ARRAY
                                                           | EMPTY OBJECT

<поведение при ошибке форматированного столбца> ::= ERROR
                                                  | NULL
                                                  | EMPTY ARRAY
                                                  | EMPTY OBJECT

<поведение при ошибке JSON_TABLE> ::= ERROR
                                    | EMPTY

Таблица для примера:

Таблица 5.3 Таблица `BOOKCLUB`
ID	JCOL
111	{ "Name": "John Smith", "address": { "streetAddress": "21 2nd Street", "city": "New York", "state": "NY", "postalCode": 10021 }, "phoneNumber": [ { "type": "home", "number": "212 555-1234"}, { "type": "fax", "number": "646 555-4567"} ] "books": [ { "title": "The Talisman", "authorList": [ "Stephen King", "Peter Straub"], "category": [ "SciFi", "Novel"] }, { "title": "Far from the Madding Crowd", "authorList": [ "Thomas Hardy"], "category": [ "Novel"] } ] }
222	{ "Name": "Peter Walker", "address": { "streetAddress": "111 Main Street", "city": "San Jose", "state": "CA", "postalCode": 95111 }, "phoneNumber": [ { "type": "home", "number": "408 555-9876"}, { "type": "office", "number": "650 555-2468"} ] "books": [ { "title":"Good Omens", "authorList": [ "Neil Gaiman", "Terry Pratchett"], "category": [ "Fantasy", "Novel"] }, { "title": "Smoke and Mirrors", "authorList": [ "Neil Gaiman"], "category": ["Novel"] } ] }
333	{ "Name" : "James Lee" }

С помощью предложения COLUMNS можно устанавливать четыре типа столбцов: столбцы с порядковым номером, стандартные, форматированные и вложенные.

Предложение FOR ORDINALITY добавляет столбец с порядковым номером. Нумерация начинается с 1. Номер автоматически увеличивается для каждого нового элемента массива в пределах одного родителя. Для дочерних строк, сформированных предложениями NESTED PATH, повторяется номер родительской строки. Если родительский элемент меняется, последовательность сбрасывается.

Пример добавления столбца с порядковым номером:

SELECT jt.rowseq, jt.name, jt.book_id, jt.book FROM bookclub,
 JSON_TABLE ( bookclub.jcol, 'lax $'
     COLUMNS ( rowSeq FOR ORDINALITY, name VARCHAR(30) PATH 'lax $.Name',
               NESTED PATH '$.books[*]'
                     COLUMNS (book_id FOR ORDINALITY, book CHAR(30) PATH '$.title'))
 ) AS jt;

     ROWSEQ NAME     BOOK_ID  BOOK
==== =============== ======== ==============================
   1 John Smith      1        The Talisman
   1 John Smith      2        Far from the Madding Crowd
   2 Peter Walker    1        Good Omens
   2 Peter Walker    2        Smoke and Mirrors
   3 James Lee       1        <null>

Стандартный столбец создаёт столбцы скалярного типа. Столбец создается с использованием семантики JSON_VALUE и использует выражение JSON пути. Столбец также содержит необязательные предложения ON EMPTY и ON ERROR с теми же вариантами выбора и семантикой, что и JSON_VALUE.

Форматированный столбец создаёт столбцы скалярного типа. Столбец создается с использованием семантики JSON_QUERY. Столбец также содержит необязательные предложения WRAPPER, QUOTES, ON EMPTY и ON ERROR с теми же вариантами выбора и семантикой, что и JSON_QUERY.

Пример работы JSON_TABLE (таблица BOOKCLUB приведена в таблице 5.3)

SELECT jt.postal
FROM bookclub, JSON_TABLE (bookclub.jcol, '$'
                           COLUMNS (postal INT PATH '$.address.postalCode'
                                    DEFAULT '0' ON EMPTY DEFAULT '1' ON ERROR)
                           ) AS jt;

POSTAL
============
10021
95111
0

Вложенные столбцы

Последний вариант определения столбца - это вложенные столбцы. Синтаксис представлен ниже:

<вложенные столбцы> ::= NESTED [ PATH ] <путь к вложенной таблице>
                        [ AS <имя вложенного пути> ]
                        <предложение COLUMNS>

Предложение NESTED позволяет объединить вложение объектов или массивов JSON в одном запросе, а не связывать несколько выражений JSON_TABLE с помощью SQL. За ключевым словом NESTED следует путь, для которого опционально можно указать псевдоним. Путь обеспечивает уточненный контекст для вложенных столбцов. Имя пути в основном используется, чтобы создать явный план.

Пример работы с вложенными столбцами (таблица BOOKCLUB приведена в таблице 5.3)

SELECT jt.Name, jt.phone
FROM bookclub, JSON_TABLE ( bookclub.jcol, 'lax $'
                            COLUMNS (Name varchar(50) path '$.Name',
                                     NESTED PATH '$.phoneNumber[*].number'
                                     COLUMNS (phone CHAR(30) PATH '$' NULL ON EMPTY))
                           ) AS jt;

NAME            PHONE
==============  ===============
John Smith      212 555-1234
John Smith      646 555-4567
Peter Walker    408 555-9876
Peter Walker    650 555-2468
James Lee       <null>

Предложение PLAN

Для каждого JSON-пути можно указать его псевдоним с попомщью предложения AS. Имена путей являются идентификаторами и должны быть уникальными. Они используются в предложении PLAN для определения плана вывода.

Синтаксис предложения PLAN:

<предложение PLAN> ::= PLAN (<план>)
                     | PLAN DEFAULT ( <определение плана по умолчанию> )

<план> ::= <имя пути>
         | <родительский/дочерний>
         | <пара>

<имя пути> ::= <идентификатор>

<родительский/дочерний> ::= <внешний план>
                          | <внутренний план>

<внешний план> ::= <путь> OUTER <первичный план>

<внутренний план> ::= <путь> INNER <первичный план>

<пара> ::=  <план объединения>
          | <перекрестный план>

<план объединения> ::= <первичный план> UNION <первичный план>
                       [ { UNION <первичный план> }... ]

<перекрестный план> ::= <первичный план> CROSS <первичный план>
                        [ { CROSS <первичный план> }... ]

<первичный план> ::=  <имя пути>
                    | ( <план> )

<определение плана по умолчанию> ::=
                  <внутренний/внешний план> [ <план объединения/перекрестный> ]
                | <план объединения/перекрестный> [ , <внутренний/внешний план> ]

<внутренний/внешний план> ::= INNER | OUTER

<план объединения/перекрестный> ::= UNION | CROSS

Ключевые слова INNER, OUTER, UNION и CROSS в контексте предложения PLAN имеют следующие характеристики:

INNER соответствует семантике INNER JOIN.
OUTER соответствует семантике LEFT OUTER JOIN и используется по умолчанию для отношений родительский/дочерний.
Первым операндом INNER и OUTER является путь, который должен быть предком всех имен путей во втором операнде.
Если план явный, то все пути должны быть явными и встречаться в предложении PLAN ровно один раз.
CROSS соответствует семантике CROSS JOIN.
UNION соответствует семантике FULL OUTER JOIN неудовлетворительным предикатом, таким как 1=0, и по умолчанию используется для отношений между сиблингами.
UNION является ассоциативным.
CROSS является ассоциативным.
Круглые скобки необходимы для разграничения сложных выражений.
Не существует приоритета между UNION и CROSS.

Пример работы плана DEFAULT (таблица BOOKCLUB приведена в таблице 5.3)

SELECT bookclub.id, jt.name, jt.title, jt.author, jt.category
FROM bookclub,
  JSON_TABLE ( bookclub.jcol, 'lax $'
  COLUMNS (name VARCHAR(30) PATH 'lax $.Name',
           NESTED PATH 'lax $.books[*]'
           COLUMNS (title VARCHAR(60) PATH 'lax $.title',
                    NESTED PATH 'lax $.authorList[*]' AS ATH
                    COLUMNS (author VARCHAR(30) PATH 'lax $'),
                             NESTED PATH 'lax $.category[*]' AS CAT
                             COLUMNS ( category VARCHAR(30) PATH 'lax $')
                   )
          )
  PLAN DEFAULT(INNER,CROSS)
  ) AS jt;

ID  NAME         TITLE             AUTHOR          CATEGORY
=== ============ ================  =============== =========
111 John Smith   The Talisman      Stephen King    SciFi
111 John Smith   The Talisman      Stephen King    Novel
111 John Smith   The Talisman      Peter Straub    SciFi
111 John Smith   The Talisman      Peter Straub    Novel
111 John Smith   Far From the      Thomas Hardy    Novel
               Madding Crowd
222 Peter Walker Good Omens        Neil Gaiman     Fantasy
222 Peter Walker Good Omens        Neil Gaiman     Novel
222 Peter Walker Good Omens        Terry Pratchett Fantasy
222 Peter Walker Good Omens        Terry Pratchett Novel
222 Peter Walker Smoke and Mirrors Neil Gaiman     Novel

Пример работы внутреннего плана с объединением

SELECT  JT.t, JT.a
FROM JSON_TABLE( '[{"t":[1,2], "a":[10,20]}]', '$[*]' as PERSON
   COLUMNS ( NESTED PATH 'lax $.t[*]' as SUB1 columns(t VARCHAR(30) PATH 'lax $'),
               NESTED PATH 'lax $.a[*]' as SUB2 columns(a VARCHAR(30) PATH 'lax $') )
   PLAN (PERSON INNER (SUB1 UNION SUB2) )
) AS JT;

T         A
========  ==============================
1         <null>
2         <null>
<null>    10
<null>    20

Пример работы внутреннего перекрёстного плана

SELECT  JT.t, JT.a
FROM JSON_TABLE( '[{"t":[1,2], "a":[10,20]}]', '$[*]' as PERSON
   COLUMNS ( NESTED PATH 'lax $.t[*]' as SUB1 columns(t VARCHAR(30) PATH 'lax $'),
               NESTED PATH 'lax $.a[*]' as SUB2 columns(a VARCHAR(30) PATH 'lax $') )
   PLAN (PERSON INNER (SUB1 CROSS SUB2) )
) AS JT;

T    A
===  ====
1    10
1    20
2    10
2    20

Пример работы внешнего плана

SELECT  JT.t, JT.a
FROM JSON_TABLE( '{"t":null, "a":[10,20]}', '$' as PERSON
         COLUMNS ( T VARCHAR(30) PATH 'lax $.t',
                  NESTED PATH 'lax $.a[*]' as SUB2
                  COLUMNS ( a VARCHAR(30) PATH 'lax $') )
         PLAN (PERSON OUTER SUB2)
) AS JT;

T        A
=======  ====
<null>   10
<null>   20

5.9. Функции валидации

Функции валидации JSON:

JSON_EXISTS — определяет, существует ли значение по заданному пути;
IS JSON — определяет, является ли указанное строковое значение текстом JSON.

JSON_EXISTS

Функция JSON_EXISTS необходима для проверки существования какого-либо значения по заданному пути. Возвращает логический результат. True, если выражение пути находит один или несколько элементов SQL/JSON.

Синтаксис оператора:

JSON_EXISTS ( <значение JSON>,
              <выражение пути>
              [<оператор передачи контекстных переменных>]
              [<поведение при ошибке> ON ERROR]
            )

<поведение при ошибке> ::=   TRUE
                           | FALSE
                           | UNKNOWN
                           | ERROR

Пример работы функции:

SELECT JSON_EXISTS('{"tags":{"test":[1,2,3,4,5]}}', '$.tags.test[2]')
FROM RDB$DATABASE;

JSON_EXISTS
===========
<true>

Предложение ON ERROR по умолчанию равно FALSE. Если входное значение является значением null, то результатом JSON_EXISTS будет Unknown.

IS JSON

Предикат IS JSON проверяет значение на соответствие схеме JSON.

Синтаксис оператора следующий:

<предикат IS JSON> ::= <значение> [FORMAT <формат>] IS JSON
                       [ {<тип значения> |  [SYSTEM] FORMAT} ]
                       [ <ограничение уникальности ключа> ]

<тип значения> ::= VALUE
                 | ARRAY
                 | OBJECT
                 | SCALAR

<ограничение уникальности ключа> ::= WITH UNIQUE [ KEYS ]
                                   | WITHOUT UNIQUE [ KEYS ]

Например, следующий запрос проверяет, является ли указанное значение JSON-текстом:

SELECT '[{"value":5}', 10, true]' IS JSON FROM RDB$DATABASE;

========================================
<true>

Скалярные значения также являются JSON-текстом:

SELECT '"String scalar value"' IS JSON FROM RDB$DATABASE;

========================================
<true>

<Тип значения> позволят проверить, соответствует ли входная строка указанному типу:

VALUE — для проверки, является ли входная строка любым значением JSON, в том числе null:
```
SELECT 'null' IS JSON VALUE FROM RDB$DATABASE;

====================
<true>
```
При указании SQL значения null результатом будет null:
```
SELECT null IS JSON VALUE FROM RDB$DATABASE;

====================
<null>
```
ARRAY — для проверки, является ли входная строка массивом:
```
SELECT '[1,2,3]' IS JSON ARRAY FROM RDB$DATABASE;

====================
<true>
```
OBJECT — для проверки, является ли входная строка объектом:
```
SELECT '{"value":5}' IS JSON OBJECT FROM RDB$DATABASE;

====================
<true>
```
SCALAR — для проверки, является ли входная строка скаляром.
```
SELECT '1' IS JSON SCALAR FROM RDB$DATABASE;

====================
<true>
```

SYSTEM FORMAT проверяет, является ли входная строка результатом работы JSON-функции:

SELECT JSON_QUERY('[]', '$') IS JSON SYSTEM FORMAT FROM RDB$DATABASE;

====================
<true>

Типом входных данных по умолчанию является FORMAT JSON.

Ограничение уникальности ключа проверяет, должны ли ключи повторяться. Если есть, то при использовании WITHOUT UNIQUE KEYS функция IS JSON вернёт true, при использовании WITH UNIQUE KEYS — false. По умолчанию используется WITHOUT UNIQUE KEYS.

Работа функции IS JSON с WITHOUT UNIQUE KEYS:

SELECT '{"A":1, "B":2, "A":3}' IS JSON FROM RDB$DATABASE;

========================================
<true>

Работа функции IS JSON с WITH UNIQUE KEYS:

SELECT '{"A":1, "B":2, "A":3}' IS JSON WITH UNIQUE FROM RDB$DATABASE;

========================================
<false>

5.10. Функции генерации данных

Функции генерации данных:

JSON_OBJECT — создаёт объект JSON из явных пар ключ-значение;
JSON_OBJECTAGG — создаёт объект JSON путем агрегирования полей из таблицы или другого селективного источника;
JSON_ARRAY — создаёт массив JSON из явного списка данных или подзапроса;
JSON_ARRAYAGG — создаёт массив JSON путём путём агрегирования данных SQL.

Общий формат входных значений

Функции генерации данных используют общий формат для входных значений:

<значение и формат> ::= <значение> FORMAT <формат>

<Значение> может быть любым типом данных. Формат может принимать три значения: AUTO, SQL и JSON. По умолчанию используется FORMAT AUTO.

FORMAT AUTO — это формат, при котором все значения, созданные функциями генерации данных (JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, JSON_ARRAYAGG) или являющиеся результатом работы функции JSON_QUERY, будут обрабатываться как JSON. Все остальные значения будут преобразованы в JSON скаляр. Двойные кавычки и слеши будут экранированы. Например:
```
SELECT JSON_ARRAY('1', '2' FORMAT SQL, '3' FORMAT JSON) from RDB$DATABASE;

============
["1","2",3]
```
FORMAT SQL — формат, при котором указанное значение будет преобразовано в JSON скаляр, даже если фактически значение является JSON. Например:
```
SELECT JSON_ARRAY(JSON_QUERY('{}', '$'),
                  JSON_QUERY('{}', '$') FORMAT JSON,
                  JSON_QUERY('{}', '$') FORMAT SQL) from RDB$DATABASE;

============
[{},{},"{}"]
```
FORMAT JSON — формат, при котором указанное значение будет обрабатываться как JSON, даже если фактически значение является строкой. Например:
```
SELECT JSON_ARRAY(1, '2', '3' FORMAT JSON) from RDB$DATABASE;

============
[1,"2",3]
```

JSON_OBJECT

Функция JSON_OBJECT создаёт объекты JSON из явных пар ключ-значение.

Синтаксис функции:

JSON_OBJECT ( [<ключ-значение> [, <ключ-значение> ...]]
              [<поведение при значении null>  ON NULL]
              [<ограничение уникальности ключа>]
              [<выходное значение>]
            )

<ключ-значение> ::=  [ KEY ] <ключ JSON> VALUE <значение JSON>
                     | <ключ JSON> : <значение JSON>

<ключ JSON> ::= <строковое значение>

<значение JSON> ::= <значение> FORMAT <формат>

<поведение при значении null> ::= NULL | ABSENT

<ограничение уникальности ключа> ::=  WITH UNIQUE [ KEYS ]
                                    | WITHOUT UNIQUE [ KEYS ]

<выходное значение> ::= RETURNING <тип данных>

<Ключ JSON> может быть только строковым типом и не может быть равным NULL. Если <значение JSON> равно NULL, то поведение определяется предложением <поведение при значении null>. NULL ON NULL возврвщвет значение NULL, а ABSENT ON NULL опускает такую пару ключ-значение из результирующего объекта. По умолчанию используется NULL ON NULL.

Построение объекта JSON с NULL ON NULL:

SELECT JSON_OBJECT('size': 3, key 'name' value null, 'ref': false NULL ON NULL)
FROM RDB$DATABASE;

========================================
{"size":3,"name":null,"ref":false}

Построение объекта JSON с ABSENT ON NULL:

SELECT JSON_OBJECT('size': 3, key 'name' value null, 'ref': false ABSENT ON NULL)
FROM RDB$DATABASE;

========================================
{"size":3,"ref":false}

Ограничение уникальности ключа определяет, должны ли ключи в результате повторяться. При использовании WITHOUT UNIQUE KEYS ключи могут дублироваться. При использовании WITH UNIQUE KEYS ключи должны быть уникальными, иначе результатом работы функции будет ошибка. По умолчанию WITHOUT UNIQUE KEYS.

Работа функции JSON_OBJECT с WITHOUT UNIQUE KEYS:

SELECT JSON_OBJECT('A':1, 'B':2, 'A':3) FROM RDB$DATABASE;

========================================
{"A":1,"B":2,"A":3}

Работа функции JSON_OBJECT с WITH UNIQUE KEYS:

SELECT JSON_OBJECT('A':1, 'B':2, 'A':3 WITH UNIQUE) FROM RDB$DATABASE;

======================================
There is a non-unique key with name 'A' on rows 0 and 2

JSON_OBJECTAGG

Можно построить объект JSON путем объединения информации из таблицы SQL. Подразумевается, что таблица фактически содержит столбец с ключами JSON и другой столбец с соответствующими ключам значениями.

Синтаксис функции:

JSON_OBJECTAGG ( <ключ-значение>
                 [<поведение при значении null> ON NULL]
                 [<ограничение уникальности ключа> ]
                 [<выходное значение> ]
               )

<ключ-значение> ::= <имя столбца с ключами>: <имя столбца со значениями>
                    [FORMAT <формат>]

<поведение при значении null> ::= NULL | ABSENT

<ограничение уникальности ключа> ::= WITH UNIQUE [ KEYS ]
                                   | WITHOUT UNIQUE [ KEYS ]

<выходное значение> ::= RETURNING <тип данных>

Пример построения объекта:

SELECT JSON_OBJECTAGG(option : id) FROM OPTIONS;

======================================
{"size":1,"name":2,"value":3}

Таблица 5.4 Таблица `OPTIONS`
ID	OPTION
1	"SIZE"
2	"NAME"
3	"VALUE"

<Поведение при значении null> по умолчанию равно NULL ON NULL.

JSON_ARRAY

Функция JSON_ARRAY позволяет построить массив JSON из явного списка данных.

Синтаксис функции:

<конструктор массивов JSON> ::=   <создание массива JSON из списка значений>
                                | <создание массива JSON из результата запроса>

<создание массива JSON из списка значений> ::=
            JSON_ARRAY (
                [ <значение и формат> [, <значение и формат> ... ]
                [ <поведение при значении null>] ON NULL]
                [ <выходное значение> ]
            )

<создание массива JSON из результата запроса> ::=
            JSON_ARRAY (
                 <SQL-запрос SELECT>
               [ FORMAT <формат>]
               [ <поведение при значении null> ON NULL]
               [ <выходное значение> ]
            )

<значение и формат> ::= <значение> [FORMAT <формат>]

<поведение при значении null> ::= NULL | ABSENT

<выходное значение> ::= RETURNING <тип данных>

JSON_ARRAY предоставляет два варианта создания массива. Первый вариант создает результат из явного списка значений SQL:

SELECT JSON_ARRAY(1,2,3,4,5) FROM RDB$DATABASE;
======================================
[1,2,3,4,5]

Второй вариант создаёт массив из результата запроса SQL, вызванного внутри функции. Запрос должен возвращать ровно один столбец, а элементы массива будут сформированы из значений столбца, сгенерированного запросом (таблица OPTION представлена в таблице 5.4):

SELECT JSON_ARRAY(select option from options) FROM RDB$DATABASE;
======================================
["size","name","value"]

Поведение при значении null по умолчанию равно ABSENT ON NULL (что отличается от значения по умолчанию для JSON_OBJECT).

JSON_ARRAYAGG

Функция JSON_ARRAYAGG позволяет создать массив JSON путём агрегирования данных SQL.

Синтаксис функции:

JSON_ARRAYAGG (   <столбец таблицы>
                 [FORMAT <формат>]
                 [<поведение при значении null> ON NULL]
                 [<выходное значение>]
              )

<поведение при значении null> ::= NULL | ABSENT

<выходное значение> ::= RETURNING <тип данных>

Пример построения массива JSON (таблица OPTION представлена в таблице 5.4):

SELECT JSON_ARRAYAGG(option) FROM OPTIONS;

======================================
["size","name","value"]

Поведение при значении null по умолчанию равно ABSENT ON NULL.