Create nested JSON arrays using FOR JSON PATH from stored procedure-CodePudding

I have a table as shown below and need to return data in JSON format to Azure Logic App.

Adding table scripts below.

CREATE TABLE [dbo].[TempStudentJsonData]
(
    [StudentID] [int] NULL,
    [subject] [varchar](500) NULL,
    [result] [varchar](10) NULL
) 
GO

And following is the test data.

INSERT INTO [dbo].[TempStudentJsonData] ([StudentID], [subject], [result]) 
VALUES (506995, N'PHYSICS', N'Pass')
GO

INSERT INTO [dbo].[TempStudentJsonData] ([StudentID], [subject], [result]) 
VALUES (506995, N'CHEMISTRY', N'Fail')
GO

INSERT INTO [dbo].[TempStudentJsonData] ([StudentID], [subject], [result]) 
VALUES (506996, N'PHYSICS', N'Pass')
GO

INSERT INTO [dbo].[TempStudentJsonData] ([StudentID], [subject], [result]) 
VALUES (506996, N'BIOLOGY', N'Pass' )
GO

INSERT INTO [dbo].[TempStudentJsonData] ([StudentID], [subject], [result]) 
VALUES (506997, N'MATH', N'Pass')
GO

From this table, I want to generate a JSON as shown below

[
        {
            "506995": [
                {
                    "subject": "Physics",
                    "result": "Pass"
                },
                 {
                    "subject": "Chemistry",
                    "result": "Fail"
                }
            ]
        },
        {
            "506996" : [
                {
                    "subject": "Physics",
                    "reason": "Pass"
                },
                {
                    "subject": "Biology",
                    "reason": "Pass"
                }
            ]
        },
        {
            "506997" : [
                {
                    "subject": "Math",
                    "reason": "Pass"
                }
            ]
        }
]

How can we achieve this?

Thanks

CodePudding user response：

Unfortunately, SQL Server does not support dynamic keys, nor does it support JSON_AGG (creating a bare JSON array). So you need to build some of it yourself using STRING_AGG

SELECT '['   STRING_AGG(sjd.json, ',')   ']'
FROM (
    SELECT
      json =
      CONCAT(
        '{"',
        sjd.StudentID,
        '":[',
        STRING_AGG(j.json, ','),
        ']}'
      )
    FROM TempStudentJsonData sjd
    CROSS APPLY (
        SELECT
          sjd.subject,
          sjd.result
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) j(json)
    GROUP BY
      sjd.StudentID
) sjd;

Note that the benefit of this version over correlated self-joins is that you only scan the table once

db<>fiddle

Output:

[
  {
    "506995": [
      {
        "subject": "PHYSICS",
        "result": "Pass"
      },
      {
        "subject": "CHEMISTRY",
        "result": "Fail"
      }
    ]
  },
  {
    "506996": [
      {
        "subject": "PHYSICS",
        "result": "Pass"
      },
      {
        "subject": "BIOLOGY",
        "result": "Pass"
      }
    ]
  },
  {
    "506997": [
      {
        "subject": "MATH",
        "result": "Pass"
      }
    ]
  }
]