MongoDB中按协作组生成去重source列表并统计次数

原始集合数据（network集合） #

{"_id" : ObjectId("639755094ab8f1702b3b661a"),"Id" : "Tom","otherId" : "Jack","source" : "proposal","year" : "2017"}
{"_id" : ObjectId("6397550a4ab8f1702b3b6ff7"),"Id" : "Tom","otherId" : "Jack","source" : "grant","year" : "2018"}
{"_id" : ObjectId("6397550a4ab8f1702b3b7336"),"Id" : "Tom","otherId" : "Jack","source" : "tech","year" : null}
{"_id" : ObjectId("6397550b4ab8f1702b3b75ae"),"Id" : "Tom","otherId" : "Jack","source" : "proposal","year" : "2019"}
{"_id" : ObjectId("6397550b4ab8f1702b3b7a03"),"Id" : "Tom","otherId" : "Jack","source" : "proposal","year" : "2018"}
{"_id" : ObjectId("6397550b4ab8f1702b3b7a01"),"Id" : "Sarah","otherId" : "Edward","source" : "proposal","year" : "2018"}
{"_id" : ObjectId("6397550b4ab8f1702b3b7a04"),"Id" : "Sarah","otherId" : "Edward","source" : "grant","year" : "2018"}

期望输出（新集合） #

{"_id" : ObjectId("6397550b4ab8f1702b3b7a03"),"Id" : "Tom","otherId" : "Jack","source" : ["proposal","grant","tech"],"count" : 5}
{"_id" : ObjectId("6397550b4ab8f1702b3b7a02"),"Id" : "Sarah","otherId" : "Edward","source" : ["proposal","grant"],"count" : 2}

解决方案：使用聚合管道+$out #

通过MongoDB聚合框架的$group、$project和$out阶段实现需求，核心用$addToSet生成去重的source列表：

db.network.aggregate([
  // 按协作组分组，统计次数并收集去重source
  {
    $group: {
      _id: { Id: "$Id", otherId: "$otherId" }, // 按Id+otherId分组
      count: { $sum: 1 }, // 统计协作总次数
      uniqueSources: { $addToSet: "$source" }, // 自动去重收集source
      originalId: { $last: "$_id" } // 取组内最后一个文档的_id作为新文档的_id，可选
    }
  },
  // 调整输出字段结构
  {
    $project: {
      _id: "$originalId", // 替换为原文档的_id
      Id: "$_id.Id",
      otherId: "$_id.otherId",
      source: "$uniqueSources",
      count: 1
    }
  },
  // 将结果输出到新集合
  {
    $out: "collaboration_summary"
  }
])

各阶段说明： #

1. $group：

   • 用_id: {Id: "$Id", otherId: "$otherId"}将同一对协作人员的所有文档归为一组；
   • $sum:1统计每组的文档总数，即两人的协作次数；
   • $addToSet: "$source"会自动跳过重复的source值，生成无重复的数组；
   • originalId可选，用来保留原集合中某一个文档的_id作为新文档的_id，可根据需求用$first或$last选择。

2. $project：
   重新整理输出字段，将分组后的Id、otherId提取出来，把去重后的source数组重命名为source，同时保留count和指定的_id。

3. $out：
   将聚合后的结果写入指定的新集合collaboration_summary，如果该集合已存在会被覆盖。

验证结果 #

运行上述聚合命令后，查询新集合即可得到期望的结果：

db.collaboration_summary.find()

内容的提问来源于stack exchange，提问作者user110920