4

For instance I have a collection:

{ "_id" : 1, "name" : "abc", "score" : 10 }
{ "_id" : 2, "name" : "abc", "score" : 15 }
{ "_id" : 3, "name" : "abc", "score" : 20 }
{ "_id" : 4, "name" : "xyz", "score" : 10 }
{ "_id" : 5, "name" : "xyz", "score" : 15 }
{ "_id" : 6, "name" : "xyz", "score" : 20 }

How can I do a query in Mongodb to group by name then sort by score and take it with limit=2. I want to get like this:

    {"_id": "abc", "items": [
          { "_id" : 3, "name" : "abc", "score" : 20 },
          { "_id" : 2, "name" : "abc", "score" : 15 }]
    }
    {"_id": "xyz", "items": [
          { "_id" : 6, "name" : "xyz", "score" : 20 },
          { "_id" : 5, "name" : "xyz", "score" : 15 }]
    }
anton
  • 675
  • 6
  • 16

1 Answers1

11

My solution is 

db.collection.aggregate([
    {$sort:{name:-1, score:-1}},
    {$group:{_id:"$name",items:{$push:{score:"$score"}}}}, 
    {$project:{items:{$slice:["$items", 2]}}}])
.pretty()

returns 

{
    "_id" : "abc",
    "items" : [
        {
            "score" : 20
        },
        {
            "score" : 15
        }
    ]
}
{
    "_id" : "xyz",
    "items" : [
        {
            "score" : 20
        },
        {
            "score" : 15
        }
    ]
}
anton
  • 675
  • 6
  • 16
  • 3
    Mongodb does not guarantee that previous sorts are preserved by a group stage, on a bigger dataset or on sharded clusters it would mess up the order. Afaik there is no way to do this – Angivare Oct 10 '19 at 14:22
  • I am exactly stuck in this phase now, my group by is returning random result set every time – abdul rashid Jun 08 '21 at 20:36