mongoDB filter, sort, and rank result-CodePudding

I have a collection like this

{
 id: 1,
 category: "food",
 score: 20
}
{
 id: 2,
 category: "drink",
 score: 19
}
{
 id: 3,
 category: "food",
 score: 50
}
{
 id: 4,
 category: "food",
 score: 30
}

id is not unique btw. but it is unique in that category.

so it is possible to have

{id: 1, category: "food"}
{id: 1, category: "drink"}

but not

{id: 1, category: "food"}
{id: 1, category: "food"}

here's what I want to do

find all category == "food"
-> it will give id: 1, 3, 4
// I can add some other filter here before sort happen
// like id less than 100

sort them by score
-> it will give id: 3, 4, 1 // highest score must be the first entry

then what is the rank of id: [4, 1]
-> it should give me {id: 4, rank: 2}, {id: 1, rank: 3}

how can I achieve this? please give me some snippets or idea

CodePudding user response：

  db.collection.aggregate([
  {
    "$match": { //Filter conditions
      "category": "food"
    }
  },
  {
    "$sort": {//Sorting
      "score": -1
    }
  },
  {
    "$group": { //Group by null to get array index
      "_id": "null",
      "data": {
        "$push": "$$ROOT",
        
      }
    }
  },
  {
    "$unwind": { //Unwind and get index
      path: "$data",
      "includeArrayIndex": "index"
    }
  },
  {
    "$match": {
      "data.id": { //Filter require ids
        $in: [
          3,
          4
        ]
      }
    }
  }
])

Sample