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 ?

AlexCode · Accepted Answer · 2017-11-02 12:52:15Z

25

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!

edited Nov 2, 2017 at 12:52

answered Nov 2, 2017 at 9:39

AlexCode

4,1334 gold badges36 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andrius Naruševičius Over a year ago

Having more than one table similar in terms of relation like Emails would cause the AUTO way to act not as expected thus the PATH way should be chosen for cases like this. Not to mention I find it more elegant anyways.

Adam Łepkowski Over a year ago

We noticed that in this specyfic case performance was better for option 1.

MiraTech Over a year ago

How about Sql Server 2012?

AlexCode Over a year ago

SQL Server 2012 doesn't support "FOR JSON" so none of this applies. This feature was only introduced in SQL Server 2016. What you can use though is "FOR XML" and achieve similar results but serialized in XML.

user2814916 · Accepted Answer · 2023-06-27 06:17:34Z

0

Similar to the answer above but I found this more generally useful:

create table #T ([data] nvarchar(5))
Insert into #T ([data]) values ('data1'),('data2')

select L1=(select L2=(select * from #T as L3 for json path) for json path) for json path

giving:

[
   {
      "L1":[
         {
            "L2":[
               {
                  "data":"data1"
               },
               {
                  "data":"data2"
               }
            ]
         }
      ]
   }
]

answered Jun 27, 2023 at 6:17

user2814916

8310 bronze badges

Collectives™ on Stack Overflow

Return SQL Server database query with nested Json

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related