I'm struggling to understand how to query my data using MQL. My dataset looks a bit like this:
{
"_id": {
"$oid": "5dcadda84d59f2e0b0d56974"
},
"object_kind": "pipeline",
"object_attributes": {
"status": "success",
"created_at": "2019-11-12 16:28:22 UTC",
"variables": []
}
},
{
"_id": {
"$oid": "5dcadda84d59f2e0b0d56998"
},
"object_kind": "pipeline",
"object_attributes": {
"status": "failed",
"created_at": "2019-11-13 12:22:22 UTC",
"variables": []
}
}
I'm adding $eventDate
using this in my aggregation, which works:
{
eventDate: { $dateFromString: {
dateString: {
$substr: [ "$object_attributes.created_at",0, 10 ]
}
}},
}
And I'm trying to turn it into this:
{
"eventDate": "2019-11-12",
"counts": {
"success": 1,
"failure": 0
}
},
{
"eventDate": "2019-11-13",
"counts": {
"success": 0,
"failure": 1
}
},
So far I can't seem to understand how to group the data twice, as if I group by "$eventDate" then I can't then group by status. Why can't I just group all docs from the same $eventDate
into an array, without losing all the other fields?
It would be ideal if the success
and failure
fields which could be inferred from different statuses that appear in object_attributes.status
This can be done in several different ways, heres a quick example using a conditional sum:
db.collection.aggregate([
{
"$addFields": {
"eventDate": {
"$dateFromString": {
"dateString": {
"$substr": [
"$object_attributes.created_at",
0.0,
10.0
]
}
}
}
}
},
{
"$group": {
"_id": "$eventDate",
"success": {
"$sum": {
"$cond": [
{
"$eq": [
"$object_attributes.status",
"success"
]
},
1.0,
0.0
]
}
},
"failure": {
"$sum": {
"$cond": [
{
"$eq": [
"$object_attributes.status",
"failed"
]
},
1.0,
0.0
]
}
}
}
},
{
"$project": {
"eventDate": "$_id",
"counts": {
"success": "$success",
"failure": "$failure"
},
"_id": 0
}
}
]);