Use $lookup after $group

Question

I have two tables: userinfo and challenge

**userinfo**
username:'aa',nickname:'bb',...

**challenge**
username:'aa',win:1,loss:0,...
username:'aa',win:0,loss:1,...
username:'bb',win:1,loss:0,...
username:'bb',win:0,loss:1,...

I am trying to perform a mongodb aggregate to group on username located in the lookup object and sum based upon items located in challenge. my code:

db.challenge.aggregate([
{$group : "$username",win: { $sum: "$win" }},loss: { $sum: "$loss" }}},
{$lookup: {from: "userinfo", localField: "username", foreignField: "username", as: "userinfo"}} ]);

But in the results userinfo returns an empty array:

{ "_id" : "aa", win:12, loss:10,"userinfo" : [ ] }
{ "_id" : "bb", win:2, loss:5,"userinfo" : [ ] }

mongo version is 3.4, so where do i make wrong?

Answer 1

You have some structurual problems mostly:

db.challenge.aggregate([
 { "$group" : {
   "_id": "$username",
   "win": { "$sum": "$win" },
   "loss": { "$sum": "$loss" }
 }},
 { "$lookup": {
   "from": "userinfo",
   "localField": "_id",
   "foreignField": "username",
   "as": "userinfo"
 }}
]);

Aside from fixing up the structural problems with the syntax, the main thing you seemed to be missing is that the "username" field needs to be included as the "grouping key" which is _id.

Once that is done, there is no such field called "username" in the output. Aggregation pipeline stages can only act on properties "as output from the previous stage". It's a "pipeline" like the pipe | operator on command lines and as is the same case there, each separated stage cannot access the shape of data previous to what the preceding stage transformed it to.

Therefore, since your "grouping key" actually contains the "username data" now, simply use it as the "localField" input to the $lookup statement. The data looks different after $group, so use the new "shape" instead.