How to generate json data with group by in SQL

Question

I have a table as shown below

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

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

Answer 1

..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