Lookup/ multiple collections for a single deeply nested document

Question

I have three collections in a database:

1. distributors
2. fruits
3. tools

I need to chain/ lookup the collections fruits and tools for a single document from the distributor collection.

The number of documents in fruits and tools is quite limited in practice (max 5-10 each). So performance-wise i guess that should be fine.

The data structure is available at: https://mongoplayground.net/p/AkCarFmdJh1

db={
  "distributors": [
    {
      "name": "Distributor 1",
      // more attributes here
      "children": [
        {
          "name": "Country 1",
          // more attributes here
          "children": [
            {
              "name": "City 1",
              // more attributes here
              "children": [
                {
                  "name": "Shop 1",
                  // more attributes here
                  "children": [
                    {
                      "name": "Fruit 1",
                      // more attributes here
                      "child": "f1"
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    }
  ],
  "fruits": [
    {
      "uid": "f1",
      "name": "Fruit 1 Detail",
      "variants": [
        {
          "uid": "v1",
          "name": "Variant 1",
          "tools": [
            "t1",
            "t2"
          ]
        },
        {
          "uid": "v2",
          "name": "Variant 2",
          "tools": [
            "t3",
            "t4"
          ]
        }
      ]
    },
  ],
  "tools": [
    {
      "uid": "t1",
      "name": "Tool 1"
    },
    {
      "uid": "t2",
      "name": "Tool 2"
    },
    {
      "uid": "t3",
      "name": "Tool 3"
    },
    {
      "uid": "t4",
      "name": "Tool 4"
    }
  ]
}

• The distributors collection contains deeply nested documents. On the last level, the child “f1” UID should be replaced with the respective document from the collection “fruits”.

• Collection “fruits” contains in the field “tools” UIDs fro the collection “tools”. They also should be replaced with the respective documents from the collection “tools”.

• In the end, I need an aggregation query that results in chaining all three collections into one.

I tried to solve this with $lookup but I am not sure how i can reach/ chain the deeper levels in documents.

GOAL:

{
"name": "Distributor 1",
"children": [
    {
        "name": "Country 1",
        "children": [
            {
                "name": "City 1",
                "children": [
                    {
                        "name": "Shop 1",
                        "children": [
                            {
                                "name": "Fruit 1",
                                "child": {
                                    "uid": "f1",
                                    "name": "Fruit 1 Detail",
                                    "variants": [
                                        {
                                            "uid": "v1",
                                            "name": "Variant 1",
                                            "tools": [
                                                { "uid": "t1", "name": "Tool 1" },
                                                { "uid": "t2", "name": "Tool 2" },
                                            ],
                                        },
                                        {
                                            "uid": "v2",
                                            "name": "Variant 2",
                                            "tools": [
                                                { "uid": "t3", "name": "Tool 3" },
                                                { "uid": "t4", "name": "Tool 4" },
                                            ],
                                        },
                                    ],
                                },
                            }
                        ],
                    }
                ],
            }
        ],
    }
],

}

Thanks for your help!

(I tried different other approaches to model the distributor collection more flat but i could not find a solution that would be easier to handle in the application, unfortunately. Any suggestion is welcome)

Answer 1

To continue our previous discussion, one option is:

db.distributors.aggregate([
  {
    "$match": {
      "uid": "d1"
    }
  },
  {
    $lookup: {
      from: "fruits",
      localField: "children.children.children.children.child",
      foreignField: "uid",
      as: "fruits"
    }
  },
  {
    $lookup: {
      from: "tools",
      localField: "fruits.variants.tools",
      foreignField: "uid",
      as: "tools"
    }
  },
  {
    $set: {
      tools: "$$REMOVE",
      fruits: {
        $map: {
          input: "$fruits",
          as: "f",
          in: {
            $mergeObjects: [
              "$$f",
              {
                variants: {
                  $map: {
                    input: "$$f.variants",
                    as: "v",
                    in: {
                      $mergeObjects: [
                        "$$v",
                        {
                          tools: {
                            $map: {
                              input: "$$v.tools",
                              as: "t",
                              in: {
                                $arrayElemAt: [
                                  "$tools",
                                  {
                                    $indexOfArray: [
                                      "$tools.uid",
                                      "$$t"
                                    ]
                                  }
                                ]
                              }
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      countriesA: {
        $map: {
          input: "$children",
          as: "country",
          in: {
            $mergeObjects: [
              "$$country",
              {
                children: {
                  $map: {
                    input: "$$country.children",
                    as: "city",
                    in: {
                      $mergeObjects: [
                        "$$city",
                        {
                          children: {
                            $map: {
                              input: "$$city.children",
                              as: "shop",
                              in: {
                                $mergeObjects: [
                                  "$$shop",
                                  {
                                    children: {
                                      $map: {
                                        input: "$$shop.children",
                                        as: "fruit",
                                        in: {
                                          $mergeObjects: [
                                            "$$fruit",
                                            {
                                              child: {
                                                $arrayElemAt: [
                                                  "$fruits",
                                                  {
                                                    $indexOfArray: [
                                                      "$fruits.uid",
                                                      "$$fruit.child"
                                                    ]
                                                  }
                                                ]
                                              }
                                            }
                                          ]
                                        }
                                      }
                                    }
                                  }
                                ]
                              }
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

See How it works on the mongoDB playground