如何在mongo中过滤子数组并返回文档

debugcn 发表于 Dev

朱格努

我有两个系列Employee，Client

Employee 架构有以下记录

{
    "_id": ObjectId("5a852dcd0290f7eca89e9a79"),
    "FirstName": "Nirav",
    "LastName": "Modi",
    "Gender": true,
    "Forms": [{
            "ClientId": ObjectId("5a8528ed0290f7eca89e9a5f"),
            "ProjectId": ObjectId("5a856fde0290f7eca89e9a88"),
            "FormId": ObjectId("5a62e561f6647f17f85e54c5")
        }]
}

Client 架构有以下记录

{
    "_id" : ObjectId("5a8528ed0290f7eca89e9a5f"),
    "CompanyName" : "PNB",
    "Projects" : [{
            "_id" : ObjectId("5a856ca70290f7eca89e9a7f"),
            "Name" : "House Loan",
            "Description" : "Get house load",
            "Forms" : []
        }, {
            "_id" : ObjectId("5a856fde0290f7eca89e9a88"),
            "Name" : "Car Loan",
            "Description" : "get car loan",
            "Forms" : [
                ObjectId("5a62e82299d9fe0a14a1ead5"),
                ObjectId("5a6eec263bf43426d4d31780"),
                ObjectId("5a62e561f6647f17f85e54c5")
            ]
        }
    ]
}

在我的系统中，员工被分配了要处理的表格。如您所见，员工架构包含Forms包含ClientId, ProjectId, 的字段FormId。

表单可以在多个客户端用于多个项目。

当我获取数据时，我希望输出如下所示

{
    "_id": ObjectId("5a852dcd0290f7eca89e9a79"),
    "FirstName": "Aartik",
    "LastName": "Ladumor",
    "Gender": true,
    "Clients": [{
            "_id": ObjectId("5a8528ed0290f7eca89e9a5f"),
            "CompanyName": "PNB",
            "Projects": [{
                    "_id": ObjectId("5a856fde0290f7eca89e9a88"),
                    "Name": "Car Loan",
                    "Description": "get car loan",
                    "Forms": [
                        ObjectId("5a62e82299d9fe0a14a1ead5"),
                        ObjectId("5a6eec263bf43426d4d31780"),
                        ObjectId("5a62e561f6647f17f85e54c5")
                    ]
                }
            ]
        }
    ]
}

仅获取项目包含分配给员工的表单的客户。

为此，我进行如下聚合

db.Employees.aggregate([{
            $lookup: {
                from: "Clients",
                localField: "Forms.ClientId",
                foreignField: "_id",
                as: "Clients"
            }
        }, {
              filter projects array in matched client contains only
              projects that match Employee.Forms each elements 
              ProjectsId -> Client.Projects._id and
              FormId -> in Client.Projects.Forms array containing forms ObjectId 
        }
    ]).pretty()

米克尔

您可以尝试以下聚合

db.employees.aggregate([
  { $unwind: "$Forms" },
  {
    $lookup: {
        from: "clients",
        localField: "Forms.ClientId",
        foreignField: "_id",
        as: "Clients"
    }
  },
  { $unwind: "$Clients" },
  { $unwind: "$Clients.Projects" },
  {
    $redact: {
      $cond: {
        if: { $eq: [ "$Forms.ProjectId", "$Clients.Projects._id" ] },
        then: "$$KEEP",
        else: "$$PRUNE"
      }
    } 
  },
  {
    $group: {
      _id: {
        _id: "$_id",
        ClientId: "$Clients._id"
      },
      FirstName: { $first: "$FirstName" },
      LastName: { $first: "$LastName" },
      Gender: { $first: "$Gender" },
      Client: { $first: "$Clients" },
      Projects: { $push: "$Clients.Projects" }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      FirstName: { $first: "$FirstName" },
      LastName: { $first: "$LastName" },
      Gender: { $first: "$Gender" },
      Clients: { $push: {
        _id: "$Client._id",
        CompanyName: "$Client.CompanyName",
        Projects: "$Projects"
      } }
    }
  }
])

基本上你必须多次使用$unwind，因为你需要比较值而不是数组。所以 $lookup 合并了两个集合。然后您需要比较项目 ID，因此您必须再次 $unwind。要过滤掉不属于员工的项目，您可以使用$redact来比较两个字段。然后要得到两层嵌套数组，您应该使用 $group。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。