I have the following documents stored in "Deployments" collection in MongoDB version 4.2.

I would like to achieve the following results group by productId and between a range of dates.

I have achieved execution times using this query.

    db.getCollection('Deployments').aggregate([
{
    $match : {$and:[{ "startedAt": { $gte: new ISODate("2021-10-01") } }, 
                   { "startedAt": { $lte: new ISODate("2021-11-17") } }]}
  },
  {
      $group : {
          _id:"$productId",
          count: { $sum: 1 },
          minExecutionTime:
                    {
                        
                       $min:
                          {
                             $divide:[{$subtract:["$completedAt", "$startedAt"]}, 1000 * 60]
                                
                           }
                    },
          maxExecutionTime:
                    {
                       $max:
                          {
                             $divide:[{$subtract:["$completedAt", "$startedAt"]}, 1000 * 60]
                                
                           }
                    },
          avgExecutionTime:
                    {
                       $avg:
                          {
                             $divide:[{$subtract:["$completedAt", "$startedAt"]}, 1000 * 60]
                                
                           }
                    }
          }
      
      }
  ])

1. Any help to add the counts to this query please?
2. How to truncate the execution times to 2 decimal places?
3. Please suggest in case of any optimizations to this query.

Documents:

[
  {
    "productId": 1,
    "deploymentStatus": "Succeeded",
    "startedAt": ISODate("2021-01-21T14:00:19.782Z"),
    "completedAt": ISODate("2021-01-21T14:03:55.789Z")
  },
  {
    "productId": 2,
    "deploymentStatus": "Failed",
    "startedAt": ISODate("2021-01-21T15:00:19.782Z"),
    "completedAt": ISODate("2021-01-21T15:03:55.789Z")
  },
  {
    "productId": 3,
    "deploymentStatus": "Cancelled",
    "startedAt": ISODate("2021-01-21T16:00:19.782Z"),
    "completedAt": ISODate("2021-01-21T16:03:55.789Z")
  },
  {
    "productId": 1,
    "deploymentStatus": "Failed",
    "startedAt": ISODate("2021-01-21T17:00:19.782Z"),
    "completedAt": ISODate("2021-01-21T17:03:55.789Z")
  },
  {
    "productId": 2,
    "deploymentStatus": "Failed",
    "startedAt": ISODate("2021-01-21T18:00:19.782Z"),
    "completedAt": ISODate("2021-01-21T18:03:55.789Z")
  },
  {
    "productId": 3,
    "deploymentStatus": "Succeeded",
    "startedAt": ISODate("2021-01-21T19:00:19.782Z"),
    "completedAt": ISODate("2021-01-21T19:03:55.789Z")
  },
  {
    "productId": 1,
    "deploymentStatus": "Cancelled",
    "startedAt": ISODate("2021-01-21T20:00:19.782Z"),
    "completedAt": ISODate("2021-01-21T20:03:55.789Z")
  },
  {
    "productId": 2,
    "deploymentStatus": "Failed",
    "startedAt": ISODate("2021-01-21T21:00:19.782Z"),
    "completedAt": ISODate("2021-01-21T21:03:55.789Z")
  },
  {
    "productId": 3,
    "deploymentStatus": "Succeeded",
    "startedAt": ISODate("2021-01-21T22:00:19.782Z"),
    "completedAt": ISODate("2021-01-21T22:03:55.789Z")
  }
]

Mongo Playground

CodePudding user response：

Your aggregation is actually on the right track. For your 3 questions:

Any help to add the counts to this query please?

Just break the count into 3 conditional count using $cond

How to truncate the execution times to 2 decimal places?

Use $round

Please suggest in case of any optimizations to this query. I did a minor tweak to pre-compute the duration in minute instead of computing them again in the $group stage

db.collection.aggregate([
  {
    $match: {
      $and: [
        {
          "startedAt": {
            $gte: ISODate("2021-01-21")
          }
        },
        {
          "startedAt": {
            $lte: ISODate("2021-01-22")
          }
        }
      ]
    }
  },
  {
    "$addFields": {
      "durationInMin": {
        $round: [
          {
            $divide: [
              {
                $subtract: [
                  "$completedAt",
                  "$startedAt"
                ]
              },
              60000
            ]
          },
          2
        ]
      }
    }
  },
  {
    $group: {
      _id: "$productId",
      SucceedCount: {
        $sum: {
          "$cond": {
            "if": {
              $eq: [
                "$deploymentStatus",
                "Succeeded"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      FailedCount: {
        $sum: {
          "$cond": {
            "if": {
              $eq: [
                "$deploymentStatus",
                "Failed"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      CancelledCount: {
        $sum: {
          "$cond": {
            "if": {
              $eq: [
                "$deploymentStatus",
                "Cancelled"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      minExecutionTime: {
        $min: "$durationInMin"
      },
      maxExecutionTime: {
        $max: "$durationInMin"
      },
      avgExecutionTime: {
        $avg: "$durationInMin"
      }
    }
  }
])

Here is the Mongo playground for your reference.