mongodbmql

Mongo MQL group by date and add counts of other field values


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


Solution

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