CODEWITHSUNDEEP
mongodbaggregation-frameworkspring-mongodb
venkat.s
venkat.s

Reputation: 140

MongoDB aggregate multiple group by top fields and array fields

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.

Upvotes: 0

Views: 265

Answers (1)

s7vr
s7vr

Reputation: 75934

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

Upvotes: 1

Related Questions