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)

No, MongoDB doesn't support joins. http://stackoverflow.com/questions/4067197/mongodb-and-joins — JohnnyHK, May 13 '14 at 17:26
Yes I understand mongo db dosen't support joins. What I am trying to understand is, what is the best way to solve my problem as listed above. For example, is there any way I can grab all the ids of users and put it into another query? — Eduardo Mayer, May 13 '14 at 17:57
"Best way" questions aren't usually a good fit for SO, but if you update your question to include your code that uses `$in` to do this and you have a specific question about a problem with the approach, then that works better. — JohnnyHK, May 13 '14 at 18:03
The question is really to understand better mongo DB actually. I dont have a real life problem. But here goes: I know I can get the list of IDs from users like this: `db.users.find({age:{$gt:20}})` And I know I can query the list of addresses like this: `db.addresses.find({userId:{$in:[123,124,125,126]}})` I know I can project the first query to return only the Ids, the only thing I dont know is if I can use the result of the first query in the second one. If this is not possible, is there any other way I can query the second collection with a result from the first? — Eduardo Mayer, May 13 '14 at 18:31
Sure, see this question: http://stackoverflow.com/q/22318719/1259510 — JohnnyHK, May 13 '14 at 18:45

ciso · Answer 1 · 2017-03-04T23:38:46.990

18

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().

edited Mar 04 '17 at 23:38

answered Apr 15 '16 at 22:40

ciso

2,887
6
33
58

1

very helpful, this saves writing some programs to do a orphanage removal. – Jesse Zhuang Jul 09 '16 at 06:06
1

This is awesome and saves me a lot of pain for my desired functionality. Thanks! – MrNorm Jan 26 '17 at 08:38
1

Sweet! We were using `var temp` instead of just `temp` and it wasn't working for hours. Thanks for the clarification! – kbpontius Feb 29 '20 at 00:00

score 7 · Answer 2 · answered Feb 07 '17 at 11:42

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.

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

2 Answers2