JSON query issues

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715889

    Comments posted to this topic are about the item JSON query issues

  • n.ryan

    SSCrazy

    Points: 2235

    Strange... I was looking for the obvious answer that the data was missing a comma after the row Anderson.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71401

    Good to see a question about JSON, thanks Steve
    Very handy functionality - make extensive use of it lately...

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    β€œlibera tute vulgaris ex”

  • nigel.

    SSChampion

    Points: 11579

    n.ryan - Thursday, June 15, 2017 12:22 AM

    Strange... I was looking for the obvious answer that the data was missing a comma after the row Anderson.

    Yeah, me too. And the additional comma after the last but one '}'

    If you fix these you then get the error:

    Msg 13608, Level 16, State 2, Line 14
    Property cannot be found on the specified JSON path.

    Which I think should be the correct answer? 

  • n.ryan

    SSCrazy

    Points: 2235

    n.ryan - Thursday, June 15, 2017 12:22 AM

    Strange... I was looking for the obvious answer that the data was missing a comma after the row Anderson.

    Now I'm in the office and sitting in front of a convenient SQL prompt, the example should be updated a little to match to answers. The error that's listed as:

    Msg 13609, Level 16, State 2, Line 14
    JSON text is not properly formatted. Unexpected character '"' is found at position 130.


    is the expected error given the missing comma in the JSON data. However this doens't match any of the answers.... Fixing this, and the second data error due to the extraneous comma closing the Offense element, the actual error message will be:

    Msg 13608, Level 16, State 5, Line 13
    Property cannot be found on the specified JSON path.

    Pretty easy to select the appropriate error from those offered now... πŸ™‚

    EDIT: Looks like nigel. beat me to posting the more detailed answer while I was typing mine!

  • Ed Wagner

    SSC Guru

    Points: 286958

    I'm familiar with JSON, but haven't used it in SQL at all.  The developers are still in the process of migrating things to SQL 2016 dev.  It'll be a while until we get to production.

  • Jacob Wilkins

    One Orange Chip

    Points: 27727

    Yeah, as others have pointed out, the JSON is actually improperly formatted with a missing comma in one place and an extra comma elsewhere.

    Since "The JSON is improperly formatted" wasn't one of the answers, I just took a stab at inferring the solution, which proved to be pretty simple.

    The other 3 options are just wrong, so... πŸ™‚

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715889

    Comma removed. Ah, the errors of cut and paste and then fix something.

    Points awarded back to this time.

  • Jacob Wilkins

    One Orange Chip

    Points: 27727

    Steve Jones - SSC Editor - Thursday, June 15, 2017 11:00 AM

    Comma removed. Ah, the errors of cut and paste and then fix something.

    Points awarded back to this time.

    I think we've all been there RE: copy and paste πŸ™‚

    Still missing the comma after Anderson, though.

    Further, when that is fixed, the actual error message caused by the case problem is:

    Msg 13608, Level 16, State 2, Line 13
    Property cannot be found on the specified JSON path.Cheers!

  • funbi

    SSCarpal Tunnel

    Points: 4992

    Wow so much nitpicking! There was enough information in the original question (even with typos) to be able to answer correctly...

  • n.ryan

    SSCrazy

    Points: 2235

    funbi - Friday, June 16, 2017 2:51 AM

    Wow so much nitpicking! There was enough information in the original question (even with typos) to be able to answer correctly...

    Now if only MS-SQL was so understanding! It would be lovely if it would interpret the SQL code as I meant it, not how it interprets it...

  • TomThomson

    SSC Guru

    Points: 104772

    funbi - Friday, June 16, 2017 2:51 AM

    Wow so much nitpicking! There was enough information in the original question (even with typos) to be able to answer correctly...

    Actually no.   There was no correct answer to the original question, so eliminating 3 of the the 4 answers didn't leave anyone with a correct answer, only with the illusion of one.

    Of course with the  question as it now is, there is still no correct answer -  an answer something like "the json parsing code in your sql system has become corrupt and broken" could be correct, since if the pasing code was working it wouldn't produce the nonsense the question says it does, but that isn't currently one of the answer options. 

    It would be sensible if the  question were fixed so that the correct answer was correct, or if the supposedly correct answer were changed so that it answerd thequestion insteadof something else.  If what you call "nitpicking" obtains a change with makes the question an answer work correctly, then the nitpicking will have been useful.  But I doubt that will happen.

    Tom

  • funbi

    SSCarpal Tunnel

    Points: 4992

    n.ryan - Friday, June 16, 2017 3:06 AM

    funbi - Friday, June 16, 2017 2:51 AM

    Wow so much nitpicking! There was enough information in the original question (even with typos) to be able to answer correctly...

    Now if only MS-SQL was so understanding! It would be lovely if it would interpret the SQL code as I meant it, not how it interprets it...

    TomThomson - Monday, June 19, 2017 10:58 AM

    funbi - Friday, June 16, 2017 2:51 AM

    Wow so much nitpicking! There was enough information in the original question (even with typos) to be able to answer correctly...

    Actually no.   There was no correct answer to the original question, so eliminating 3 of the the 4 answers didn't leave anyone with a correct answer, only with the illusion of one.

    Of course with the  question as it now is, there is still no correct answer -  an answer something like "the json parsing code in your sql system has become corrupt and broken" could be correct, since if the pasing code was working it wouldn't produce the nonsense the question says it does, but that isn't currently one of the answer options. 

    It would be sensible if the  question were fixed so that the correct answer was correct, or if the supposedly correct answer were changed so that it answerd thequestion insteadof something else.  If what you call "nitpicking" obtains a change with makes the question an answer work correctly, then the nitpicking will have been useful.  But I doubt that will happen.

    Welp, I managed to answer it correctly even before the typo was corrected... as did most people

  • TomThomson

    SSC Guru

    Points: 104772

    funbi - Monday, June 19, 2017 2:21 PM

    Welp, I managed to answer it correctly even before the typo was corrected... as did most people

    No, you didn't.  You managed to pick the wrong answer which was being treated as correct - you didn't answer it correctly, because QOTD has no mechanis,m for saying "all the answer options are wrong".

    Tom

  • funbi

    SSCarpal Tunnel

    Points: 4992

    TomThomson - Friday, June 23, 2017 7:15 PM

    funbi - Monday, June 19, 2017 2:21 PM

    Welp, I managed to answer it correctly even before the typo was corrected... as did most people

    No, you didn't.  You managed to pick the wrong answer which was being treated as correct - you didn't answer it correctly, because QOTD has no mechanis,m for saying "all the answer options are wrong".

    *sigh* πŸ™‚

    Here's the thing: in the real world you don't always get a perfect spec and people don't always know how to ask for what they want using all the correct technical terms. If you are pedantic about everything then how do you see through the requirement spec to figure out what the job is? For this QOTD we had enough information to see what the question was really asking and what the intended correct answer was.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply