I am trying to use mongoDb aggregation pipeline to lookup other table and check if matched some of the attributes.

my query is something like below (p.s. bigtable is sharded):

 db.bigtable.aggregate( [  
    {$lookup:{ from: "user", localField:
               "user_code", foreignField: "code",as: "user"}},   
    {$match: {"user.country":"US" } }] )

I have created index for bigtable.user_code, user.code & user.country. But the query is very very very slow. I tried explain it, it is COLLSCAN.

It seems to me what using $match with foreign table's attribute, index is not supported and therefore, the query speed is very slow.