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.

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

Share on Facebook

Tweet

Follow us

Save