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?

CodePudding user response：

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