mongodb 聚合与 $group 和 $lookup
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39091662/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mongodb aggregate with $group and $lookup
提问by KTB
I'm trying to perform a "group by" on a table and "join" it with another table. Corresponding SQL statement would be:
我正在尝试在一张桌子上执行“分组依据”并将其与另一张桌子“连接”。对应的 SQL 语句为：
SELECT T1.total, T1.email, T1.type, table_2.name FROM
(SELECT SUM(amount) AS total, email, type
FROM table_1
GROUP BY email, type) T1
INNER JOIN table_2
on T1.email = table_2.email
But since mongodb still doesn't have inner join feature, I tried to use "$lookup" and do the task. here's my code:
但是由于 mongodb 仍然没有内部连接功能，我尝试使用“$lookup”并完成任务。这是我的代码：
db.table_1.aggregate([
{$group : {_id : {email: "$email", type:"$type"},total: { $sum: "$amount" }}},
{$lookup: {from: "table_2", localField: "email", foreignField: "email", as: "details"}} ]);
But in the results I'm getting, details returns and empty object:
但在我得到的结果中，细节返回和空对象：
{ "_id" : { "user" : "[email protected]", "type" : "Car" }, "total" : 2, "details" : [ ] }
{ "_id" : { "user" : "[email protected]", "type" : "Bike" }, "total" : 3, "details" : [ ] }
{ "_id" : { "user" : "[email protected]", "type" : "Car" }, "total" : 1, "details" : [ ] }
But if I run the query without using $group, it works fine. So I'm wondering whether the $group and $lookup functions cannot be used together. If so is there a work-around or what would be the optimal way to get the query done?
但是如果我在不使用 $group 的情况下运行查询，它就可以正常工作。所以我想知道 $group 和 $lookup 函数是否不能一起使用。如果是这样，是否有解决方法或完成查询的最佳方法是什么？
[mongo db version I'm using: > db.version() 3.2.7]
[我正在使用的 mongo db 版本：> db.version() 3.2.7]
回答by KTB
I found the answer to the problem. The reason why I got empty array was the way I've used the localField inside the $lookup.
我找到了问题的答案。我得到空数组的原因是我在 $lookup 中使用 localField 的方式。
Since I'm trying to join the table_2 with the $group result of the table_1, the local field should be "_id.email".
由于我试图将 table_2 与 table_1 的 $group 结果连接起来，所以本地字段应该是“_id.email”。
So the working query would be:
所以工作查询将是：
db.table_1.aggregate([
{$group : {_id : {email: "$email", type:"$type"},total: { $sum: "$amount" }}},
{$lookup: {from: "table_2", localField: "_id.email", foreignField: "email", as: "details"}},
{$match: {details: {$ne: []}}}
]);
Thanks @Wake and @Clement for help
感谢@Wake 和@Clement 的帮助
回答by Wake
If you want your $lookupto work like an INNER JOIN, that is, you don't want results unless there is at least one matching document in the lookup table, you can add a $matchat the end comparing your lookup table results to an empty array [ ]:
如果您希望$lookup像INNER JOIN一样工作，也就是说，除非查找表中至少有一个匹配文档，否则您不想要结果，您可以在末尾添加$match将查找表结果与一个空数组[]：
db.table_1.aggregate([
{$group : {_id : {email: "$email", type:"$type"},total: { $sum: "$amount" }}},
{$lookup: {from: "table_2", localField: "email", foreignField: "email", as: "details"}},
{$match: {details: {$ne: []}}}
]);
回答by Clement Amarnath
From Mongo version 3.2 onwards $lookup is used to support left-outer join.
从 Mongo 3.2 版开始， $lookup 用于支持左外连接。
I'm wondering whether the $group and $lookup functions cannot be used together.
我想知道 $group 和 $lookup 函数是否不能一起使用。
$group and $lookup can be used together.
$group 和 $lookup 可以一起使用。
How to Use it for INNER JOIN
如何将其用于 INNER JOIN
You have add one more condition to filter the results. Use $match. You can also try with $in.
您还添加了一个条件来过滤结果。使用 $match。您也可以尝试使用 $in。
References
参考
http://www.clusterdb.com/mongodb/joins-and-other-aggregation-enhancements-in-mongodb-3-2
http://www.clusterdb.com/mongodb/joins-and-other-aggregation-enhancements-in-mongodb-3-2
https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/
https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/
https://docs.mongodb.com/manual/reference/operator/aggregation/match/
https://docs.mongodb.com/manual/reference/operator/aggregation/match/
