Let's say I have this data:

{"Plane":"5546","Time":"55.0", City:"LA"}
{"Plane":"5548","Time":"25.0", City:"CA"}
{"Plane":"5546","Time":"6.0", City:"LA"}
{"Plane":"5548","Time":"5.0", City:"CA"}
{"Plane":"5555","Time":"15.0", City:"XA"}
{"Plane":"5555","Time":"8.0", City:"XA"}

and more but I just visualize the data I want to calculate and group all the time and plane, this is expected output:

{"_id:":["5546","LA"],"Sum":2,"LateRate":1,"Prob"0.5}

The sum is sum all the time, Late is sum all the time with time > "15" and Prob is Late/Sum The code I have tried but it still is missing something:

db.Collection.aggregate([
    {
        $project: {
            Sum: 1,
            Late: {
                $cond: [{ $gt: ["$Time", 15.0] }, 1, 0]
            },
            prob:1
        }
    },
    {
        $group:{
            _id:{Plane:"$Plane", City:"$City"},
            Sum: {$sum:1},
            Late: {$sum: "$Late"}
        }
    },
    {
    $addFields: {
      prob: {
        "$divide": [
          "$Late",
          "$Sum"
        ]
      }
    }
  },
])

CodePudding user response：

db.collection.aggregate([
  {
    $project: {
      Time: 1,
      Late: {
        $cond: [
          {
            $gt: [
              {
                $toDouble: "$Time"
              },
              15.0
            ]
          },
          "$Time",
          0
        ]
      },
      prob: 1,
      Plane: 1,
      City: 1
    }
  },
  {
    $group: {
      _id: {
        Plane: "$Plane",
        City: "$City"
      },
      Sum: {
        $sum: {
          "$toDouble": "$Time"
        }
      },
      Late: {
        $sum: {
          $toDouble: "$Late"
        }
      }
    }
  },
  {
    $addFields: {
      prob: {
        "$divide": [
          "$Late",
          "$Sum"
        ]
      }
    }
  }
])

1. Project limits the fields passed to the next stage
2. On string, you cannot perform all relational/arithmetic operations

Playground