Must numeric JSON keys be quoted?





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







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?










share|improve this question































    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?










    share|improve this question



























      5












      5








      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?










      share|improve this question
















      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?







      sql-server t-sql json






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 21 hours ago









      EzLo

      2,8151621




      2,8151621










      asked 21 hours ago









      youcantryreachingmeyoucantryreachingme

      4788




      4788






















          1 Answer
          1






          active

          oldest

          votes


















          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.






          share|improve this answer
























          • 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
            6 hours ago












          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234394%2fmust-numeric-json-keys-be-quoted%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          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.






          share|improve this answer
























          • 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
            6 hours ago
















          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.






          share|improve this answer
























          • 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
            6 hours ago














          5












          5








          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.






          share|improve this 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.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered 21 hours ago









          EzLoEzLo

          2,8151621




          2,8151621













          • 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
            6 hours ago



















          • 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
            6 hours ago

















          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
          6 hours ago





          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
          6 hours ago


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234394%2fmust-numeric-json-keys-be-quoted%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Knooppunt Holsloot

          Altaar (religie)

          Gregoriusmis