Trjtdtk

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.