Mongodb上的聚合过滤和查找

Question

我的第一个收藏品employeecategory如下所示；

[{
    name: "GARDENING"
  },
  {
    name: "SECURITY"
  },
  {
    name: "PLUMBER"
  }
]

我的第二个收藏complaints如下所示；

[{
  communityId: 1001,
  category: "SECURITY",
  //other fields
}, {
  communityId: 1001,
  category: "GARDENING",
  //other fields
}]

我试着加入上面的表格，得到下面的结果；

[{
  "count": 1,
  "name": "GARDENING"
}, {
  "count": 1,
  "name": "SECURITY"
}, {
  "count": 0,
  "name": "PLUMBER"
}]

即使在集合2中没有记录，我也需要计数。我尝试了以下聚合，但没有起作用。如果我删除匹配条件，它是工作的，但我需要过滤社区id。能不能请一些人建议最好的方法来实现这一点。Mongo版本为3.4.0

db.employeecategory.aggregate(
  [{
    $match: {
      "complaints.communityId": 1001
    }
  }, {
    "$lookup": {
      from: "complaints",
      localField: "name",
      foreignField: "category",
      as: "embeddedData"
    }
  }]
)

Answer 1

不可能在单个聚合中同时实现对communityId = 1001的筛选和分组而不丢失count = 0类别。这样做的方法是首先从complaints集合开始，过滤communityId = 1001对象，并使用它创建一个临时集合。然后从employeecategory集合中，从$lookup到该临时集合，从$group到name，此时您将得到您的结果，然后删除temp表。

// will not modify complaints document, will create a filtered temp document
db.complaints.aggregate(
  [{
      $match: {
        communityId: 1001
      }
    },
    {
      $out: "temp"
    }
  ]
);

// will return the answer that is requested by OP
db.employeecategory.aggregate(
  [{
    $lookup: {
      from: "temp",
      localField: "name",
      foreignField: "category",
      as: "array"
    }
  }, {
    $group: {
      _id: "$name",
      count: {
        $sum: {
          $size: "$array"
        }
      }
    }
  }]
).pretty();

db.temp.drop(); // to get rid of this temporary collection

会产生结果；

{ _id: "PLUMBER", count: 0},
{ _id: "SECURITY", count: 2},
{ _id: "GARDENING", count: 1}

对于我所拥有的测试数据；

db.employeecategory.insertMany([
   { name: "GARDENING" },
   { name: "SECURITY" },
   { name: "PLUMBER" }
]);

db.complaints.insertMany([
   { category: "GARDENING", communityId: 1001 },
   { category: "SECURITY", communityId: 1001 },
   { category: "SECURITY", communityId: 1001 },
   { category: "SECURITY", communityId: 1002 }
]);