mongodb aggregate with $group and $lookup

Question

I'm trying to perform a "group by" on a table and "join" it with another table. Corresponding SQL statement would be:

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:

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?

[mongo db version I'm using: > db.version() 3.2.7]

Answer 1

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.

Since I'm trying to join the table_2 with the $group result of the table_1, the local field should be "_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

Answer 2

If you want your $lookup to 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 $match at the end comparing your lookup table results to an empty array [ ]:

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: []}}}
]);