mongodb聚合多个数组 [英] mongodb aggregate multiple arrays

问题描述

我正在使用MongoDB v3.4版本.我有一个documents集合，示例数据如下:

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

{
    "mlVoters" : [ 
        {"email" : "[e-mail]", "isApproved" : false}
    ],
    "egVoters" : [ 
        {"email" : "[e-mail]", "isApproved" : false}, 
        {"email" : "[e-mail]", "isApproved" : true}
    ]
},{
    "mlVoters" : [ 
        {"email" : "[e-mail]", "isApproved" : false}, 
        {"email" : "[e-mail]", "isApproved" : true}
    ],
    "egVoters" : [ 
        {"email" : "[e-mail]", "isApproved" : true}
    ]
}

现在，如果我想为mlVoters计算不同的电子邮件地址:

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

查询结果为:

{"mlCount" : 2.0,"email" : "[e-mail]"}
{"mlCount" : 1.0,"email" : "[e-mail]"}

如果我想计算egVoters的不同电子邮件地址，我对egVoters字段也做同样的事情.该查询的结果将是:

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

因此，我想将这两个聚合结合起来并得到如下结果(按totalCount排序):

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

{"email" : "[e-mail]", "mlCount" : 2, "egCount" : 1, "totalCount":3}
{"email" : "[e-mail]", "mlCount" : 1, "egCount" : 1, "totalCount":2}
{"email" : "[e-mail]", "mlCount" : 0, "egCount" : 1, "totalCount":1}

我该怎么做?查询应如何?谢谢.

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

推荐答案

首先，在每个投票中添加一个字段voteType.该字段指示其类型.具有此字段，您无需将投票保留在两个单独的数组mlVoters和egVoters中；您可以改为将这些数组连接到每个文档的单个数组中，然后展开.

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.

最后，您将字段totalCount添加为其他两个计数的总和.

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

这篇关于mongodb聚合多个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！