MongoDB lookup with multiple nested levels

Performing a MongoDB $lookup with multiple nested levels involves using multiple $lookup stages and pipeline expressions to join data from multiple collections. The $lookup stage allows you to perform a left outer join between the current collection and another collection based on a field's value.

Let's consider a scenario with three collections: orders, products, and categories. Each order document contains a reference to a product, and each product document contains a reference to a category. We want to retrieve orders along with the nested product and category information.

Here's an example of how you can achieve this using MongoDB's aggregation framework:

javascriptCopy code
db.orders.aggregate([
  {
    $lookup: {
      from: "products",
      localField: "product_id",
      foreignField: "_id",
      as: "productData",
    },
  },
  {
    $unwind: "$productData",
  },
  {
    $lookup: {
      from: "categories",
      localField: "productData.category_id",
      foreignField: "_id",
      as: "productData.categoryData",
    },
  },
  {
    $unwind: "$productData.categoryData",
  },
  {
    $project: {
      _id: 1,
      order_number: 1,
      order_date: 1,
      productData: {
        _id: "$productData._id",
        product_name: "$productData.product_name",
        price: "$productData.price",
        categoryData: {
          _id: "$productData.categoryData._id",
          category_name: "$productData.categoryData.category_name",
          // Add other category fields as needed
        },
        // Add other product fields as needed
      },
    },
  },
]);

Explanation:

1. The first $lookup stage joins the orders collection with the products collection based on the product_id field in the orders collection and the _id field in the products collection. It creates a new array field productData that contains the product information.

2. The $unwind stage deconstructs the productData array, so each order gets a separate document for the joined product information.

3. The second $lookup stage joins the products collection with the categories collection based on the category_id field in the products collection and the _id field in the categories collection. It creates a new array field productData.categoryData that contains the category information.

4. The second $unwind stage deconstructs the productData.categoryData array, so each product gets a separate document for the joined category information.

5. The $project stage reshapes the output to retain only the desired fields and presents the nested structure for the product and category information.

Please adjust the collection names, field names, and nesting structure according to your data model. The provided example assumes that the orders collection has a field product_id that references the products collection, and the products collection has a field category_id that references the categories collection.