Create nested JSON arrays using FOR JSON PATH from stored procedure

Question

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

Using dynamic property names "506995", "506996" and "506997" like that is just awful. Is that an actual business requirement or just something you wanted to do? — AlwaysLearning
– AlwaysLearning, Commented Jun 24, 2022 at 14:01
Yes, that's what I figured out. Not suggestible and straightforward to use dynamic property names. Will be implementing PARENT / CHILD for this. But still, is this doable? dynamic property names? — Sky Monster
– Sky Monster, Commented Jun 24, 2022 at 14:14

Charlieface · Accepted Answer · 2022-06-24 14:30:19Z

2

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

answered Jun 24, 2022 at 14:30

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

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

6 Comments

Sky Monster Over a year ago

can we parse this string to JSON Object as when returning this in output parameter in Logic app, its appending backslash' \' with all output values? and also adding extra quotes at the start and end. or can we somehow remove these escaping characters? Tried, OPENJSON but didn't able to. "[{\"506997\":[{\"subject\":\"Physics\",\"result\":\"Pass\"}

Charlieface Over a year ago

There is no JSON object in SQL Server, it's always nvarchar(max). The extra escaping is caused because Logic understands it as a string. You need to parse it into an object once you get it into your app, I suggest you create a different question for that.

Sky Monster Over a year ago

should we parse this string and pass the JSON object to the logic app or (not sure) we can parse it in the Logic App itself.

Charlieface Over a year ago

In SQL Server there is nothing to parse. JSON is just a string. You need to parse it in the app.

Sky Monster Over a year ago

This is by default sorting it by ascending order by StudentID. How can we use order by in it, if we want to order it or rather keep the same sequence as we have in the table? or order it by any other table column.

|

Collectives™ on Stack Overflow

Create nested JSON arrays using FOR JSON PATH from stored procedure

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related