mongodb：汇总查询-电脑我帮您

mongodb：汇总查询

我有一个名为Engineer的集合，其中包含一个文档：

{
  id : *some mongodb id*,
  username : "xyz",
  email id : "[email protected]"
  profession : [
      {
      id : *some mongodb id* ,
      location : "Moscow",
      licence : "civil engineer"
      }
      {
      id : *some mongodb id* ,
      location : "Berlin",
      licence : "cs engineer"
      }
]
}

我有一个包含三个文档的项目集合：

{   
        "_id":"5ebaa72c9ad5010017a2179b"
        "projectName":"test project 1", 
        "status": "open"
        "licence":"cs engineer",
        "location":"Berlin",
        "description":"This is project",
        "duration":20,
 } ,
{   
        "_id":"5ebaa72c9ad5010017a2178b"
        "projectName":"test project 2",
        "status": "open"
        "licence":"civil engineer",
        "location":"Berlin",
        "description":"This is project",
        "duration":10,
 } ,
{   
        "_id":"5ebaa72c9ad5010017a2177b"
        "projectName":"test project 3",
        "status": "open"
        "licence":"civil engineer",
        "location":"Moscow",
        "description":"This is project",
        "duration":30,
 }

我有一个“出价”模式。模式如下：（当工程师投标项目时，用于存储必要的数据）

{
    "projectID": : "*some project _id"       // will be a project id
    "bids": [{
        "bidAmount":
        "engineerID": 
    }]
    "__v": 1
}

我的问题：我只想填充工程师not投标的那些项目，并且我想将工程师的位置和许可证与该项目的位置和许可证进行匹配，以便他可以查看有关其许可证和许可证的项目。位置。因此，如果您采用上述示例数据，我只希望我的结果包含“ 测试项目1”和“ 测试项目3”的详细信息。

很抱歉，冗长的解释只是希望人们在阅读时能够理解。感谢任何帮助。谢谢！

更新：

我尝试使用此聚合查询：

// match.location = ['Moscow' , 'Berlin']
// match.licence = ['civil engineer' , 'cs engineer']

const project = await BidHistory.aggregate([
  {
    $match: {
      'bids.engineerID': { $ne: mongoose.Types.ObjectId(req.engineer.id) }
    }
  },
  {
    $lookup: {
      from: 'projects',
      localField: 'projectID',
      foreignField: '_id',
      as: 'project'
    }
  },
  {
    $match: {       
          'project.location': { $in: match.location },
          'project.licence': { $in: match.licence }
    }
  },
  {
    $project: {
      bids: 0,
      projectID: 0,
      _id: 0,
    }
  }
]);

但是通过上面的查询，我得到了不必要的数据。如果将此查询应用于我的样本数据。我也返回了我不想得到的结果[[test project 2。我知道它的原因是$ in运算符，但我找不到解决方法！

注意：工程师也只能拥有一个
位置和许可证
回答如下：

// note that there shouldn't be id
const professions = [
  {
    location: "Moscow",
    licence: "civil engineer"
  },
  {
    location: "Berlin",
    licence: "cs engineer"
  }
]

然后您可以使用以下查询

const projects = BidHistory.aggregate([{ { $match: { 'bids.engineerID': { $ne: mongoose.Types.ObjectId(req.engineer.id) } } }, { $lookup: { from: "projects", localField: "projectID", foreignField: "_id", as: "project" } }, { $match: { "project": { $elemMatch: { $or: professions // use matching professions here } } } }, /* Bonus: to get projects at top level add following stages */ { $unwind: '$project' }, { $replaceRoot: { newRoot: '$project' } } }])