Must numeric JSON keys be quoted? Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)PostgreSQL, finding elements by value in numeric JSON arraysPostgres function to merge two json objects with overlapping keys into one objectPostgresql: Optimize finding all distinct JSON keys from a colulmnPostgres query to return JSON object keys as arrayAggregate count over variable number of json keysConfiguring a non-nullable foreign key uniqueidentifier with a default Guid.Empty value that exists in foreign tableJSON format for time-loggingIndex on JSON field with dynamic keysget inherit keys inside jsonSQL Server Exclusive(X) lock not consistently blocking Shared(S) lock on a resource

Why are both D and D# fitting into my E minor key?

Is it common practice to audition new musicians 1-2-1 before rehearsing with the entire band?

Fantasy story; one type of magic grows in power with use, but the more powerful they are, they more they are drawn to travel to their source

Is it cost-effective to upgrade an old-ish Giant Escape R3 commuter bike with entry-level branded parts (wheels, drivetrain)?

Denied boarding although I have proper visa and documentation. To whom should I make a complaint?

How does the math work when buying airline miles?

Is safe to use va_start macro with this as parameter?

Is "Reachable Object" really an NP-complete problem?

When the Haste spell ends on a creature, do attackers have advantage against that creature?

Do I really need to have a message in a novel to appeal to readers?

When a candle burns, why does the top of wick glow if bottom of flame is hottest?

Withdrew £2800, but only £2000 shows as withdrawn on online banking; what are my obligations?

How do I find out the mythology and history of my Fortress?

How to tell that you are a giant?

An adverb for when you're not exaggerating

Circuit to "zoom in" on mV fluctuations of a DC signal?

Using audio cues to encourage good posture

Wu formula for manifolds with boundary

How to react to hostile behavior from a senior developer?

また usage in a dictionary

Why are the trig functions versine, haversine, exsecant, etc, rarely used in modern mathematics?

Irreducible of finite Krull dimension implies quasi-compact?

How to down pick a chord with skipped strings?

Do I really need recursive chmod to restrict access to a folder?



Must numeric JSON keys be quoted?



Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)PostgreSQL, finding elements by value in numeric JSON arraysPostgres function to merge two json objects with overlapping keys into one objectPostgresql: Optimize finding all distinct JSON keys from a colulmnPostgres query to return JSON object keys as arrayAggregate count over variable number of json keysConfiguring a non-nullable foreign key uniqueidentifier with a default Guid.Empty value that exists in foreign tableJSON format for time-loggingIndex on JSON field with dynamic keysget inherit keys inside jsonSQL Server Exclusive(X) lock not consistently blocking Shared(S) lock on a resource



.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


      1






      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 Apr 10 at 7:00









      EzLo

      2,9231621




      2,9231621










      asked Apr 10 at 6:56









      youcantryreachingmeyoucantryreachingme

      4838




      4838




















          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
            Apr 10 at 22:35











          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
            Apr 10 at 22:35















          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
            Apr 10 at 22:35













          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 Apr 10 at 7:18









          EzLoEzLo

          2,9231621




          2,9231621












          • 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

















          • 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
















          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





          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

















          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

          រឿង រ៉ូមេអូ និង ហ្ស៊ុយលីយេ សង្ខេបរឿង តួអង្គ បញ្ជីណែនាំ

          Crop image to path created in TikZ? Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Crop an inserted image?TikZ pictures does not appear in posterImage behind and beyond crop marks?Tikz picture as large as possible on A4 PageTransparency vs image compression dilemmaHow to crop background from image automatically?Image does not cropTikzexternal capturing crop marks when externalizing pgfplots?How to include image path that contains a dollar signCrop image with left size given

          Romeo and Juliet ContentsCharactersSynopsisSourcesDate and textThemes and motifsCriticism and interpretationLegacyScene by sceneSee alsoNotes and referencesSourcesExternal linksNavigation menu"Consumer Price Index (estimate) 1800–"10.2307/28710160037-3222287101610.1093/res/II.5.31910.2307/45967845967810.2307/2869925286992510.1525/jams.1982.35.3.03a00050"Dada Masilo: South African dancer who breaks the rules"10.1093/res/os-XV.57.1610.2307/28680942868094"Sweet Sorrow: Mann-Korman's Romeo and Juliet Closes Sept. 5 at MN's Ordway"the original10.2307/45957745957710.1017/CCOL0521570476.009"Ram Leela box office collections hit massive Rs 100 crore, pulverises prediction"Archived"Broadway Revival of Romeo and Juliet, Starring Orlando Bloom and Condola Rashad, Will Close Dec. 8"Archived10.1075/jhp.7.1.04hon"Wherefore art thou, Romeo? To make us laugh at Navy Pier"the original10.1093/gmo/9781561592630.article.O006772"Ram-leela Review Roundup: Critics Hail Film as Best Adaptation of Romeo and Juliet"Archived10.2307/31946310047-77293194631"Romeo and Juliet get Twitter treatment""Juliet's Nurse by Lois Leveen""Romeo and Juliet: Orlando Bloom's Broadway Debut Released in Theaters for Valentine's Day"Archived"Romeo and Juliet Has No Balcony"10.1093/gmo/9781561592630.article.O00778110.2307/2867423286742310.1076/enst.82.2.115.959510.1080/00138380601042675"A plague o' both your houses: error in GCSE exam paper forces apology""Juliet of the Five O'Clock Shadow, and Other Wonders"10.2307/33912430027-4321339124310.2307/28487440038-7134284874410.2307/29123140149-661129123144728341M"Weekender Guide: Shakespeare on The Drive""balcony"UK public library membership"romeo"UK public library membership10.1017/CCOL9780521844291"Post-Zionist Critique on Israel and the Palestinians Part III: Popular Culture"10.2307/25379071533-86140377-919X2537907"Capulets and Montagues: UK exam board admit mixing names up in Romeo and Juliet paper"Istoria Novellamente Ritrovata di Due Nobili Amanti2027/mdp.390150822329610820-750X"GCSE exam error: Board accidentally rewrites Shakespeare"10.2307/29176390149-66112917639"Exam board apologises after error in English GCSE paper which confused characters in Shakespeare's Romeo and Juliet""From Mariotto and Ganozza to Romeo and Guilietta: Metamorphoses of a Renaissance Tale"10.2307/37323537323510.2307/2867455286745510.2307/28678912867891"10 Questions for Taylor Swift"10.2307/28680922868092"Haymarket Theatre""The Zeffirelli Way: Revealing Talk by Florentine Director""Michael Smuin: 1938-2007 / Prolific dance director had showy career"The Life and Art of Edwin BoothRomeo and JulietRomeo and JulietRomeo and JulietRomeo and JulietEasy Read Romeo and JulietRomeo and Julieteeecb12003684p(data)4099369-3n8211610759dbe00d-a9e2-41a3-b2c1-977dd692899302814385X313670221313670221