SQL JSON : Nest few nodes under a custom node using sql query FOR JSON

Question

So I have written a query that gives the output as an array of JSON objects, one of the objects is below

{

        "cardType": "abc",
        "createdOnDateTime": "2020-03-26",
        "courseName": "course1",
        "courseID": 1,
        "sectionName": 1,
        "studentList": [
            {
                "name": "student 1",
                "nameLink": "0"
            },
            {
                "name": "student 2",
                "nameLink": "0"
            },
            {
                "name": "student 3",
                "nameLink": "0"
            }
        ]
    }

But I want the output as

    {
        "cardType": "abc",
        "createdOnDateTime": "2020-03-26",
        "payload" : {
            "courseName": "course1",
            "courseID": 1,
            "sectionName": 1,
            "studentList": [
                {
                    "name": "student 1",
                    "nameLink": "0"
                },
                {
                    "name": "student 2",
                    "nameLink": "0"
                },
                {
                    "name": "student 3",
                    "nameLink": "0"
                }
            ]
        }
    }

I have used 'For JSON Auto' phrase at the end of my Select query and as the course and student has a One-to-many relation, the student gets formatted in an array. What I want is that few nodes along with the "studentList" node array should be nested under a custom node 'payload'. How can this be achieved in SQL query using the For JSON and it's related properties?

Answer 1

Totally guessing here on what your data and query looks like based on what little you've given us so far. Use a subquery for studentList with for json auto then, on your outer query use for json path, without_array_wrapper.

When using for json path you can nest elements inside each other by giving them dot-separated paths, i.e.: separating parent elements from children with period (.) characters, such as the following...

create table dbo.Course (
  cardType nvarchar(3),
  createdOnDateTime date,
  courseName nvarchar(20),
  courseID int,
  sectionName int
);
insert dbo.Course values
  ('abc', '2020-03-26', 'course1', 1, 1);
go
create table dbo.Student (
  courseID int,
  name nvarchar(20),
  nameLink nvarchar(20)
);
insert dbo.Student values
  (1, 'student 1', '0'),
  (1, 'student 2', '0'),
  (1, 'student 3', '0');
go
select
  cardType,
  createdOnDateTime,
  [payload.courseName] = courseName,
  [payload.courseID] = courseID,
  [payload.sectionName] = sectionName,
  [payload.studentList] = (
    select name, nameLink
    from dbo.Student S1
    where S1.courseID = C1.courseID
    for json auto
  )
from dbo.Course C1
where courseID = 1
for json path, without_array_wrapper;
go

Which yields the result...

{
    "cardType": "abc",
    "createdOnDateTime": "2020-03-26",
    "payload": {
        "courseName": "course1",
        "courseID": 1,
        "sectionName": 1,
        "studentList": [
            {
                "name": "student 1",
                "nameLink": "0"
            },
            {
                "name": "student 2",
                "nameLink": "0"
            },
            {
                "name": "student 3",
                "nameLink": "0"
            }
        ]
    }
}

Answer 2

My query was resolved eventually, I found this really helpful video on youtube that exactly shows what I had to do. Click here for the video. Also one more thing, this video is a very good example, but for large size data, this approach makes the query very slow.