1st query:
db.a.find({
"type": ObjectId("50ed90f5a70defef23000002"),
"config.name": "alpha"
}, {
"user": 1
})
1st query result:
{ "_id" : ObjectId("5684035c91b4693b39547591"), "user" : ObjectId("54ba80ac2735eea200623612") }
2st query:
db.users.find({
"_id": ObjectId("54ba80ac2735eea200623612")
}, {
"lastname": 1
})
can I combine these two queries into one query? 2st query uses 1st query result's "user" value ObjectId("54ba80ac2735eea200623612")
You can use an aggregation with a $match to match your condition and $lookup to map your local field user to your usercollection _id field :
db.a.aggregate(
[{
$match: {
"type": ObjectId("50ed90f5a70defef23000002"),
"config.name": "alpha"
}
}, {
$lookup: {
from: "users",
localField: "user",
foreignField: "_id",
as: "users"
}
}, {
$unwind: "$users"
}]);
In Javascript, with mongoose for example you can do this with :
YourModel.aggregate(
[{
$match: {
"type": ObjectId("50ed90f5a70defef23000002"),
"config.name": "alpha"
}
}, {
$lookup: {
from: "users",
localField: "user",
foreignField: "_id",
as: "users"
}
}, {
$unwind: "$users"
}],
function(err, result) {
console.log("lastname : " + result.users.lastname);
});
Since MongoDB doesn't support joins, this can't be conbined into one query.
You can do this directly from the mongo shell very easily by firing first query, storing the result in a variable and then using it to fire second query.
var userDoc = db.a.findOne({"type" : ObjectId("50ed90f5a70defef23000002"),"config.name":"alpha" },{"user":1});
db.users.find({"_id" : userDoc.user},{"lastname":1})
If your first query returns multiple results, then
var cursor = db.a.find({"type" : ObjectId("50ed90f5a70defef23000002"),"config.name":"alpha" },{"user":1});
cursor.forEach(function (userDoc) {
var user = db.users.findOne({"_id" : userDoc.user},{"lastname":1});
printjson(user);
})
Note: MongoDB supports left outer join using $lookup in aggregation pipeline since version 3.2
but in this case, that seems to be an overkill.
userDoc.user seems not work, "Can't canonicalize query: BadValue cannot compare to undefined"
find() returns a cursor and not the actual document, so if only one document is expected output, findOne would be more appropriate. Updated the answer accordingly.
$lookupbut if this is a common operation It would be worth re-modeling your document for your given usage.