Ndtyhij

I've noted that key-value values may be unquoted when numeric in TSQL JSON strings, but it seems the key component must always be quoted.

select 1, isjson(''), 'empty string' union

select 2, isjson('{}'), 'empty braces' union

select 3, isjson('{1:2}'), 'unquoted both, numerals both' union

select 4, isjson('{1:"2"}'), 'unquoted key, numerals both' union

select 5, isjson('{"1":2}'), 'unquoted value, numerals both' union

select 6, isjson('{"1":"2"}'), 'quoted both, numerals both' union

select 7, isjson('{a:b}'), 'unquoted both, alpha both' union

select 8, isjson('{a:"b"}'), 'unquoted key, alpha both' union

select 9, isjson('{"a":b}'), 'unquoted value, alpha both' union

select 10, isjson('{"a":"b"}'), 'quoted both, alpha both'

order by 1

;

Results:

1   0   empty string

2   1   empty braces

3   0   unquoted both, numerals both

4   0   unquoted key, numerals both

5   1   unquoted value, numerals both

6   1   quoted both, numerals both

7   0   unquoted both, alpha both

8   0   unquoted key, alpha both

9   0   unquoted value, alpha both

10  1   quoted both, alpha both

The above demonstrates this but my questions are:

1. Must this always be so? (Is there a configuration that can override this behaviour?)


2. Is this behaviour specified by JSON or SQL Server?


3. What is the rationale behind this design decision?


4. Would there be a performance benefit if SQL Server automatically cast unquoted numerals as integers?

The JSON notation definition follows the following schema:

The definition of the string is the following:

You can see that the quotes are mandatory both at the beginning and at the end.

The definition of the value is the following:

Note that here you can either supply a string or a number, the number being:

Conclusions:

• Keys must have quotes both at the beginning and at the end.


• Quotes can be avoided on the value end if you are supplying numbers.

I can't answer why JSON adopted this particular schema and an response here might be opinion based.

SQL Server will always get a performance increases when treating integers over string data types (like VARCHAR or NVARCHAR) since they are faster to operate with and compare, but make sure the data type is actually a numeric type and not a number stored as a string.