I have two collections with the following schema:
product_categories
interface Category {
_id: string;
name: string;
parentId: string;
}
This collection is hierarchical.
Example Data
- Components
- CPU
- GPU
- RAM
- MOBO
- Accessories
- Keyboard
- Mouse
- Headphone
products
interface Product {
_id: string;
name: string;
categoryId: string;
// ... other irrelevant fields ...
}
I want to filter products by categoryId
. For example: if I'm searching
with the _id
of Components category then the query should also find
products from sub-categories like "CPU", "GPU" etc.
I've managed to come up with a query to find sub-categories recursively with the
$graphLookup
stage.
db.product_categories.aggregate([
{ $match: { _id: "<parent-category-id-here>" } },
{
$graphLookup: {
as: "children",
startWith: "$_id",
connectFromField: "_id",
connectToField: "parentId",
from: "product_categories",
},
},
{ $unwind: "$children" },
{ $project: { _id: 1, childId: "$children._id" } },
]);
But, I don't know how to use it in the $match
stage of the following query.
db.products.aggregate([
{
$match: {
categoryId: {
$in: [
"<parent-category-id>",
"<sub-category-ids-from-the-$graphLookup-stage>",
],
},
},
},
// ... other stages
]);
I know that, I can just fetch the subcategories before running second query but I'm trying to do it all in one go. Is it possible?
Please give me some hint about how can I proceed further from this step. Is there a better alternative solution to this problem?
Thanks in advance, I highly appreciate your time on SO .