How do I query another collection within mongo aggregate

Question

I have a DB similar to Collection 1

{group_name: "group_1", key_name: "key_name_1"},
{group_name: "group_1", key_name: "key_name_2"},
{group_name: "group_2", key_name: "key_name_3"},
{group_name: "group_2", key_name: "key_name_4"},
{group_name: "group_2", key_name: "key_name_5"}

Collection 2

{subtype: "A", key_name: "key_name_1"},
{subtype: "B", key_name: "key_name_1"},
{subtype: "A", key_name: "key_name_2"},
{subtype: "B", key_name: "key_name_2"},
{subtype: "C", key_name: "key_name_2"},
{subtype: "A", key_name: "key_name_3"},
{subtype: "A", key_name: "key_name_4"}

I want a count of objects from collection2 per each group. I am trying to use aggregate as below:

db.collection1.aggregate([
{
    $group : {
        _id: "$group_name",
        total : { $sum : db.collection2.find({key_name : "$key_name"}).count()}
   }
}
])

Expected Output:

{_id:"group_1",total:5},
{_id:"group_2",total:2}

Actual Output:

{_id:"group_1",total:0},
{_id:"group_2",total:0}

Note:

db.collection1.aggregate([ {
    $group : {
        _id: "$group_name",
        total : { $sum : db.collection2.find({key_name : "key_name_1"}).count()}
        } 
    } 
]) 

Above Code produces the following output which is expected.

{_id:"group_1",total:4}, 
{_id:"group_2",total:6}

I need a solution in pymongo or mongoshell..

Answer 1

That's what the $lookup aggregation is for

db.collection1.aggregate([ {   
     $lookup:
       {
         from: "collection2",
         localField: "key_name",
         foreignField: "key_name",
         as: "collection2_docs"
       }
  },{
    $group : {
        _id: "$group_name",
        total : { $sum : { $size: "$collection2_docs" } }
        } 
    } 
])