使用 $lookup 在 MongoDB 中进行深度级联关联查询

Question

我正在使用$lookup连接两个集合，并从以下查询中获取数据：

let condition = {status:{$ne:config.PROJECT_STATUS.completed}, assignId:mongoose.Types.ObjectId(req.params.id)};
Project.aggregate([
    {
      $match: condition  
    },
    {
      "$group":{
        "_id": "$_id"
      }
    },
    { 
      "$lookup": {
        "from": "worksheets",
        "let": { "projectId": "$_id" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": [ "$projectId", "$$projectId" ] }}},
          { "$group": {_id:"$projectId", totalHours:{"$sum": "$hours"}}},
          { 
            "$lookup": {
              "from": "projects",
              "let": { "projectId": "$_id" },
              "pipeline": [
                { "$match": { "$expr": { "$eq": [ "$_id", "$$projectId" ] }}},
                { "$project": { "projectName": 1,"upworkdId":1,"status":1,"developers":1,"hoursApproved":1 }}
              ],
              "as": "project"
            }
          }
        ],
        "as": "projects"
      }
    }
])
.then((data)=>{
    res.json(data);
})

执行以上查询会给我以下结果：

[
  {
    "_id": "5c0a4083753a321c6c4ee024",
    "projects": [
        {
            "_id": "5c0a4083753a321c6c4ee024",
            "totalHours": 11,
            "project": [
                {
                    "_id": "5c0a4083753a321c6c4ee024",
                    "hoursApproved": 24,
                    "developers": [
                        "5c0a29c697e71a0d28b910a9"
                    ],
                    "projectName": "fallbrook winery",
                    "status": "pending"
                }
            ]
        }
    ]
  }
]

现在我想填充 project 数组中的 developers 子字段。如何修改上述代码以达到此目的。

- user10742206

Answer 1

你可以在developers数组中再使用$lookup一层。

就像这样

Project.aggregate([
  { "$match": condition },
  { "$group": { "_id": "$_id" }},
  { "$lookup": {
    "from": "worksheets",
    "let": { "projectId": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$projectId", "$$projectId"] } } },
      { "$group": { "_id": "$projectId", "totalHours": { "$sum": "$hours" } }},
      { "$lookup": {
        "from": "projects",
        "let": { "projectId": "$_id" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": ["$_id", "$$projectId"] } } },
          { "$lookup": {
            "from": "developers",
            "let": { "developers": "$developers" },
            "pipeline": [
              { "$match": { "$expr": { "$in": ["$_id", "$$developers"] } } },
            ],
            "as": "developers"
          }},
          { "$project": { 
            "projectName": 1, "upworkdId": 1, "status": 1, "developers": 1,  "hoursApproved": 1
          }}
        ],
        "as": "project"
      }}
    ],
    "as": "projects"
  }}
])

- Ashh

---

我得到了一个空数组。 - user10742206

开发者的 ids 是字符串还是 objectId？你确定在 from 表达式中放入了正确的 developers 名称吗？ - Ashh

我有一个问题。我可以问你吗？ - user10742206

请继续进行。 - Ashh