Find documents in MongoDB using result of another query

Question

I'm new to MongoDB and I'm trying to find documents in collection A, where field _id is equal to field excel_template from collection B.

var r = db.B.find({"name":/.*aco.*/}, {excel_template:1, _id:0}).excel_template;
db.A.find({"_id":{$eq: "${r}" }})

but I have hard time doing it. It gives me no results while it should give me one result. Any suggestions will be appreciated

Answer 1

find returns a cursor to the matching documents. So the query

db.B.find({ "name": /.*aco.*/ }, { "excel_template": 1, "_id": 0 })

will not return a single document but a cursor to the documents that match the query criteria above.

You can either use the distinct() method to return an array of excel_template values from documents that match the query above and use that in the other query as in:

var r = db.B.distinct("excel_template", { "name": /.*aco.*/ });
db.A.find({ "_id": { "$in": r } });

OR

---

MongoDB 3.2 and newer:

You can also use the aggregation framework where the $lookup pipeline will provide a functionality to join the two collections and run the query in a single operation

db.B.aggregate([
    { "$match": { "name": /.*aco.*/ } },
    {
        "$lookup": {
            "from": "A",
            "localField": "excel_template",
            "foreignField": "_id",
            "as": "bList"
        }
    }
])