MongoDB map reduce - group and find top N

Admin 1
Viewed 70

An , and collection called test

Consider this test collection, in which an airport is identified by AirportID:

{ AirportID:"1001", delayMinutes :"15.0" },
{ AirportID:"1004", delayMinutes :"3.0" },
{ AirportID:"1001", delayMinutes :"20.0" },
{ AirportID:"1002", delayMinutes :"6.0" },
{ AirportID:"1002", delayMinutes :"25.0" },
{ AirportID:"1004", delayMinutes :"55.0" },

I want to group it together and list the top 2 from that list.

This is the query I tried:

db.test.aggregate([
  { $group: { _id: "$AirportID", delayMinutes: { $sum: 1 } } }, 
  { $sort: { delayMinutes: -1 } }
])

But it didn't work for me

mongodb-query mongodb
1 Answers

You're very close, you just need to do 2 additional things:

  1. Add a $limit stage at the end to only return 2 results
  2. Cast the delayMinutes to a number using $toDecimel or $toDouble, note that any invalid string value will make this stage fail.

Overall this is what it'll look like:

db.test.aggregate([
  {
    $group: {
      _id: "$AirportID",
      delayMinutes: {
        $sum: {
          "$toDouble": "$delayMinutes"
        }
      }
    }
  },
  {
    $sort: {
      delayMinutes: -1
    }
  },
  {
    $limit: 2
  }
])

Mongo Playground

user_107703700
Related