Aggregate the data using two fields and then display the aggregated data by latest run date in mongoDB

Question

I am trying to find the failed and passed test cases for each asset class by the latest run date. Below is the sample documents in the collection.

{
"failed": 2,
"passed": 4,
"asset_class": "A",
"run_date: Date("2024-08-28")
},
{
"failed": 1,
"passed": 3,
"asset_class": "A",
"run_date: Date("2024-08-29")
},
{
"failed": 5,
"passed": 1,
"asset_class": "A",
"run_date: Date("2024-08-29")
},
{
"failed": 2,
"passed": 4,
"asset_class": "B",
"run_date: Date("2024-08-22")
},
{
"failed": 5,
"passed": 8,
"asset_class": "B",
"run_date: Date("2024-08-22")
},
{
"failed": 5,
"passed": 1,
"asset_class": "B",
"run_date: Date("2024-08-26")
}

I am expecting the output as below.

{
"failed": 6,
"passed": 4,
"asset_class": "A",
"run_date: Date("2024-08-29")
},
{
"failed": 5,
"passed": 1,
"asset_class": "B",
"run_date: Date("2024-08-26")
}

I have tried the below query, **It works but output document doesn't have proper format and I'm more concern about the performance. ** is there any other way to write this query more efficiently?

Note: I have compound index like { asset_class:1, run_date:1 }

db.getCollection("test").aggregate([

{ $group: {
    _id: {asset_class: "$asset_class", run_date: "$run_date"},
    passed: {$sum: "$passed"},
    failed: {$sum: "$failed"}
    run_date: {
      $push: "$run_date"
    },
    asset_class: {
      $push: "$asset_class"
   }
  }
},

{$sort: {asset_class:1, run_date:1}},

 {$group: {
 _id: "$_id.asset_class",
  "result": {$last: "$$ROOT"}
 }
},
{$replaceRoot: {newRoot: "$result"}}
])

Answer 1

$project can be used to format the output in desired format.Altername way to write the query to achieve desired results Working example:

db.collection.aggregate([
  {
    $group: {
      _id: { asset_class: "$asset_class", run_date: "$run_date" },
      totalFailed: { $sum: "$failed" },
      totalPassed: { $sum: "$passed" }
    }
  },
  {
    $sort: { "_id.asset_class": 1, "_id.run_date": -1 }
  },
  {
    $group: {
      _id: "$_id.asset_class",
      latestRunDate: { $first: "$_id.run_date" },
      totalFailed: { $first: "$totalFailed" },
      totalPassed: { $first: "$totalPassed" }
    }
  },
  {
    $project: {
      _id: 0,
      asset_class: "$_id",
      run_date: "$latestRunDate",
      failed: "$totalFailed",
      passed: "$totalPassed"
    }
  }
]);