By youcantryreachingme


2019-04-10 06:56:08 8 Comments

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?

1 comments

@EzLo 2019-04-10 07:18:30

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.

@youcantryreachingme 2019-04-10 22:35:31

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.

Related Questions

Sponsored Content

1 Answered Questions

1 Answered Questions

[SOLVED] get inherit keys inside json

  • 2019-02-18 14:30:58
  • Bear Brown
  • 21 View
  • 0 Score
  • 1 Answer
  • Tags:   postgresql json

3 Answered Questions

[SOLVED] Index on JSON field with dynamic keys

1 Answered Questions

[SOLVED] PostgreSQL, finding elements by value in numeric JSON arrays

1 Answered Questions

0 Answered Questions

JSON format for time-logging

  • 2016-10-02 16:13:51
  • user2250649
  • 11 View
  • 1 Score
  • 0 Answer
  • Tags:   json

1 Answered Questions

1 Answered Questions

[SOLVED] Aggregate count over variable number of json keys

1 Answered Questions

[SOLVED] Postgres query to return JSON object keys as array

Sponsored Content