sql - 使用嵌套 Json 返回 SQL Server 数据库查询

Question

当我使用端点时，我试图得到这种答案:

[  
   {  
      "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
               }
            ]
         }
      ]
   }
]

这是我的查询(示例):

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

我想要一个嵌套的 JSON 来表示我的数据，但它最终的格式如下(较小的示例):

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

我已经尝试了一切，FOR JSON PATH、FOR JSON AUTO、JSON_QUERY等...

没有任何效果。 FOR JSON PATH 似乎不适用于多个嵌套集合。

我如何得到这个结果？

Answer 1

您需要像平常一样使用 JOIN。使用 FOR JSON AUTO 将选择 JOIN 别名，如果您想要更多控制，请使用 FOR JSON PATH。

我将为您提供一个通用示例，该示例将很容易映射到您的场景:

选项 1 - 对于 JSON AUTO:JOIN 别名将用作嵌套集合属性名称。

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

选项 2 - FOR JSON 路径:您处理所有事情并注意内部选择必须返回一个字符串，这里也使用 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

两者生成相同的结果:

[{
    "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": [{}]
}]

干杯!