javascript - $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"] } } },
                  { 
                    "$lookup": {
                      "from": "users",
                      "let": { "developers": "$developers" },
                      "pipeline": [
                        { "$match": { "$expr": { "$in": ["$_id", "$$developers"] } } },
                        { "$project":{"firstName":1,"lastName":1}}
                      ],
                      "as": "developers"
                    }
                  },
                  { 
                    "$lookup": {
                      "from": "billing_accounts",
                      "let": { "upworkId": "$upworkId" },
                      "pipeline": [
                        { "$match": { "$expr": { "$eq": ["$_id", "$$upworkId"] } } },
                        {"$project":{"name":1,"username":1}}
                      ],
                      "as": "upworkId"
                    }
                  },
                  { 
                    "$project": { 
                      "projectName": 1, "upworkId": 1, "status": 1, "developers": 1,  "hoursApproved": 1
                    }
                  }
                ],
                "as": "project"
            }}
           ],
            "as": "projects"
          }
    }
])

它给了我以下结果：

[
    {
        "_id": "5c188a9959f6cf1258f4cb01",
        "projects": [
            {
                "_id": "5c188a9959f6cf1258f4cb01",
                "totalHours": 8,
                "project": [
                    {
                        "_id": "5c188a9959f6cf1258f4cb01",
                        "hoursApproved": 192,
                        "developers": [
                            {
                                "_id": "5c0a29e597e71a0d28b910aa",
                                "lastName": "kumar",
                                "firstName": "Amit"
                            }
                        ],
                        "projectName": "Jims fitness",
                        "status": "ongoing",
                        "upworkId": [
                            {
                                "_id": "5c17a1cec1a7681f7c54bb2d",
                                "name": "Heena Ln",
                                "username": "heena_ln"
                            }
                        ]
                    }
                ]
            }
        ]
    },
    {
        "_id": "5c17a253c1a7681f7c54bb2f",
        "projects": []
    }
]

但我想要得到的是：

[
        {
            "_id": "5c188a9959f6cf1258f4cb01",
            "projects": [
                {
                    "_id": "5c188a9959f6cf1258f4cb01",
                    "totalHours": 0,
                    "project": [
                        {
                            "_id": "5c188a9959f6cf1258f4cb01",
                            "hoursApproved": 192,
                            "developers": [
                                {
                                    "_id": "5c0a29e597e71a0d28b910aa",
                                    "lastName": "kumar",
                                    "firstName": "Amit"
                                }
                            ],
                            "projectName": "Project1",
                            "status": "ongoing",
                            "upworkId": [
                                {
                                    "_id": "5c17a1cec1a7681f7c54bb2d",
                                    "name": "Heena Ln",
                                    "username": "heena_ln"
                                }
                            ]
                        }
                    ]
                }
            ]
        },
        {
            "_id": "5c17a253c1a7681f7c54bb2f",
            "projects": [
             {
                    "_id": "5c17a253c1a7681f7c54bb2f",
                    "totalHours": 0,
                    "project": [
                        {
                            "_id": "5c17a253c1a7681f7c54bb2f",
                            "hoursApproved": 192,
                            "developers": [
                                {
                                    "_id": "5c0a29e597e71a0d28b910a9",
                                    "lastName": "kumar",
                                    "firstName": "Rajat"
                                }
                            ],
                            "projectName": "project2",
                            "status": "ongoing",
                            "upworkId": [
                                {
                                    "_id": "5c17a1cec1a7681f7c54bb2d",
                                    "name": "Heena Ln",
                                    "username": "heena_ln"
                                }
                            ]
                        }
                    ]
                }
             ]
        }
 ]

如您所见，现在我的totalHours 等于 0而不是空数组，并且有项目详细信息。

实际上我有四个集合：项目、工作表、用户和账单，我正在对项目集合执行聚合查询以获取项目经理的项目，为此我还加入工作表集合以获取员工多少小时的数据在这个项目上工作，因为工作表集合包含 projectId、userId 和 hours。

查询：您可以在结果中看到，我得到了空的项目数组，这是因为我没有第二个项目 projectId 的任何记录到工作表集合中，所以为此它给了我空数组，但我希望按原样获取项目详细信息，并且 totalHours 等于 0。

标签： javascriptmongodb