I am trying to get count of emails on daily bases, on condition of email status Send and Failed.

db.emails.aggregate([
   {
      "$group":{
         "_id":{
            "$dateToString":{
               "format":"%Y-%m-%d",
               "date":"$createdAt"
            },
            {
               "status":"$sEmailStatus"
            }
         }
      }
   }
])

CodePudding user response：

I think you are looking for this query:

Your _id into $group is bad formatted so you can try this way (you were almost there).

The trick here is to group by date parsing to string and getting only the day (i.e. group by the day) and then using $sum with $cond. If the status is "Send" is added 1 into sent. If it is "Failed" then is added into failed.

And last a $project stage to get the day into field date and not inside _id. By the way this is optional.

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "id": {
          "$dateToString": {
            "date": "$createdAt",
            "format": "%Y-%m-%d"
          }
        }
      },
      "sent": {
        "$sum": {
          "$cond": {
            "if": {
              "$eq": [
                "$sEmailStatus",
                "Send"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      "failed": {
        "$sum": {
          "$cond": {
            "if": {
              "$eq": [
                "$sEmailStatus",
                "Failed"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "date": "$_id.id",
      "sent": 1,
      "failed": 1
    }
  }
])

Example here

Also another approach is to using $size and $filter. But filter twice the array I assume is worst than sum into group directly.