Merge 3 collections in nested documents

Hi there,
I have three collections in a database:

  1. distributors
  2. fruits
  3. tools

The data structure is available at: Mongo playground

  • 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 the deeper levels in documents.

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)

It would be nice to share exactly what you tried and how it failed. This way we save time NOT investigating in a direction that you already know it fails.