SQL Server JSON output of nested tables-CodePudding

I have the following tables:

create table students 
(
    id int, 
    name varchar(10)
)    

create table subjects
(
    subjectId int, 
    studentId int, 
    subject varchar(12)
)  

create table marks
(
    studentId int, 
    subjectId int, 
    marks int 
)    

create table sports
(
    sportId int, 
    studentId int, 
    name varchar(12) 
)

with the following data:

insert into students values(1, 'Rusty');   
insert into subjects values(1, 1, 'math')    
insert into subjects values(2, 1, 'science')    
insert into marks values(1,1,50)    
insert into marks values(1,2,60)    
insert into sports values(1, 1, 'soccer')    
insert into sports values(2, 1, 'baseball')

I want to write a query in SQL Server to get the following output:

studentId = 1    
{    
    "id": 1,    
    "name": "Rusty",    
    "subjects" : [    
        {    
            "name": "math",    
            "marks": 50    
        },    
        {    
            "name": "science",    
            "marks": 60    
        }    
    ],    
    "sports": [    
        {    
            "name": "soccer"    
        },    
        {    
            "name": "baseball"    
        }    
    ]    
}

I tried the following query

select *

from students s

join subjects su on (s.id = su.studentId)

join sports sp on (s.id = sp.studentId)

where s.id = 1

for json auto

and here is the output:

[

{

    "id": 1,

    "name": "Rusty",

    "su": [

        {

            "subjectId": 1,

            "studentId": 1,

            "subject": "math",

            "sp": [

                {

                    "sportId": 1,

                    "studentId": 1,

                    "name": "soccer"

                }

            ]

        },

        {

            "subjectId": 1,

            "studentId": 1,

            "subject": "science",

            "sp": [

                {

                    "sportId": 1,

                    "studentId": 1,

                    "name": "soccer"

                }

            ]

        },

        {

            "subjectId": 1,

            "studentId": 1,

            "subject": "math",

            "sp": [

                {

                    "sportId": 1,

                    "studentId": 1,

                    "name": "baseball"

                }

            ]

        },

        {

            "subjectId": 1,

            "studentId": 1,

            "subject": "science",

            "sp": [

                {

                    "sportId": 1,

                    "studentId": 1,

                    "name": "baseball"

                }

            ]

        }

    ]

}

]

CodePudding user response：

For your desired output you can use correlated subqueries for sports and subjects that generate their own JSON, using FOR JSON PATH, and include that information as an array of nested objects in your main JSON output by way of JSON_QUERY (Transact-SQL), e.g.:

/*
 * Data setup...
 */
create table students (
  id int,
  name varchar(10)
);   
create table subjects (
  subjectId int,
  studentId int,
  subject varchar(12)
);
create table marks (
  studentId int,
  subjectId int,
  marks int
);
create table sports (
  sportId int,
  studentId int,
  name varchar(12)
);
insert into students (id, name) values
  (1, 'Rusty');
insert into subjects (subjectId, studentId, subject) values
  (1, 1, 'math'),
  (2, 1, 'science');
insert into marks (studentId, subjectId, marks) values
  (1,1,50),
  (1,2,60);
insert into sports (sportId, studentId, name) values
  (1, 1, 'soccer'),
  (2, 1, 'baseball');
/*
 * Example query...
 */
select
  students.id,
  students.name,
  json_query(( --<<-- doubled brakcets
    select
      subjects.subject,
      marks.marks
    from subjects
    join marks
      on marks.subjectId = subjects.subjectId
      and marks.studentId = subjects.studentId
    where subjects.studentId = students.id
    for json path
  )) as [subjects],
  json_query(( --<<-- doubled brackets
    select
      sports.name
    from sports
    where sports.studentId = students.id
    for json path
  )) as [sports]
from students
where students.id = 1
for json path, without_array_wrapper;

Which yields the JSON output:

{
    "id": 1,
    "name": "Rusty",
    "subjects": [
        {
            "subject": "math",
            "marks": 50
        },
        {
            "subject": "science",
            "marks": 60
        }
    ],
    "sports": [
        {
            "name": "soccer"
        },
        {
            "name": "baseball"
        }
    ]
}