How to generate json data with group by in SQL

user_14040749 0
Viewed 1124

I have a table as shown below

Table structure

Adding table scripts below.

CREATE TABLE [dbo].[TempStudentJsonData](
    [StudentID] [int] NULL,
    [ClassID] [varchar](500) NULL,
    [DocumentID] [int] NULL,
    [DocumentChildID] [int] NULL,
    [UploadedFileName] [varchar](500) NULL,
    [documentname] [varchar](500) NULL
) ON [PRIMARY]
GO

And following are the test data.

INSERT [dbo].[TempStudentJsonData] ([StudentID], [ClassID], [DocumentID], [DocumentChildID], [UploadedFileName], [documentname]) VALUES (506995, N'CLASS-X', 5055, 0, N'P1.pdf', N'PHYSICS')
GO
INSERT [dbo].[TempStudentJsonData] ([StudentID], [ClassID], [DocumentID], [DocumentChildID], [UploadedFileName], [documentname]) VALUES (506995, N'CLASS-X', 5058, 0, N'C1.pdf', N'CHEMISTRY')
GO
INSERT [dbo].[TempStudentJsonData] ([StudentID], [ClassID], [DocumentID], [DocumentChildID], [UploadedFileName], [documentname]) VALUES (506995, N'CLASS-X', 5066, 387654, N'B1.pdf', N'BIOLOGY')
GO
INSERT [dbo].[TempStudentJsonData] ([StudentID], [ClassID], [DocumentID], [DocumentChildID], [UploadedFileName], [documentname]) VALUES (506995, N'CLASS-X', 5066, 387655, N'B2.pdf', N'BIOLOGY')
GO
INSERT [dbo].[TempStudentJsonData] ([StudentID], [ClassID], [DocumentID], [DocumentChildID], [UploadedFileName], [documentname]) VALUES (506995, N'CLASS-X', 5151, 387656, N'H1.pdf', N'HISTORY')
GO

From this table i want to generate a json as shown below

[
  {
    "StudentID": 506995,
    "documents": [
      {
        "documentname": "PHYSICS",
        "attachments": [
          {
            "attachmentname": "P1.pdf"
          }
        ]
      },
      {
        "documentname": "CHEMISTRY",
        "attachments": [
          {
            "attachmentname": "C1.pdf"
          }
        ]
      },
      {
        "documentname": "BIOLOGY",
        "attachments": [
          {
            "attachmentname": "B1.pdf"
          },
          {
            "attachmentname": "B2.pdf"

          }
        ]
      },
      {
        "documentname": "HISTORY",
        "attachments": [
          {
            "attachmentname": "H1.pdf"
          }
        ]
      }
    ]
  }
]

I am tried to generate json using the following query

SELECT [StudentID],            
          (
             SELECT documentname,                  
          (
             SELECT UploadedFileName    as attachmentname          
                              
             FROM TempStudentJsonData
              FOR JSON PATH
          ) AS attachments
             FROM TempStudentJsonData FOR JSON PATH
          ) AS documents
             FROM TempStudentJsonData group by [DocumentID],[StudentID]  FOR JSON PATH

and getting the result as follow

tries

How can i map only the desired uploaded files to respective document name (grouping the uploaded files to respective document name or document id)? Please help

stored-procedures sql-server sql json
2 Answers

..correlate the subquery for the documentnames(for each student) and then the documents(for each student&documentname)

 SELECT st.[StudentID],            
          (
          
             SELECT distinct dn.documentname,                  
          (
             SELECT uf.UploadedFileName    as attachmentname          
             FROM TempStudentJsonData as uf
             where uf.StudentId  = dn.StudentId
             and uf.DocumentId = dn.DocumentId
              FOR JSON PATH
          ) AS attachments
          FROM TempStudentJsonData as dn
          where dn.StudentId = st.StudentId
          FOR JSON PATH
          ) AS documents
FROM TempStudentJsonData as st 
group by st.[StudentID]  FOR JSON PATH
user_124036120

Another possible approach is to use FOR JSON AUTO and the appropriate joins. As is mentioned in the documentation, when ... you specify the AUTO option, the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables.

SELECT DISTINCT 
   students.StudentID, 
   documents.DocumentName, 
   attachments.UploadedFileName AS attachmentname
FROM TempStudentJsonData students
JOIN TempStudentJsonData documents ON students.StudentID = documents.StudentID
JOIN TempStudentJsonData attachments ON students.StudentID = documents.StudentID AND documents.DocumentID = attachments.DocumentID
FOR JSON AUTO

Result:

[
   {
      "StudentID":506995,
      "documents":[
         {
            "DocumentName":"BIOLOGY",
            "attachments":[
               {"attachmentname":"B1.pdf"},
               {"attachmentname":"B2.pdf"}
            ]
         },
         {
            "DocumentName":"CHEMISTRY",
            "attachments":[
               {"attachmentname":"C1.pdf"}
            ]
         },
         {
            "DocumentName":"HISTORY",
            "attachments":[
               {"attachmentname":"H1.pdf"}
            ]
         },
         {
            "DocumentName":"PHYSICS",
            "attachments":[
               {"attachmentname":"P1.pdf"}
            ]
         }
      ]
   }
]
user_65780800
Related