These are my tables:
Students

Terms

Course

I have tried using different combination of JSON_OBJECT, JSON_ARRAY, JOIN, GROUP BY and unable to produce the result I want.
I want my result to be a JSON_OBJECT that looks like this with nested structure base on studentID, year, term, course:

{
  "studentResults": [
    {"xk341": {
        "name": "XeraKay",
        "years": [
          {"2019": [
              {"1": {
                  "startDate": "2019-09-01",
                  "endDate": "2019-12-24",
                  "courses": [
                    {"courseCode": "198:033:111",
                      "grade": "B"
                    },
                    {"courseCode": "198:033:124",
                      "grade": "A"
                    }
                  ]}
              },
              {"2": {
                  "startDate": "2019-01-01",
                  "endDate": "2019-03-05",
                  "courses": [
                    {"courseCode": "350:043:323",
                      "grade": "W"
                    }
                  ]}
              }
            ]},
          {"2022": [
              {"4": {
                  "startDate": "2022-05-01",
                  "endDate": "2022-08-28",
                  "courses": [
                    {"courseCode": "750:452:256",
                      "grade": "B+"
                    }
                  ]
                }}
            ]}
        ]}
    },
    {"kj403": {
        "name": "KevinJo",
        "years": [
          {"2022": [
              {"4": {
                  "startDate": "2022-05-01",
                  "endDate": "2022-08-28",
                  "courses": [
                    {"courseCode": "750:452:256",
                      "grade": "C"
                    }
                  ]}}
            ]}
        ]}
    }]
}

I have tried a lot of things,
closest I have gotten to is with:

SELECT DISTINCT JSON_OBJECT(
t.year, JSON_ARRAY(
JSON_OBJECT(t.term, JSON_ARRAY( (select DISTINCT GROUP_CONCAT(
                    JSON_OBJECT(
                    'courseCode',c.courseCode
                    ))
                    FROM Courses c WHERE c.term = t.term and c.studentID = t.studentID)) )
)            )       
from Terms t, Courses c WHERE t.term = c.term AND t.studentID = c.studentID AND t.studentID in ('xk341','kj403')
GROUP BY t.term
;

This is the result I got running it against different data, the terms 2,3 aren’t joined together in the same 2014 year

{
"2014": [
{
"2": [
"{"courseCode": "16:185:419"},{ "courseCode": "11:175:543"},{"courseCode": "21:940:365"},{ "courseCode": "16:198:501"}"
]
}
]
}
{
"2014": [
{
"3": [
"{"courseCode": "19:165:611"},{"courseCode": "03:709:257"},{"courseCode": "04:486:627"},{ "courseCode": "09:790:671"},{"courseCode": "09:920:272"}"
]
}
]
}