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 with the condition that $delayMinutes is greater than "10.0". I want to group it together and list the top 2 from that list. Code I have tried

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

CodePudding user response：

As the requirement is clarified,

You need to filter the document first with delayMinutes field greater than ($gt) 10.

Make sure you convert the delayMinutes to double before comparing.

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

Sample Mongo Playground

And you can update the delayMinutes field to double with the $set stage. As it is redundant in the $match and $group stages.

db.collection.aggregate([
  {
    $set: {
      delayMinutes: {
        "$toDouble": "$delayMinutes"
      }
    }
  },
  {
    $match: {
      $expr: {
        $gt: [
          "$delayMinutes",
          10
        ]
      }
    }
  },
  {
    $group: {
      _id: "$AirportID",
      delayMinutes: {
        $sum: "$delayMinutes"
      }
    }
  },
  {
    $sort: {
      delayMinutes: -1
    }
  },
  {
    $limit: 2
  }
])

Sample Mongo Playground (with $set)