Mongo lookup join on objectid not working?

Question

For the collections:

 data:
 { "_id" : ObjectId("57a"), "rep" : ObjectId("570"), "label" : "pat" }
 { "_id" : ObjectId("57b"), "rep" : ObjectId("571"), "label" : "pat" }

 rep:
 { "_id" : ObjectId("570") }
 { "_id" : ObjectId("571") }

query

db.rep.aggregate([{ $lookup: 
   {from: "data", localField:"rep", foreignField:"_id", as: "in_common" }
}])

yields an empty set.

The query should produce a result with two rows.

How can I fix this?

Answer 1

You need to modify your query as shown below

db.data.aggregate([ { $lookup: {from: "rep", localField:"rep", foreignField:"_ id", as: "in_common" }}])

This query will yield you two records.

Reason for not getting the records: In your collection you don't have mapping for data._id to rep._id whereas you have the mapping from rep._id to data.rep

https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

Hope it Helps!