Filter deep populeted field in MongoDB

Question

How to filter products by deep nested populated fields. catalogProduct is an ObjectId (ref to catalog product). category is an ObjectId inside catalogProduct (ref to categories). Categories is an array of category ids.

products = await StorageModel
                .find({"catalogProduct.category": {$in: categories }})
                .skip((page-1)*8)
                .limit(8)
                .populate({path: "catalogProduct", populate: {path: "category", select: "name"}})
                .select('-__v')
                .sort({_id: -1});

Answer 1

You'll need to do a $lookup on the catalogProduct collection so that you can access the catalogProduct data in the query.

Unfortunately that's only available when using Mongo Aggregation, however aggregation is very powerful and is perfect for this sort of thing. You could do something like this:

const products = await StorageModel.aggregate([
    { $lookup: {  // Replace the Catalog Product ID with the Catalog  Product
        from: "catalogProduct",
        localField: "catalogProduct",
        foreignField: "_id",
        as: "catalogProduct"
    } },
    { $lookup: {  // Replace the Category ID with the Category
        from: "categories",
        localField: "catalogProduct.category",
        foreignField: "_id",
        as: "catalogProduct.category"
    } },
    { $addFields: {  // Replace the Category with its name
        "catalogProduct.category": "$catalogProduct.category.name"   
    } },
    { $match: {
        "catalogProduct.category": { $in: categories }
    } },
    { $sort: { _id: -1 } },
    { $skip: (page - 1) * 8 },
    { $limit: 8 }
]);

Ideally you wouldn't do the $lookup until you've paginated the results (using $skip and $limit), but in this case it makes sense to do the $lookup first. Make sure you've got an index on catalogProduct._id and categories._id to optimize the query.

For more info on $lookup, look at this article. For more info on Mongo Aggregation, look at this article.