0

I'm trying to figure out the appropriate syntax to query the Note column from only the "1" object under "worklistData"...and failing miserably. I've gone through several iterations, but the code below seems to be what "should" be working (but clearly isn't). The query's good up until the last CROSS APPLY, then it's kicking back the error:

"JSON path is not properly formatted. Unexpected character '1' is found at position 2."

Would one of you be so kind as to point out where I'm screwing this up?

DECLARE @JSON VARCHAR(MAX) = 
'
{
   "_id":143884,
   "member":{
      "memberId":"12345",
      "firstName":"Test",
      "lastName":"Test"
   },
   "worklistData":{
      "1":{
         "Note":"Note 1 text goes here",
         "thUpdateCount":26
      },
      "2":{
         "Note":"This is a test",
         "thUpdateCount":1
      }
   }
}
';

SELECT RT.*, MEM.*, WL1.*
    FROM OPENJSON(@JSON)
            WITH (
                    SK VARCHAR(MAX) '$._id'
                 ) RT

        CROSS APPLY OPENJSON(@JSON, '$.member')
            WITH (
                    SourceMemberID VARCHAR(MAX) '$.memberId',
                    FirstName VARCHAR(MAX) '$.firstName',
                    LastName VARCHAR(MAX) '$.lastName'
                 )MEM

        CROSS APPLY OPENJSON(@JSON, '$.worklistData') WL

        CROSS APPLY OPENJSON(WL.value, '$.1')
            WITH (
                    Note1 VARCHAR(MAX) '$.Note'
                 ) WL1;

Thanks in advance!

Zhorov
  • 28,486
  • 6
  • 27
  • 52
Zac Truelove
  • 23
  • 6
  • 2
    `1` is not a valid identifier on its own and must be properly quoted (`$."1"`). Your query will then be empty, however, because you've already taken `worklistData` apart. You can simply change the last bit to `CROSS APPLY OPENJSON(@JSON, '$.worklistData."1"') WITH (Note VARCHAR(MAX) '$.Note') WL` to get the object directly. – Jeroen Mostert May 27 '21 at 15:06
  • Thank you, @JeroenMostert!! I had tried something similar without the quotes around the 1. That's the missing piece! – Zac Truelove May 27 '21 at 15:14

0 Answers0