I have two tables, posts and categories. In posts table there is a field categoryId. I want that only those posts are shown category of which is in enabled state, i.e. for which categories.enabled = true.
To achieve this in mongodb there is an Aggregation Pipeline Stage named $lookup. This stage was included in mongodb 3.2. According to mongo documentation $lookup:
Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing. To each input document, the
$lookupstage adds a new array field whose elements are the matching documents from the “joined” collection. The$lookupstage passes these reshaped documents to the next stage.
Here is the complete code.
var Post = [...load posts model here]
query = Post.aggregate([
{
$lookup: {
from:"categories",
localField:"categoryId",
foreignField:"_id",
as:"post_category"
},
},
{
$unwind:"$post_category"
},
{
$match:{
"post_category.enable":{
$ne:false
},
status:{
$eq: criteria.status
}
}
},
{
$limit : limit
},
{$sort:{}},
{$project:{}}
]);Though it is quite self explanatory lets have a close look at the $lookup section i.e to clear any doubt.
$lookup: {
from:"categories",
localField:"categoryId",
foreignField:"_id",
as:"post_category"
}from: is the name of foreign table i.e. categories. Remember posts table has a field which stores category id in form of categoryId. Hence:,
localField: holds the name of posts field i.e. categoryId
foreignField: name of the primary field in categories table. _id in our case.
as: this acts as an alias to hold join object. Since I don’t need matched records from categories table I do $unwind them so that my results object does not hold unnecessary data. See the unwind call above:
$unwind:"$post_category"
Also notice the $match stage which holds conditions to match
$match:{
"post_category.enable":{
$ne:false
},
status:{
$eq: 'active'
}
}status is another field in my posts table and I want ‘active’ posts only
