面试题：MongoDB内嵌文档专家难度查询问题

Question

假设MongoDB集合中有这样的文档结构：{ '_id': ObjectId('64c3c0c9d5c09506559d955f'), 'department': 'IT', 'employees': [ { 'name': 'Eve', 'performance': [ { 'quarter': 1,'score': 85 }, { 'quarter': 2,'score': 90 } ] }, { 'name': 'Frank', 'performance': [ { 'quarter': 1,'score': 78 }, { 'quarter': 2,'score': 82 } ] } ] } 。现在需要查询出每个部门中，至少有一名员工在两个季度的平均绩效得分大于85分的部门，并且要返回这些部门及其满足条件的员工信息，写出实现该复杂查询的MongoDB语句。

Answer 1

db.collection.aggregate([
    {
        $unwind: "$employees"
    },
    {
        $unwind: "$employees.performance"
    },
    {
        $group: {
            _id: {
                department: "$department",
                employeeName: "$employees.name"
            },
            averageScore: {
                $avg: "$employees.performance.score"
            }
        }
    },
    {
        $match: {
            averageScore: { $gt: 85 }
        }
    },
    {
        $group: {
            _id: "$_id.department",
            employees: {
                $push: {
                    name: "$_id.employeeName",
                    averageScore: "$averageScore"
                }
            }
        }
    }
]);