返回带有嵌套Json的SQL Server数据库查询 [英] Return SQL Server database query with nested Json

问题描述

当我消耗端点时，我试图得到这种答案:

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

我想要一个嵌套的JSON来表示我的数据，但是最终却被格式化为(较小的示例):

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\"}]"
   }
]

我已经尝试了所有内容，例如FOR JSON PATH，FOR JSON AUTO，JSON_QUERY等...

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

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

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

如何获得此结果?

推荐答案

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

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:

选项1-FOR JSON AUTO: JOIN别名将用作嵌套的集合属性名称.

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

选项2-FOR JSON PATH: 处理完所有内容后，请注意内部select必须返回一个字符串，这里也要使用FOR JSON PATH.

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

两者都会产生相同的结果:

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

干杯！

这篇关于返回带有嵌套Json的SQL Server数据库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！