面试题：MongoDB中$group操作符在嵌套文档结构下的深度应用

Question

有一个集合`company`，文档结构如下：{ "department": "部门A", "employees": [ { "name": "员工1", "projects": [ { "projectName": "项目1", "hoursWorked": 40 }, { "projectName": "项目2", "hoursWorked": 30 } ] }, { "name": "员工2", "projects": [ { "projectName": "项目1", "hoursWorked": 35 }, { "projectName": "项目3", "hoursWorked": 25 } ] } ] } 使用聚合管道中的`$group`操作符，按部门统计每个项目的总工时，以及平均每个员工参与项目的数量。

Answer 1

[
    {
        $group: {
            _id: "$department",
            totalHoursPerProject: {
                $push: {
                    $reduce: {
                        input: "$employees.projects",
                        initialValue: 0,
                        in: {
                            $cond: [
                                { $eq: ["$$this.projectName", "$$value.projectName"] },
                                {
                                    projectName: "$$this.projectName",
                                    totalHours: { $add: ["$$this.hoursWorked", "$$value.totalHours"] }
                                },
                                {
                                    projectName: "$$this.projectName",
                                    totalHours: "$$this.hoursWorked"
                                }
                            ]
                        }
                    }
                }
            },
            averageProjectsPerEmployee: {
                $avg: {
                    $size: "$employees.projects"
                }
            }
        }
    }
]