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" : "[email protected]", "isApproved" : false}
    ],
    "egVoters" : [ 
        {"email" : "[email protected]", "isApproved" : false}, 
        {"email" : "[email protected]", "isApproved" : true}
    ]
},{
    "mlVoters" : [ 
        {"email" : "[email protected]", "isApproved" : false}, 
        {"email" : "[email protected]", "isApproved" : true}
    ],
    "egVoters" : [ 
        {"email" : "[email protected]", "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" : "[email protected]"}
{"mlCount" : 1.0,"email" : "[email protected]"}

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" : "[email protected]"}
{"egCount" : 1.0,"email" : "[email protected]"}
{"egCount" : 1.0,"email" : "[email protected]"}

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

{"email" : "[email protected]", "mlCount" : 2, "egCount" : 1, "totalCount":3}
{"email" : "[email protected]", "mlCount" : 1, "egCount" : 1, "totalCount":2}
{"email" : "[email protected]", "mlCount" : 0, "egCount" : 1, "totalCount":1}

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

abl · Accepted Answer · 2018-10-25 00:03:20Z

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

Your answer looks quite reasonable but it is giving me "errmsg" : "$concatArrays only supports arrays, not object". Is that because instead of being simple array they are array of objects?
Do you have any document in the collection which does not have both a egVoters and mlVoters arrays, even if empty? That would explain this error, because in that case $addFields: { "mlVoters.voteType": "ml" } would create a field like "mlVoters" : { "voteType" : "ml" }. You can solve that by creating the (empty) arrays first if they don't exist.
Yes i have. How can i create empty arrays if they don't exist?

Collectives™ on Stack Overflow

mongodb aggregate multiple arrays

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related