Return SQL Server database query with nested Json

Question

I am trying to get this kind of answer when I consume my endpoint :

[  
   {  
      "McqID":"7EED5396-9151-4E3D-BCBF-FDB72CDD22B7",
      "Questions":[  
         {  
            "QuestionId":"C8440686-531D-4099-89E9-014CAF9ED054",
            "Question":"human text",
            "Difficulty":3,
            "Answers":[  
               {  
                  "AnswerId":"7530DCF4-B2D9-48B0-9978-0E4690EA0C34",
                  "Answer":"human text2",
                  "IsTrue":false
               },
               {  
                  "AnswerId":"5D16F17F-E205-42A5-873A-1A367924C182",
                  "Answer":"human text3",
                  "IsTrue":false
               },
               {  
                  "AnswerId":"64E78326-77C3-4628-B9E3-2E8614D63632",
                  "Answer":"human text4",
                  "IsTrue":false
               },
               {  
                  "AnswerId":"199241A9-0EF6-4F96-894A-9256B129CB1F",
                  "Answer":"human text5",
                  "IsTrue":true
               },
               {  
                  "AnswerId":"EDCCAC18-5209-4457-95F2-C91666F8A916",
                  "Answer":"human text6",
                  "IsTrue":false
               }
            ]
         }
      ]
   }
]

Here's my query (example) :

SELECT 
    Questions.QcmID AS QcmID, 
    (SELECT 
         Questions.id AS QuestionId, 
         Questions.Intitule AS Question, 
         Questions.Difficulte AS Difficulty, 
         (SELECT 
              Reponses.id AS AnswerId, 
              Reponses.Libelle AS Answer, 
              Reponses.IsTrue AS IsTrue
          FROM 
              Reponses
          WHERE 
              Reponses.QuestionID = Questions.id
          FOR JSON PATH) AS Answers
     FROM 
         Questions
     WHERE 
         Questions.QcmID = '7EED5396-9151-4E3D-BCBF-FDB72CDD22B7'
     FOR JSON PATH) AS Questions
FROM 
    Questions
WHERE 
    Questions.QcmID = '7EED5396-9151-4E3D-BCBF-FDB72CDD22B7'
FOR JSON PATH

I want a nested JSON representing my data, but it ends up being formatted like (smaller example) :

[  
   {  
      "JSON_F52E2B61-18A1-11d1-B105-00805F49916B":"[{\"QcmID\":\"7EED5396-9151-4E3D-BCBF-FDB72CDD22B7\"}]"
   }
]

I've tried everything, FOR JSON PATH, FOR JSON AUTO, JSON_QUERY, etc...

Nothing works. FOR JSON PATH doesn't seem to work with multiple nested collections.

How do I get this result ?

Answer 1

You need to use JOINs as you would normally. Using FOR JSON AUTO will pick the JOIN alias and if you want more control use the FOR JSON PATH.

I'm going to give you a generic example that will be easy to map to your scenario:

Option 1 - FOR JSON AUTO: The JOIN alias will be used as the nested collection property name.

SELECT
    ent.Id AS 'Id',
    ent.Name AS 'Name',
    ent.Age AS 'Age',
    Emails.Id AS 'Id',
    Emails.Email AS 'Email'
FROM Entities ent
LEFT JOIN EntitiesEmails Emails ON Emails.EntityId = ent.Id
FOR JSON AUTO

Option 2 - FOR JSON PATH: You handle everything and note that the inner select must return a string, here also using FOR JSON PATH.

SELECT
    ent.Id AS 'Id',
    ent.Name AS 'Name',
    ent.Age AS 'Age',
    EMails = (
        SELECT
            Emails.Id AS 'Id',
            Emails.Email AS 'Email'
        FROM EntitiesEmails Emails WHERE Emails.EntityId = ent.Id
        FOR JSON PATH
    )
FROM Entities ent
FOR JSON PATH

Both generate the same result:

[{
    "Id": 1,
    "Name": "Alex",
    "Age": 35,
    "Emails": [{
        "Id": 1,
        "Email": "[email protected]"
    }, {
        "Id": 2,
        "Email": "[email protected]"
    }, {
        "Id": 3,
        "Email": "[email protected]"
    }]
}, {
    "Id": 2,
    "Name": "Another Ale",
    "Age": 40,
    "Emails": [{
        "Id": 4,
        "Email": "[email protected]"
    }, {
        "Id": 5,
        "Email": "[email protected]"
    }]
}, {
    "Id": 3,
    "Name": "John Doe",
    "Age": 33,
    "Emails": [{
        "Id": 6,
        "Email": "[email protected]"
    }]
}, {
    "Id": 4,
    "Name": "Mario",
    "Age": 54,
    "Emails": [{}]
}]

Cheers!