How to get X students from each grade in MongoDB?-CodePudding

I have a mongoDB collection named students with the fields grade and name.

I would like to create a query that will get up to 2 students from each grade inside my gradesList.

So this is my input:

const gradesList = [10, 11, 12]

And this is my desired output:

[
    {
        name: 'Albert',
        grade: 10
    },
    {
        name: 'Tim',
        grade: 10
    },
    {
        name: 'Monika',
        grade: 11
    },
    {
        name: 'Mike',
        grade: 11
    },
    {
        name: 'Rich',
        grade: 12
    },
    {
        name: 'Beth',
        grade: 12
    },
]

I am assuming I need to use the aggregate framework but I have no idea how to accomplish what I am trying to do.

One solution is to iterate over the gradesList array and execute a query like this: db.students.find({ grade: 10 }).limit(2). However this is a really bad idea.

Is there an efficient way of accomplishing this in 1 query?

CodePudding user response：

Here's one way to do it.

db.collection.aggregate([
  {
    "$match": {
      "grade": {"$in": [10, 11, 12]}
    }
  },
  {
    "$group": {
      "_id": "$grade",
      "name": {
        "$firstN": {
          "n": 2,
          "input": "$name"
        }
      }
    }
  },
  {"$unwind": "$name"},
  {
    "$project": {
      "_id": 0,
      "name": 1,
      "grade": "$_id"
    }
  },
  {
    "$sort": {
      "grade": 1,
      "name": 1
    }
  }
])

Sample output:

[
  {
    "grade": 10,
    "name": "Jayne Cormier"
  },
  {
    "grade": 10,
    "name": "Rebekah Jacobi"
  },
  {
    "grade": 11,
    "name": "Mariano Reinger"
  },
  {
    "grade": 11,
    "name": "Shea Hartmann"
  },
  {
    "grade": 12,
    "name": "Colt Spinka"
  },
  {
    "grade": 12,
    "name": "Stephanie Schiller"
  }
]

Try it on mongoplayground.net.