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

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