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

Автор оригинала: Daniel Calbimonte

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