To validate JSON, you can use the ISJSON function. This is a scalar function and checks whether the input string is valid JSON data. The function has one input argument:
string: This is an expression of any string data type, excepttextandntext
The return type of the function is int, but only three values are possible:
1if the input string is JSON conforming0if the input string is not valid JSON dataNULLif the input expression is NULL
The following statement checks whether the input variable is JSON valid:
SELECT
ISJSON ('test'),
ISJSON (''),
ISJSON ('{}'),
ISJSON ('{"a"}'),
ISJSON ('{"a":1}'),
ISJSON ('{"a":1"}');Here is the output:
------ ------ ------ ------ ------ ------ 0 0 1 0 1 0
ISJSON does not check the uniqueness of keys at the same level. Therefore, this JSON data is valid:
SELECT ISJSON ('{"id":1, "id":"a"}') AS is_json;
It returns:
is_json ----------- 1
Since there is...