Must Numeric JSON Keys Be Quoted in SQL Server?

jsonsql servert-sql

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?

Best Answer

The JSON notation definition follows the following schema:

![enter image description here

The definition of the string is the following:

enter image description here

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

The definition of the value is the following:

enter image description here

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

enter image description here

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.

Related Question