Renvoyer la requête de base de données SQL Server avec JSON imbriqué

Question

Je essaie d'obtenir ce genre de réponse quand je consomme mon endpoint :

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

Voici ma requête (exemple) :

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

Je veux un JSON imbriqué représentant mes données, mais cela se présente sous cette forme (petit exemple) :

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

J'ai tout essayé, FOR JSON PATH, FOR JSON AUTO, JSON_QUERY, etc...

Rien ne marche. FOR JSON PATH ne semble pas fonctionner avec des collections imbriquées multiples.

Comment obtenir ce résultat ?

Answer 1

Vous devez utiliser les JOINS comme vous le feriez normalement. En utilisant FOR JSON AUTO, vous choisirez l'alias JOIN et si vous voulez plus de contrôle, utilisez le FOR JSON PATH.

Je vais vous donner un exemple générique qui sera facile à appliquer à votre scénario :

Option 1 - FOR JSON AUTO : L'alias du JOIN sera utilisé comme nom de propriété de la collection imbriquée.

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 : Vous gérez tout et notez que la sélect interne doit retourner une chaîne, ici aussi en utilisant 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

Les deux génèrent le même résultat :

[{
    "Id": 1,
    "Name": "Alex",
    "Age": 35,
    "Emails": [{
        "Id": 1,
        "Email": "abc@domain.com"
    }, {
        "Id": 2,
        "Email": "def@domain.com"
    }, {
        "Id": 3,
        "Email": "ghi@domain.net"
    }]
}, {
    "Id": 2,
    "Name": "Another Ale",
    "Age": 40,
    "Emails": [{
        "Id": 4,
        "Email": "user@skdfh.com"
    }, {
        "Id": 5,
        "Email": "asldkj@als09q834.net"
    }]
}, {
    "Id": 3,
    "Name": "John Doe",
    "Age": 33,
    "Emails": [{
        "Id": 6,
        "Email": "ooaoasdjj@ksjsk0913.org"
    }]
}, {
    "Id": 4,
    "Name": "Mario",
    "Age": 54,
    "Emails": [{}]
}]

Santé !