MongoDB: Optimal joining of one to many relationship

Question

Here is a hypothetical case of orders and products.

1. 'products' collection

[
  {
    "_id": "61c53eb76eb2dc65de621bd0",
    "name": "Product 1",
    "price": 80
  },
  {
    "_id": "61c53efca0a306c3f1160754",
    "name": "Product 2",
    "price": 10
  },
  ... // truncated
]

2. 'orders' collection:

[
  {
    "_id": "61c53fb7dca0579de038cea8", // order id

    "products": [
      {
        "_id": "61c53eb76eb2dc65de621bd0", // references products._id
        "quantity": 1
      },
      {
        "_id": "61c53efca0a306c3f1160754",
        "quantity": 2
      },
    ]
  }
]

As you can see, an order owns a list of product ids. When I pull an order's details I also need the product details combined like so:

{
    _id: ObjectId("61c53fb7dca0579de038cea8"),
    products: [
        {
            _id: ObjectId("61c53eb76eb2dc65de621bd0"),
            quantity: 1,
            name: 'Product 1',
            price: 80
        },
        {
            _id: ObjectId("61c53efca0a306c3f1160754"),
            quantity: 2,
            name: 'Product 2',
            price: 10
        },
        ... // truncated
    ]
}

Here is the aggregation pipleline I came up with:

db.orders.aggregate([

  {
    $match: {_id: ObjectId('61c53fb7dca0579de038cea8')}
  },

  {
    $unwind: {
      path: "$products"
    }
  },

  {
    $lookup: {
      from: 'products',
      localField: 'products._id',
      foreignField: '_id',
      as: 'productDetail'
    }
  },

  {
    $unwind: {
      path: "$productDetail"
    }
  },

 {
   $group: {
     _id: "$_id",
     products: {
       $push: {$mergeObjects: ["$products", "$productDetail"]}
     }
   }
 }
])

Given how the data is organized I'm doubting if the pipeline stages are optimal and could do better (possibility of reducing the number of stages, etc.). Any suggestions?

Answer 1

As already mentioned in comments the design is poor. You can avoid multiple $unwind and $group, usually the performance should be better with this:

db.orders.aggregate([
  { $match: { _id: "61c53fb7dca0579de038cea8" } },
  {
    $lookup: {
      from: "products",
      localField: "products._id",
      foreignField: "_id",
      as: "productDetail"
    }
  },
  {
    $project: {
      products: {
        $map: {
          input: "$products",
          as: "product",
          in: {
            $mergeObjects: [
              "$$product",
              {
                $first: {
                  $filter: {
                    input: "$productDetail",
                    cond: { $eq: [ "$$this._id", "$$product._id" ] }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Mongo Playground