DB join query with student and department where a student can be enlisted with multiple departments where each department can have multiple teachers. After query getting the following output
student_id student_name department_id department_name teacher_id
1 S1 d1 d1_name t1
1 S1 d1 d1_name t2
1 S1 d1 d1_name t3
1 S1 d2 d2_name t4
1 S1 d2 d2_name t5
1 S1 d2 d2_name t6
2 S2 d2 d2_name t7
2 S2 d1 d1_name t1
2 S2 d1 d1_name t2
2 S2 d1 d1_name t3
2 S2 d3 d3_name t8
2 S2 d3 d3_name t9
2 S2 d3 d3_name t10
2 S2 d3 d3_name t11
Need the following output
{
{
"student_id": 1,
"student_name": "s1_name",
"departments": [
{
"id": "d1",
"name": "d1_name",
"teacher_id": [
"t1",
"t2",
"t3"
]
},
{
"id": "d2",
"name": "d2_name",
"teacher_id": [
"t4",
"t5",
"t6"
]
}
]
},
{
"student_id": 2
"student_name": "s2_name",
"departments": [
{
"id": "d1",
"name": "d1_name",
"teacher_id": [
"t1",
"t2",
"t3"
]
},
{
"id": "d3",
"name": "d3_name",
"teacher_id": [
"t7",
"t8",
"t9"
]
}
]
}
}
Could you define the aggregate function