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"}}
])
$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"
}
}
]);