MongoDB对同一集合进行多次查找

Question

我有两个集合比尔和员工。比尔包含有关每月学生账单的信息，员工包含在学校工作的所有类型的人（会计师、教师、维护等）。
比尔有billVerifyBy和classteacher字段，它们指向员工的记录。
账单收集

{
    "_id": ObjectId("ab12dns..."),  //mongoid
    "studentname": "demoUser",
    "class": { "section": "A"},
    "billVerifiedBy": "121212",
    "classteacher": "134239",

}

员工集合

json prettyprint-override">{
    "_id": ObjectId("121212"), // random number
    "name": "Darn Morphy",
    "department": "Accounts",
    "email": "[e-mail]",
}

{
    "_id": ObjectId("134239"),
    "name": "Derreck",
    "department": "Faculty",
    "email": "[e-mail]",
}

我需要检索与特定账单相关的帐户和教师信息。我正在使用MongoDB查找来获取信息。但是，我必须查找同一个表两次，因为billVerifiedBy和classteacher属于相同的员工表，如下所示。

db.bill.aggregate([  
    {
        $lookup: {"from": "employee", "localField": "billVerifiedBy", "foreignField": "_id", "as": "accounts"}},
    },
    {
        $lookup: {"from": "employee", "localField": "classteacher", "foreignField": "_id", "as": "faculty"}},
    },
    {
        $project: {
            "studentname": 1, 
            "class": 1,
            "verifiedUser": "$accounts.name",
            "verifiedByEmail":"$accounts.email",
            "facultyName": "$faculty.name",
            "facultyEmail": "$faculty.email"
        }
    }
]

我不知道这是否是在单个员工集合中安排帐户和教员信息的好方法。在同一个集合中查找两次是正确的吗？或者我应该创建单独的帐户和教员集合并使用它进行查找。请建议在性能方面最好的方法。

Answer 1

在mongoDB中，当您想从同一个集合中加入多个文档时，您可以使用“$lookup”及其“管道”和“let”选项。它使用定义的变量过滤您要获取的文档。

db.getCollection('Bill').aggregate([{
        "$lookup": {
            "as": "lookupUsers",
            "from": "Employee",
            // define variables that you need to use in pipeline to filter documents
            "let": {
                "verifier": "$billVerifiedBy",
                "teacher": "$classteacher"
            },
            "pipeline": [{ // filter employees who you need to filter.
                    "$match": {
                        "$expr": {
                            "$or": [{
                                    "$eq": ["$_id", "$$verifier"]
                                },
                                {
                                    "$eq": ["$_id", "$$teacher"]
                                }
                            ]
                        }
                    }
                },

                { // combine filtered 2 documents in an employee array
                    "$group": {
                        "_id": "",
                        "employee": {
                            "$addToSet": {
                                "_id": "$_id",
                                "name": "$name",
                                "department": "$department",
                                "email": "$email"
                            }
                        }
                    }
                },
                { // takes item from the array by predefined variable.
                    "$project": {
                        "_id": 0,
                        "billVerifiedBy": {
                            "$slice": [{
                                    "$filter": {
                                        "input": "$employee",
                                        "cond": {
                                            "$eq": ["$$this._id", "$$verifier"]
                                        }
                                    }
                                },
                                1
                            ]
                        },
                        "classteacher": {
                            "$slice": [{
                                    "$filter": {
                                        "input": "$employee",
                                        "cond": {
                                            "$eq": ["$$this._id", "$$teacher"]
                                        }
                                    }
                                },
                                1
                            ]
                        }
                    }
                },
                {
                    "$unwind": "$billVerifiedBy"
                },
                {
                    "$unwind": "$classteacher"
                },
            ]
        }
    },
    {
        "$unwind": "$lookupUsers"
    },
]);

输出如下：

{
    "_id": ObjectId("602916dcf4450742cdebe38d"),
    "studentname": "demoUser",
    "class": {
        "section": "A"
    },
    "billVerifiedBy": ObjectId("6029172e9ea6c9d4776517ce"),
    "classteacher": ObjectId("6029172e9ea6c9d4776517cf"),
    "lookupUsers": {
        "billVerifiedBy": {
            "_id": ObjectId("6029172e9ea6c9d4776517ce"),
            "name": "Darn Morphy",
            "department": "Accounts",
            "email": "[e-mail]"
        },
        "classteacher": {
            "_id": ObjectId("6029172e9ea6c9d4776517cf"),
            "name": "Derreck",
            "department": "Faculty",
            "email": "[e-mail]"
        }
    }
}