Azure Cosmos DB (SQL API)

Author: Shekhar

Originally Sourced from: https://stackoverflow.com/questions/73243507/azure-cosmos-db-sql-api

I have this dummy student information.There are multiple documents as JSON in Items. The JSON object has a "type" which can be either a teacher or a student. I need to find only student data.

A student has id, StudentName and an enrolled list of courses.

{
"type" : "student",
"id" : "123",
"StudentName" : {
  "firstName" : "Mila",
  "lastName" : "Kunis"
},
"Courses": [
{
"CourseId" : "CE1",
"CourseName" : "Computer Applications"
},
{
"CourseId" : "CE2",
"CourseName" : "Computer Architecture"
}
]
}

{
"type" : "student",
"id" : "222",
"StudentName" : {
  "firstName" : "Aston",
  "lastName" : "Kutcher"
},
"Courses": [
{
"CourseId" : "CE1",
"CourseName" : "Computer Applications"
},
{
"CourseId" : "CE3",
"CourseName" : "Computer Genius"
}
]
}

{
"type" : "student",
"id" : "321",
"StudentName" : {
  "firstName" : "Justin",
  "lastName" : "Timberlake"
},
"Courses": [
{
"CourseId" : "CE2",
"CourseName" : "Computer Architecture"
},
{
"CourseId" : "CE3",
"CourseName" : "Computer Genius"
}
]
}

{
"type" : "teacher",
"id" : "444",
"StudentName" : {
  "firstName" : "Tom",
  "lastName" : "Cruise"
},
"Courses": [
{
"CourseId" : "CE2",
"CourseName" : "Computer Architecture"
},
{
"CourseId" : "CE3",
"CourseName" : "Computer Genius"
}
]
}

I need to find each "CourseName" which is taken by multiple students (only type student) Courses should be distinct. Each student might have more than two courses.

So it should look like

{
  "CourseName" : "Computer Applications",
  "Students" : {
     "Mila Kunis",
     "Aston Kutcher"
   }
},
{
"CourseName" : "Computer Architecture",
  "Students" : {
     "Aston Kutcher",
     "Justin Timberlake"
   }
}

I need to write an sql query.