patternsqlModerate
Must numeric JSON keys be quoted?
Viewed 0 times
mustnumerickeysquotedjson
Problem
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.
Results:
The above demonstrates this but my questions are:
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 bothThe above demonstrates this but my questions are:
- Must this always be so? (Is there a configuration that can override this behaviour?)
- Is this behaviour specified by JSON or SQL Server?
- What is the rationale behind this design decision?
- Would there be a performance benefit if SQL Server automatically cast unquoted numerals as integers?
Solution
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:
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
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.Context
StackExchange Database Administrators Q#234394, answer score: 10
Revisions (0)
No revisions yet.