I have a dataset like this and bus is the name of the collection.

{ BusID:"1001", delayMinutes :"15.0", City:"LA" },
{ BusID:"1004", delayMinutes :"3.0", City:"PA" },
{ BusID:"1001", delayMinutes :"20.0", City:"LA" },
{ BusID:"1002", delayMinutes :"6.0", City:"CA" },
{ BusID:"1002", delayMinutes :"25.0", City:"CA" },
{ BusID:"1004", delayMinutes :"55.0", City:"PA" },
{ BusID:"1003", delayMinutes :"55.0", City:"KA" },
{ BusID:"1003", delayMinutes :"5.0", City:"KA" },

I tried to group like this format but it didn't work for me

{"_id":["1003","KA"], "A":"2","B":"1",C:"1"}

With A: total number of buses, B: late bus arrival with delayMinutes gt "10.0", C: the ratio of A/B and display must be descending. My code I have tried:

db.bus.aggregate([
    {
        $project: {
            A: 1,
            B: {
                $cond: [{ $gt: ["$delayMinutes", "10.0"] }, 1, 0]
            }
        }
    },
    
    {
        $group:{
            _id:{BusID:"$BusID", City:"$City"},
            A: {$sum:1},
            B: {$sum: "$B"}
        }
    }
])

CodePudding user response：

Playground

db.collection.aggregate([
  {
    $addFields: { //Projection limits the fields to be passed to the next stage
      A: 1,
      B: {
        $cond: [
          {
            $gt: [
              {
                $toDouble: "$delayMinutes" //You need conversion as you have string
              },
              10.0
            ]
          },
          1,
          0
        ]
      }
    }
  },
  {
    $group: {
      _id: {
        BusID: "$BusID",
        City: "$City"
      },
      A: {
        $sum: 1
      },
      B: {
        $sum: "$B"
      }
    }
  },
  {
    $match: {
      $expr: {
        "$gt": [ //You need this to skip divideByZero error
          "$B",
          0
        ]
      }
    }
  },
  {
    "$addFields": {
      "C": {
        "$divide": [
          "$A",
          "$B"
        ]
      }
    }
  }
])

Additionally, you can use $sort to get the desired order.

To transform ID to an array,

{
    "$project": {
      A: 1,
      B: 1,
      C: 1,
      _id: [
        "$_id.BusID",
        "$_id.City"
      ]
    }
  }

Sample