Новое в SQL Server 2022: изменения в функции ISJSON

Kate · 16 Дек 2023

SQL Server поддерживает работу с данными типа JSON, и имеет для этого необходимый функционал, в который входит функция ISJSON, для проверки, соответствует ли значение типу JSON. Она вернет 0, если это не правильный JSON, и 1, если JSON правильный. Если JSON содержит недопустимые данные, функция помогает это обнаружить.

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

ISJSON (значение или выражение, тип)
Значение или выражение — это значение или выражение T-SQL, которое будет оцениваться. Тип — это новый аргумент, появившийся в SQL Server 2022.

Тип JSON может быть:

value
array
object
scalar

Эта функция существует с SQL Server 2016, однако раньше она имела только один аргумент. Следующий пример иллюстрирует что будет, если её вызвать с двумя аргументами на SQL Server 2019 или более ранних версиях.

SELECT ISJSON ('true', scalar) as isvalid
Это вернуло следующее:

Msg 174, Level 15, State 1, Line 1
The isjson function requires 1 argument(s).

Пример использования функции ISJSON для значений

В следующем примере показана типичная ошибка:

SELECT ISJSON (33, value) as isvalid
Тип данных аргумента int в качестве значения недопустим. Эта ошибка возникает, поскольку функция ISJSON не считает правильными числовые значения.

В следующем примере показано, как устранить проблему.

SELECT ISJSON ('33', value) as isvalid
Этот запрос вернет 1, что означает, что это допустимое значение.

В следующем примере показаны недопустимые значения:

SELECT ISJSON ('33,33', value) as isvalid
isvalid
---------
0
Следующий пример показывает, что происходит при проверке массивов:

SELECT ISJSON ('[23,34]', value) as isvalid
isvalid
---------
1
В этом случае возвращаемое значение соответствует успешной проверке.

Примеры ISJSON с массивами

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

SELECT ISJSON ('23', array) as isvalid
Функция вернет 0, что означает, что значение для JSON недопустимо.

isvalid
---------
0
С другой стороны, следующий пример вернет 1.

SELECT ISJSON ('[23,34]', array) as isvalid
isvalid
---------
1
Вы также можете использовать переменные, сохраняя JSON в них и подставляя переменные напрямую в функцию. В следующем примере показан список имен лучших игроков НБА всех времен.

DECLARE @json NVARCHAR(MAX) = N'["Michael", "Kobe", "LeBron", "Magic", "Larry", "Kareem", "Wilt", "Bill", "Shaquille", "Tim"]';

SELECT ISJSON (@json, array) as isvalid;
isvalid
---------
1
Примечание. Если вы будете использовать в качестве аргумента массив значений в двойных кавычках, он тоже будет действителен:

DECLARE @json NVARCHAR(MAX) = N'["Michael", "Kobe"]';

SELECT ISJSON (@json, "array") as isvalid;
isvalid
---------
1
Но если вы поставите одинарные кавычки для значений массива, будет возвращена ошибка:

DECLARE @json NVARCHAR(MAX) = N'[‘Michael’, ‘Kobe’]';

SELECT ISJSON (@json, "array") as isvalid;
Сообщение об ошибке следующее:

Msg 1023, Level 15, State 1, Line 2
Invalid parameter 2 specified for isjson.
Вот, что произойдет, если мы имеем дело с объектами, и один из объектов содержит массив атрибутов:

DECLARE @json NVARCHAR(MAX) = N'{
"name": "John",
"age": 30,
"city": "New York",
"pets": [
{
"type": "dog",
"name": "Buddy"
},
{
"type": "cat",
"name": "Lucy"
}
],
"family": {
"father": {
"name": "Peter",
"age": 60
},
"mother": {
"name": "Mary",
"age": 55
}
}
}';

SELECT ISJSON (@json, array) as isvalid;
В этом примере объектом является John. Тут указан его возраст, город, домашние животные и семья. Pet — это массив с двумя домашними животными, кошкой и собакой. Однако основным типом является объект, содержащий массивы. Вот почему функция сигнализирует, что значение недопустимо:

isvalid
---------
0
С другой стороны, если мы проверим, что JSON является допустимым объектом, он вернет значение, равнозначное true (1).

DECLARE @json NVARCHAR(MAX) = N'{
"name": "John",
"age": 30,
"city": "New York",
"pets": [
{
"type": "dog",
"name": "Buddy"
},
{
"type": "cat",
"name": "Lucy"
}
],
"family": {
"father": {
"name": "Peter",
"age": 60
},
"mother": {
"name": "Mary",
"age": 55
}
}
}';

SELECT ISJSON(@json, object) as isvalid;
isvalid
---------
1
В предыдущем примере мы проверяли, что строка JSON является допустимым массивом, и результат был отрицательным. В этом примере мы проверили, что строка JSON является допустимым объектом и проверка прошла успешно.

Примеры ISJSON со скалярными значениями

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

SELECT ISJSON ('[23,34]', scalar) as isvalid
isvalid
---------
0
В следующем примере показано допустимое скалярное значение.

SELECT ISJSON ('34', scalar) as isvalid
isvalid
---------
1
Если же мы предоставим числа:

SELECT ISJSON (34, scalar) as isvalid
Функция не вернёт ошибку:

Msg 8116, Level 16, State 1, Line 18
Argument data type int is invalid for argument 1 of isjson function.

Примечание: Значения True и False не являются допустимыми скалярными значениями.

SELECT ISJSON ('true', scalar) as isvalid
isvalid
---------
0
Строковые значения также являются недопустимыми в качестве скалярных значений.

SELECT ISJSON ('myvalue', scalar) as isvalid
isvalid
---------
0

Пример использования функции ISJSON с IF

В следующем примере показано, как скрестить IF и ISJSON. В результате должен вернуться текст «The value is valid», если ISJSON вернёт 1, или другой текст, если значение будет равно 0.

IF ISJSON ('myvalue', scalar) = 1
SELECT 'The value is valid' as result
ELSE
SELECT 'The value is invalid' as result
result
---------
The value is valid

Примеры функции ISJSON с объектами

В следующем примере показано допустимое значение для объекта.

SELECT ISJSON ('{"name":"daniel"}', object) as isvalid
isvalid
---------
1
В следующем примере показано, как работать с неопределёнными значениями.

SELECT ISJSON ('{"name":null}', object) as isvalid
isvalid
---------
1
Вот пример, показывающий допустимый объект с числом.

SELECT ISJSON ('{"name":1}', object) as isvalid
И наконец, в этом примере показан недопустимый объект.

SELECT ISJSON ('{"value"}', object) as isvalid
isvalid
---------
1