Mongo db using result of query in another query using $in

Question

I have the following model in mongo db:

User collection

{
_id:12345,
name:"Joe",
age:15,
}

Addresses collection

{
_id:7663,
userId:12345,
Street:"xyz",
number:"1235",
city:"New York",
state:"NY"
}

Now I want to get all the addresses of users above the age of 20. What I thought was to query all the ids of users above 20 and with the result of this query use the $in operator to find the addresses.

My question is, is there a way to turn this into one query? Is there a better way to query this? (obs: this is just an example, with my problem I cannot embed addresses into users)

Answer 1

Use the aggregation framework where the $lookup pipeline stage provides the functionality to join the two collections:

db.user.aggregate([
    { "$match": { "age": { "$gt": 20 } } },
    {
        "$lookup": {
            "from": "addresses",
            "localField": "_id",
            "foreignField": "userId",
            "as": "address"
        }
    }
])

The above will create a new array field called address (as specified in the $lookup as option) and this contains the matching documents from the from collection addresses. If the specified name already exists in the input document, the existing field is overwritten.

Answer 2

In Mongo shell you can use the result of one query in another. For example:

use database  // the name of your database
db.coll1.find({_id:{$nin:db.coll2.distinct("coll1_id")}})

Here collection coll1 contains an _id field. Then you can check for any ids that are not in collection coll2's list of the coll1_id field. So this is a way to clean up two tables if you have records in coll1 which have no reference via the coll1_id field in coll2.

Another approach doing the same thing:

use database  // the name of your database
temp = db.coll2.distinct("coll1_id");
db.coll1.find({_id:{$nin:temp}})

The first example does it in one command, the second does it in two, but the concept is the same. Using results from one query in another. Many different ways to do this. Also, the .toArray() method can be useful to create arrays if you're doing more than just using distinct().