$lookup two levels

Question

I am new to the MongoDB and NoSQL and I am trying to get a feeling (for a quite limited period of time) if it would do the job for a quick demo project for which we already have the data as json files. I am trying to test different query scenarios with Mongo and there is one scenario where I am stuck. Imagine I have 3 mongodb collections representing Products with Categories and Sub-Categories. Each project may have many categories and each category may have many sub-categories. Here is a simple example of the data:

Products

{ "id" : "1", "name" : "product1", "categories": ["cat1_id", "cat2_id"] }
{ "id" : "2", "name" : "product2", "categories": ["cat1_id"]}
{ "id" : "3", "name" : "product3", "categories": ["cat3_id"}

Categories

{ "id" : "cat1_id", "name" : "cat1", "sub_categories": ["subcat1_id", "scat2_id"]}
{ "id" : "cat2_id", "name" : "cat2", "sub_categories": ["subcat3_id"]}
{ "id" : "cat3_id", "name" : "cat3", "sub_categories": ["subcat1_id"]}

Sub-Categories

{ "id" : "subcat1_id", "name" : "sub cat1"}
{ "id" : "subcat2_id", "name" : "sub cat2"}
{ "id" : "subcat3_id", "name" : "sub cat3"}

I am trying to lookup the 3 collections so I can get the data for the categories and the sub categories into the product documents:

{ 
  "id" : "1", 
  "name" : "product1", 
  "categories": [{
    "id": "cat1_id", 
    "name": "cat1", 
    "sub_categories": [
      { "id" : "subcat1_id", "name" : "sub cat1"}
      { "id" : "subcat2_id", "name" : "sub cat2"}
    ]
  }, {
    "id": "cat2_id", 
    "name": "cat2", 
    "sub_categories": [
      { "id" : "subcat1_id", "name" : "sub cat1"}
    ]
  }]
}

I am doing 2 lookups and tried multiple options with unwind but if the product has 2 categories I am always getting the sub-categories details only for the first category - the details for the sub-categories of the second category are gone and even for category 2 I am getting the sub categories of category 1.

Here is the query and the result I got:

Query:

db.product.aggregate([
    $lookup: {
        from: "category",
        localField: "categories",
        foreignField: "id",
        as: "categories_obj"
    },
    $unwind: "categories",
    $unwind: "categories_obj.sub_categories",
    $lookup: {
        from: "sub_category",
        localField: "categories_obj.sub_categories",
        foreignField: "id",
        as: "sub_category_obj"
    }
])

Result:

{
  "id" : "1", 
  "name" : "product1",
  "categories": "cat1_id"
  "categories_obj" : [
     { "id" : "cat1_id", "name" : "cat1", "sub_categories": ["subcat1_id", 
     "scat2_id"]}
     { "id" : "cat2_id", "name" : "cat2", "sub_categories": ["subcat3_id"]}
  ],
  "sub_category_obj": [
     { "id" : "subcat1_id", "name" : "sub cat1"}
     { "id" : "subcat2_id", "name" : "sub cat2"}
  ]
}

{
  "id" : "1", 
  "name" : "product1",
  "categories": "cat2_id"
  "categories_obj" : [
     { "id" : "cat1_id", "name" : "cat1", "sub_categories": ["subcat1_id", 
     "scat2_id"]}
     { "id" : "cat2_id", "name" : "cat2", "sub_categories": ["subcat3_id"]}
  ],
  "sub_category_obj": [
     { "id" : "subcat1_id", "name" : "sub cat1"}
     { "id" : "subcat2_id", "name" : "sub cat2"}
  ]
}

As seen from the result both cat1 and cat2 displays the details for sub-categories 1 and 2 (belongs to cat1), but sub-category 3 is missing (belongs to cat2). Once I am able to get the details for all sub-categories, I will try to $group, $project, etc. to get the desired data format described above, but I don't know how to get all the sub-categories details.

Please, help me with this. I hope it is possible with MongoDB.
A query to be able to get the data in the described format will be welcome as well!
Thanks!

Answer 1

You can use below aggregation with mongodb 3.6 and above

Project.aggregate([
  { "$lookup": {
    "from": Category.collection.name,
    "let": { "categories": "$categories" },
    "pipeline": [
      { "$match": { "$expr": { "$in": [ "$_id", "$$categories" ] } } },
      { "$lookup": {
        "from": SubCategory.collection.name,
        "let": { "sub_categories": "$sub_categories" },
        "pipeline": [
          { "$match": { "$expr": { "$in": [ "$_id", "$$sub_categories" ] } } }
        ],
        "as": "sub_categories"
      }}
    ],
    "as": "categories"
  }}
])