Microsoft
Microsoft
MicrosoftLenniLobel Great question. The ISJSON function without the new type_constraint parameter test documents that conform to the RFC 4627: The application/json Media Type for JavaScript Object Notation (JSON) (rfc-editor.org) specification. In this specification a JSON document is defined as a JSON object or array.
JSON-text = object / array
Using ISJSON with the VALUE constraint tests for RFC 8259: The JavaScript Object Notation (JSON) Data Interchange Format (rfc-editor.org), where a JSON document is defined as:
JSON-text = ws value ws
value = false / null / true / object / array / number / string
As for JSON scalar, it is either a number or string. Below is an example to illustrate the new SCALAR constraint:
SELECT T.doc, ISJSON(T.doc, SCALAR) AS is_json_scalar, ISJSON(T.doc, VALUE) AS is_json_value
FROM (VALUES('abc'), ('"test string"'), ('1.0'), ('1'), ('1E2'), ('false'), ('true'), ('null'), ('TRUE'), ('NULL'), ('"false"')) as T(doc);I am also pasting the table from my spec below:
|
Input value with JSON string |
ISJSON expression |
Result |
|
Test string |
ISJSON( input_value ) |
0 |
|
Test string |
ISJSON( input_value, VALUE ) |
0 |
|
Test string |
ISJSON( input_value, ARRAY ) |
0 |
|
Test string |
ISJSON( input_value, OBJECT ) |
0 |
|
Test string |
ISJSON( input_value, SCALAR ) |
0 |
|
"Test string" |
ISJSON( input_value ) |
0 |
|
"Test string" |
ISJSON( input_value, VALUE ) |
1 |
|
"Test string" |
ISJSON( input_value, ARRAY ) |
0 |
|
"Test string" |
ISJSON( input_value, OBJECT ) |
0 |
|
"Test string" |
ISJSON( input_value, SCALAR ) |
1 |
|
true |
ISJSON( input_value ) |
0 |
|
true |
ISJSON( input_value, VALUE ) |
1 |
|
true |
ISJSON( input_value, SCALAR ) |
0 |
|
TRUE |
ISJSON( input_value ) |
0 |
|
TRUE |
ISJSON( input_value, VALUE ) |
0 |
|
TRUE |
ISJSON( input_value, SCALAR ) |
0 |
|
null |
ISJSON( input_value ) |
0 |
|
null |
ISJSON( input_value, VALUE ) |
1 |
|
null |
ISJSON( input_value, SCALAR ) |
0 |
|
Null |
ISJSON( input_value ) |
0 |
|
Null |
ISJSON( input_value, VALUE ) |
0 |
|
Null |
ISJSON( input_value, SCALAR ) |
0 |
|
123 |
ISJSON( input_value ) |
0 |
|
123 |
ISJSON( input_value, VALUE ) |
1 |
|
123 |
ISJSON( input_value, SCALAR ) |
1 |
|
234.5 |
ISJSON( input_value ) |
0 |
|
234.5 |
ISJSON( input_value, VALUE ) |
1 |
|
234.5 |
ISJSON( input_value, SCALAR ) |
1 |
|
6E7 |
ISJSON( input_value ) |
0 |
|
6E7 |
ISJSON( input_value, VALUE ) |
1 |
|
6E7 |
ISJSON( input_value, SCALAR ) |
1 |
|
8E-2 |
ISJSON( input_value ) |
0 |
|
8E-2 |
ISJSON( input_value, VALUE ) |
1 |
|
8E-2 |
ISJSON( input_value, SCALAR ) |
1 |
|
{} |
ISJSON( input_value ) |
1 |
|
{} |
ISJSON( input_value, VALUE ) |
1 |
|
{} |
ISJSON( input_value, OBJECT ) |
1 |
|
{} |
ISJSON( input_value, SCALAR ) |
0 |
|
{"k":"v"} |
ISJSON( input_value ) |
1 |
|
{"k":"v"} |
ISJSON( input_value, VALUE ) |
1 |
|
{"k":"v"} |
ISJSON( input_value, OBJECT ) |
1 |
|
{"k":"v"} |
ISJSON( input_value, SCALAR ) |
0 |
|
[] |
ISJSON( input_value ) |
1 |
|
[] |
ISJSON( input_value, VALUE ) |
1 |
|
[] |
ISJSON( input_value, ARRAY ) |
1 |
|
[] |
ISJSON( input_value, OBJECT ) |
0 |
|
[] |
ISJSON( input_value, SCALAR ) |
0 |
|
[ 1, "v", {}, true, null ] |
ISJSON( input_value ) |
1 |
|
[ 1, "v", {}, true, null ] |
ISJSON( input_value, VALUE ) |
1 |
|
[ 1, "v", {}, true, null ] |
ISJSON( input_value, ARRAY ) |
1 |
|
[ 1, "v", {}, true, null ] |
ISJSON( input_value, OBJECT ) |
0 |
|
[ 1, "v", {}, true, null ] |
ISJSON( input_value, SCALAR ) |
0 |
To summarize, use ISJSON without type constraint to test documents for RFC 4627 and ISJSON with VALUE constraint for RFC 8259. Rest of the constraints are useful if you want to validate input before using JSON_OBJECT or JSON_ARRAY to construct new JSON documents or perform other conditional logic. Hope this helps clarify the behavior & syntax.
Another tip, if you use Visual Studio Code and use JSON as the file type it will validate the file based on RFC 8259.
