How to get records filtered by a condition of another table field through join in Mongodb

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.

Though it is quite self explanatory lets have a close look at the $lookup section i.e to clear any doubt.

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:

Also notice the $match stage which holds conditions to match

status is another field in my posts table and I want ‘active’ posts only

Leave a Reply