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:
1, if the input string is JSON conforming0, if the input string is not valid JSON dataNULL, if 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 no JSON data type and data must be stored...