I’m trying to see how I can efficiently get a list of courses a student is registered for. All courses are stored in Cosmos DB using the Course object that looks like this:

{
   "id": "31dcf7e7-6864-4f4b-8a6b-0dfe78cbc7bc",
   "title": "Intro to Some Topic",
   "level": 100,
   "instructor": "Robert Green",
   "startDate": "2024-08-19T08:00:00Z",
   "endDate": "2024-12-10T09:30:00Z"
}

And registrations are stored in Cosmos DB using CourseRegistration object that looks like this:

{
   "id": "4a7dae56-af2e-4f66-b57c-80aa9dd8811f",
   "courseId": "31dcf7e7-6864-4f4b-8a6b-0dfe78cbc7bc",
   "studentId": "1e9b87c1-75f2-4d27-8989-cff3dcdf8fd9",
   "studentName": "John Doe"
}

How do efficiently get a list of courses a particular student is registered for? Making two calls is not very efficient i.e. first get a list of course Id’s the student is registered with and then get a list of courses and their details.

I want to get the following info:

• Course Id
• Course Name
• Instructor
• Start Date
• End Date

What approach can I use to get all I need in an efficient manner? BTW, I use the SQL API to query my Cosmos DB. Thanks.