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
$lookup
stage adds a new array field whose elements are the matching documents from the “joined” collection. The$lookup
stage passes these reshaped documents to the next stage.
Here is the complete code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 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.
1 2 3 4 5 6 | $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:
1 | $unwind:"$post_category" |
Also notice the $match stage which holds conditions to match
1 2 3 4 5 6 7 8 | $match:{ "post_category.enable":{ $ne:false }, status:{ $eq: 'active' } } |
status is another field in my posts table and I want ‘active’ posts only