CODEWITHSUNDEEP
mongodbmql
miff2000
miff2000

Reputation: 61

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

Upvotes: 1

Views: 309

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22296

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

Upvotes: 1

Related Questions