How to sort a populated two level deep subdocuments array with mongoose

Question

I'm developping an api with mongoose and nodejs.

Basically there is a Course Model, Student Model and Group Model. For every course there is two fields in course document:

. Field "students": array of objectids referencing Student Model.

. Field "groups": array of subdocuments, each subdocument has a "groupId" field referencing Group Model and a "students" field: an array of objectids referencing Student Model. These are the students enrolled in the group by course.

I can get the sorted student list by course, but I can't get this list by group. I can get the list unsorted, but not sorted.

These are my main schemas:

Group Model -> groups collection:

const groupSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    unique: true
  }
})

Student Model -> students collection:

const studentSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    index:true
  },
  firstName: {
    type: String,
    required: true,
    index:true
  },
  lastName: {
    type: String,
    required: true,
    index:true
  }
})

Course Model -> courses collection:

const courseSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    index: true
  },
  students: [
    {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'Student'
    }
  ],
  groups: [
    {
      groupId: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'Group'
      },
      students: [
        {
          type: mongoose.Schema.Types.ObjectId,
          ref: 'Student'    
        }
      ],
    }
  ]
})

Using next query I get an alphabetically sorted student list by course, even I can filter with "where" without problems:

const query = await Course.findById(courseId)
    .select('students')
    .populate({
      path: 'students',
      select: ['_id', 'firstName', 'lastName', 'name'],
      match: where,
      options: {
        sort: {
          firstName: 1,
          lastName: 1,
          name: 1
        }
      }
    })

This query is fine,but I want another query to return the ordered student list by group and course. I'm using a similar query, but with a nested subdocuments array:

  const query = await Course.findById(courseId)
    .where('groups.groupId').equals(groupId)
    .select('groups.$.students')
    .populate({
      path: 'groups.students',
      select: ['_id', 'firstName', 'lastName', 'name'],
      match: where,
      // options: {
      //   sort: {
      //     firstName: 1,
      //     lastName: 1,
      //     name: 1
      //   }
      // }
    })

This way I get the student list, but unsorted. If I uncomment the options to sort the populated list, I get the error:

"Cannot populate with sort on path groups.students because it is a subproperty of a document array".

I'm newbie in mongodb, before I worked with sql databases, but I'm learning mongodb with mongoose. I don't know if my schema design is the best to get what I want.

As this is not working, to fix the problem I apply the "sort" method to the query to sort, like this:

  query.groups[0].students.sort((st1, st2) => {
    if (st1.firstName > st2.firstName) return 1
    if (st1.lastName > st2.lastName) return 1
    if (st1.name > st2.name) return 1
    return -1
  })

Is there a way to get this using the query and/or populate methods?

Following the expert advices about mongodb aggregation framework I find out my first approach to solve my problem. The aggregate I've used:

db.courses.aggregate([
    { $match: { _id: ObjectId("5c6d43c98068bc0836a62b65") }},
    { $project: {
        _id: 0,
        groups: '$groups'
        }
    },
    { $unwind: "$groups" },
    { $match: { 'groups.groupId': ObjectId("5c94b0d81ce16357d74549dd") }},
    { $project: {
        students: '$groups.students'
    }},
    { $unwind: '$students' },
    { $lookup: {
        from: "students",
        localField: "students",
        foreignField: "_id",
        as: "students"
    }},
    { $project: {
        id: { $arrayElemAt: [ "$students._id", 0 ] },
        nie: { $arrayElemAt: [ "$students.nie", 0 ] },
        name: { $arrayElemAt: [ "$students.name", 0 ] },
        firstName: { $arrayElemAt: [ "$students.firstName", 0 ] },
        lastName: { $arrayElemAt: [ "$students.lastName", 0 ] }
    }},
    { $sort: { firstName: 1, lastName: 1, name: 1 } }
])