MongoDB aggregate multiple group by top fields and array fields

Question

My collection will look like this,

{
"_id" : ObjectId("591c5971240033283736860a"),
"status" : "Done",
"createdDate" : ISODate("2017-05-17T14:09:20.653Z")
"communications" : [ 
    {
        "communicationUUID" : "df07948e-4a14-468e-beb1-db55ff72b215",
        "communicationType" : "CALL",
        "recipientId" : 12345,
        "createdDate" : ISODate("2017-05-18T14:09:20.653Z")
        "callResponse" : {
            "Status" : "completed",
            "id" : "dsd45554545ds92a9bd2c12e0e6436d",
        }
    }
]}
{
"_id" : ObjectId("45sdsd59124003345121450a"),
"status" : "ToDo",
"createdDate" : ISODate("2017-05-17T14:09:20.653Z")
"communications" : [ 
    {
        "communicationUUID" : "45sds55-4a14-468e-beb1-db55ff72b215",
        "communicationType" : "CALL",
        "recipientId" : 1234,
        "createdDate" : ISODate("2017-05-18T14:09:20.653Z")
        "callResponse" : {
            "Status" : "completed",
            "id" : "84fe862f1924455dsds5556436d",
        }
    }
]}

Currently I am writing two aggregate query to achieve my requirement and my query will be below

db.collection.aggregate(
{ $project: {
      dayMonthYear: { $dateToString: { format: "%d/%m/%Y", date: "$createdDate" } },
      status: 1,
}},
{ $group: { 
    _id: "$dayMonthYear",
    Pending: { $sum: { $cond :  [{ $eq : ["$status", "ToDo"]}, 1, 0]} },
    InProgress: { $sum: { $cond :  [{ $eq : ["$status", "InProgress"]}, 1, 0]} },
    Done: { $sum: { $cond : [{ $eq : ["$status", "Done"]}, 1, 0]} },
    Total: { $sum: 1 }
}}

My output will be,

{"_id" : "17/05/2017", "Pending" : 1.0, "InProgress" : 0.0, "Done" : 1.0, "Total" : 2.0 }

Using above query I can able to get count but I need to find the count based on communication Status too so I am writing one more query to achieve,

db.collection.aggregate(
{"$unwind":"$communications"},
{ $project: {
    dayMonthYear: { $dateToString: { format: "%d/%m/%Y", date: "$createdDate" } },
    communications: 1
}},
{ "$group": { 
    _id: "$dayMonthYear",
    "total_call": { $sum: { $cond :  [{ $or : [ { $eq: [ "$communications.callResponse.Status", "failed"] },
                                                { $eq: [ "$communications.callResponse.Status", "busy"] },
                                                { $eq: [ "$communications.callResponse.Status", "completed"] },
                                                { $eq: [ "$communications.callResponse.Status", "no-answer"] }
                                      ]}, 1, 0 ] }},
    "engaged": { $addToSet: { $cond :  [{ $eq : ["$communications.callResponse.Status", "completed"]}, 
                                                "$communications.recipientId", "null" ]} },
    "not_engaged": { $addToSet: { $cond: [{ $or : [ { $eq: [ "$communications.callResponse.Status", "failed"] },
                                                             { $eq: [ "$communications.callResponse.Status", "busy"] },
                                                             { $eq: [ "$communications.callResponse.Status", "no-answer"] } ]}, 
                                                "$communications.recipientId", "null" ] }}
}},
{ "$project": {
    "_id": 1,
    "total_call": 1,
    "engaged": { "$setDifference": [ "$ngaged", ["null"] ] },
    "not_engaged": { "$setDifference": [ "$not_engaged", ["null"] ] },
}},
{ "$project": {
    "total_call": 1,
    "engaged": { "$size": "$engaged" },
    "not_engaged": { "$size": { "$setDifference": [ "$not_engaged", "$engaged" ] }},
}})

My output will be,

{"_id" : "18/05/2017", "total_call" : 2.0, "engaged" : 2, "not_engaged" : 0}

Using above query I can able to get count but I want to achieve it in single query

I am looking for output like

{"_id":"17/05/2017", "Pending" : 1.0, "InProgress" : 0.0, "Done" : 1.0, "total_call" : 0, "engaged" : 0, "not_engaged" : 0}
{"_id":"18/05/2017", "Pending" : 0.0, "InProgress" : 0.0, "Done" : 0.0, "total_call" : 2, "engaged" : 2, "not_engaged" : 0}

Can anyone suggest or provide me good way to get above result.

s7vr · Accepted Answer · 2017-07-10 11:37:50Z

You can use $concatArrays to merge the status& createdDate documents followed by $group to count the occurrences.

db.collection.aggregate([
  {
    "$project": {
      "statusandcreateddate": {
        "$concatArrays": [
          [
            {
              "status": "$status",
              "createdDate": "$createdDate"
            }
          ],
          {
            "$map": {
              "input": "$communications",
              "as": "l",
              "in": {
                "status": "$$l.callResponse.Status",
                "createdDate": "$$l.createdDate"
              }
            }
          }
        ]
      }
    }
  },
  {
    "$unwind": "$statusandcreateddate"
  },
  {
    "$group": {
      "_id": {
        "$dateToString": {
          "format": "%d/%m/%Y",
          "date": "$statusandcreateddate.createdDate"
        }
      },
      "total_call": {
        "$sum": {
          "$cond": [
            {
              "$or": [
                {
                  "$eq": [
                    "$statusandcreateddate.status",
                    "failed"
                  ]
                },
                {
                  "$eq": [
                    "$statusandcreateddate.status",
                    "busy"
                  ]
                },
                {
                  "$eq": [
                    "$statusandcreateddate.status",
                    "completed"
                  ]
                },
                {
                  "$eq": [
                    "$statusandcreateddate.status",
                    "no-answer"
                  ]
                }
              ]
            },
            1,
            0
          ]
        }
      },
      "engaged": {
        "$sum": {
          "$cond": [
            {
              "$eq": [
                "$statusandcreateddate.status",
                "completed"
              ]
            },
            1,
            0
          ]
        }
      },
      "not_engaged": {
        "$sum": {
          "$cond": [
            {
              "$or": [
                {
                  "$eq": [
                    "$statusandcreateddate.status",
                    "failed"
                  ]
                },
                {
                  "$eq": [
                    "$statusandcreateddate.status",
                    "busy"
                  ]
                },
                {
                  "$eq": [
                    "$statusandcreateddate.status",
                    "no-answer"
                  ]
                }
              ]
            },
            1,
            0
          ]
        }
      },
      "Pending": {
        "$sum": {
          "$cond": [
            {
              "$eq": [
                "$statusandcreateddate.status",
                "ToDo"
              ]
            },
            1,
            0
          ]
        }
      },
      "InProgress": {
        "$sum": {
          "$cond": [
            {
              "$eq": [
                "$statusandcreateddate.status",
                "InProgress"
              ]
            },
            1,
            0
          ]
        }
      },
      "Done": {
        "$sum": {
          "$cond": [
            {
              "$eq": [
                "$statusandcreateddate.status",
                "Done"
              ]
            },
            1,
            0
          ]
        }
      }
    }
  }
])

Thanks for your suggestion. I made small mistake on my output. Actually second aggregation will look into communication object "createdDate" not the ROOT one
Its works like charming, Thanks. While using concatArrays can we add separate criteria for top level status and communication level status
Np. You should add all the criteria before the project stage, so it can use index. Please feel free to create a separate question.

Collectives™ on Stack Overflow

MongoDB aggregate multiple group by top fields and array fields

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related