title: mongodb aggregate with $group and $lookup
body: <p>I'm trying to perform a "group by" on a table and "join" it with another table.
  Corresponding SQL statement would be:</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>But since mongodb still doesn't have inner join feature, I tried to use "$lookup" and do the task. here's my code:</p>
  
  <pre><code>db.table_1.aggregate([
  {$group : {_id : {email: "$email", type:"$type"},total: { $sum: "$amount" }}},
  {$lookup: {from: "table_2", localField: "email", foreignField: "email", as: "details"}} ]);
  </code></pre>
  
  <p>But in the results I'm getting, details returns and empty object:</p>
  
  <pre><code>{ "_id" : { "user" : "b@b.com", "type" : "Car" }, "total" : 2, "details" : [ ] }
  { "_id" : { "user" : "a@a.com", "type" : "Bike" }, "total" : 3, "details" : [ ] }
  { "_id" : { "user" : "a@a.com", "type" : "Car" }, "total" : 1, "details" : [ ] }
  </code></pre>
  
  <p>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?</p>
  
  <p>[mongo db version I'm using: > db.version() 3.2.7]</p>