'T-SQL Formatting Nested JSON Output

I am using SQL Server 2019. My data has the overlapping values for all rows except 2. I would to be able to flatten this table and output it as JSON.

Schema


CREATE TABLE student_data(
     first_name varchar(50),
     last_name varchar(50),
     grade int,
     ID int,
     subject varchar(50),
     teacher varchar(50)
);


INSERT INTO student_data
VALUES ('John', 'Doe', 0, 1000, 'Home Room', 'Parker, Peter')
      ,('John', 'Doe', 0, 1000, 'Math', 'Quest, Jonny')
      ,('John', 'Doe', 0, 1000, 'Reading', 'Gordon, Flash')
      ,('John', 'Doe', 0, 1000, 'History', 'Banner, Bruce')
      ,('John', 'Doe', 0, 1000, 'Science', 'Kent, Clark')
      ,('Jane', 'Doe', 0, 2000, 'Home Room', 'Parker, Peter')
      ,('Jane', 'Doe', 0, 2000, 'Math', 'Quest, Jonny')
      ,('Jane', 'Doe', 0, 2000, 'Reading', 'Gordon, Flash')
      ,('Jane', 'Doe', 0, 2000, 'History', 'Banner, Bruce')
      ,('Jane', 'Doe', 0, 2000, 'Science', 'Kent, Clark');

Current Output


SELECT *
FROM student_data
WHERE ID=1000;
first_name last_name grade ID subject teacher
John Doe 0 1000 Home Room Parker, Peter
John Doe 0 1000 Math Quest, Jonny
John Doe 0 1000 Reading Gordon, Flash
John Doe 0 1000 History Banner, Bruce
John Doe 0 1000 Science Kent, Clark
SELECT *
FROM student_data
WHERE ID=1000
FOR JSON AUTO;
[
    {"first_name":"John",
    "last_name":"Doe",
    "grade":0,
    "ID":1000,
    "subject":"Home Room",
    "teacher":"Parker, Peter"},
    {"first_name":"John",
    "last_name":"Doe",
    "grade":0,
    "ID":1000,
    "subject":"Math",
    "teacher":"Quest, Jonny"},
    {"first_name":"John",
    "last_name":"Doe",
    "grade":0,
    "ID":1000,
    "subject":"Reading",
    "teacher":"Gordon, Flash"},
    {"first_name":"John",
    "last_name":"Doe",
    "grade":0,"ID":1000,
    "subject":"History",
    "teacher":"Banner, Bruce"},
    {"first_name":"John",
    "last_name":"Doe",
    "grade":0,
    "ID":1000,
    "subject":"Science",
    "teacher":"Kent, Clark"}
]

Desired Output


{"first_name":"John",
"last_name":"Doe",
"grade":0,
"ID":1000,
"Home Room":{"teacher":"Parker, Peter"},
"Math":{"teacher":"Quest, Jonny"},
"Reading":{"teacher":"Gordon, Flash"},
"History":{"teacher":"Banner, Bruce"},
"Science":{"teacher":"Kent, Clark"}
}


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source