mongodb aggregate multiple arrays

Question

I am using MongoDB version v3.4. I have a documents collection and sample datas are like this:

{
    "mlVoters" : [ 
        {"email" : "a@b.com", "isApproved" : false}
    ],
    "egVoters" : [ 
        {"email" : "a@b.com", "isApproved" : false}, 
        {"email" : "c@d.com", "isApproved" : true}
    ]
},{
    "mlVoters" : [ 
        {"email" : "a@b.com", "isApproved" : false}, 
        {"email" : "e@f.com", "isApproved" : true}
    ],
    "egVoters" : [ 
        {"email" : "e@f.com", "isApproved" : true}
    ]
}

Now if i want the count of distinct email addresses for mlVoters:

db.documents.aggregate([
  {$project: { mlVoters: 1 } },
  {$unwind: "$mlVoters" },
  {$group: { _id: "$mlVoters.email", mlCount: { $sum: 1 } }},
  {$project: { _id: 0, email: "$_id", mlCount: 1 } },
  {$sort: { mlCount: -1 } }
])

Result of the query is:

{"mlCount" : 2.0,"email" : "a@b.com"}
{"mlCount" : 1.0,"email" : "e@f.com"}

And if i want the count of distinct email addresses for egVoters i do the same for egVoters field. And the result of that query would be:

{"egCount" : 1.0,"email" : "a@b.com"}
{"egCount" : 1.0,"email" : "c@d.com"}
{"egCount" : 1.0,"email" : "e@f.com"}

So, I want to combine these two aggregation and get the result as following (sorted by totalCount):

{"email" : "a@b.com", "mlCount" : 2, "egCount" : 1, "totalCount":3}
{"email" : "e@f.com", "mlCount" : 1, "egCount" : 1, "totalCount":2}
{"email" : "c@d.com", "mlCount" : 0, "egCount" : 1, "totalCount":1}

How can I do this? How should the query be like? Thanks.

Answer 1

First you add a field voteType in each vote. This field indicates its type. Having this field, you don't need to keep the votes in two separate arrays mlVoters and egVoters; you can instead concatenate those arrays into a single array per document, and unwind afterwards.

At this point you have one document per vote, with a field that indicates which type it is. Now you simply need to group by email and, in the group stage, perform two conditional sums to count how many votes of each type there are for every email.

Finally you add a field totalCount as the sum of the other two counts.

db.documents.aggregate([
  {
    $addFields: {
      mlVoters: {
        $ifNull: [ "$mlVoters", []]
      },
      egVoters: {
        $ifNull: [ "$egVoters", []]
      }
    }
  },
  {
    $addFields: {
      "mlVoters.voteType": "ml",
      "egVoters.voteType": "eg"
    }
  },
  {
    $project: {
      voters: { $concatArrays: ["$mlVoters", "$egVoters"] }
    }
  },
  {
    $unwind: "$voters"
  },
  {
    $project: {
      email: "$voters.email",
      voteType: "$voters.voteType"
    }
  },
  {
    $group: {
      _id: "$email",
      mlCount: {
        $sum: {
          $cond: {
            "if": { $eq: ["$voteType", "ml"] },
            "then": 1,
            "else": 0
          }
        }
      },
      egCount: {
        $sum: {
          $cond: {
            "if": { $eq: ["$voteType", "eg"] },
            "then": 1,
            "else": 0
          }
        }
      }
    }
  },
  {
    $addFields: {
      totalCount: {
        $sum: ["$mlCount", "$egCount"]
      }
    }
  }
])