5

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?
5

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.

  • Excellent. Thank you. Classic that you've essentially pointed me to the JSON homepage :D but you did answer everything right here, and it seems a few people value this question/answer already. Cheers. – youcantryreachingme Apr 10 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.