How to filter in mongoose aggregation while joining based on two equal fields stored in array of objects?

Question

Lets say I have two documents in products collection:

{
  _id: ObjectId("abc"), name: "Pizza",
  attributes: [
    {salePrice: 250, size: "Small"},
    {salePrice: 300, size: "Medium"},
    {salePrice: 350, size: "Large"}
  ]
},
{
  _id: ObjectId("xyz"), name: "Burger",
  attributes: [
    {salePrice: 100, size: "Regular"},
    {salePrice: 150, size: "Large"}
  ]
}

I have added products to cart so a document in cart collection looks like this:

{
  cartProducts: [
    { productId: ObjectId("xyz"), type: "Large", quantity: 5 },
    { productId: ObjectId("abc"), type: "Regular", quantity: 2 }, 
  ]
}

I want to retrieve cart details ( all cart items ) with details based on "type" field of cart.cartProducts meet the "size" field of product.attributes. So the output would look like this:

 cart items
{
  cartDetails: [
    {
      name: "Pizza", quantity: 5, salePrice: 350, productId: "xyz"
    },
    {
      name: "Burger", quantity: 2, salePrice: 100, productId: "abc"
    }
  ]
}

How can I do that using aggregate. What are the next stages?

Cart.aggregate([
  { $unwind: { path: "$cartProducts" } },
  {$lookup:{ from:"products", localField:"cartProducts.productId", foreignField:"_id", as:"products" } },
  { $unwind: { path: "$products", includeArrayIndex: "arrayIndex", preserveNullAndEmptyArrays: true } }
]);

Answer 1

• $lookup pass array of productId as localField
• now you have 2 arrays one is cartProducts and second products,
• $map to iterate loop of cartProducts
• $reduce to iterate loop of products and check condition if productId match then go to next step
• $reduce to iterate look of attributes and check condition of size and type
• $mergeObjects to merge objects with new fields

Cart.aggregate([
  {
    $lookup: {
      from: "products",
      localField: "cartProducts.productId",
      foreignField: "_id",
      as: "products"
    }
  },
  {
    $project: {
      cartProducts: {
        $map: {
          input: "$cartProducts",
          as: "p",
          in: {
            $mergeObjects: [
              "$$p",
              {
                $reduce: {
                  input: "$products",
                  initialValue: {},
                  in: {
                    $cond: [
                      { $eq: ["$$this._id", "$$p.productId"] },
                      {
                        name: "$$this.name",
                        salePrice: {
                          $reduce: {
                            input: "$$this.attributes",
                            initialValue: 0,
                            in: {
                              $cond: [
                                { $eq: ["$$this.size", "$$p.type"] },
                                "$$this.salePrice",
                                "$$value"
                              ]
                            }
                          }
                        }
                      },
                      "$$value"
                    ]
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground

---

Second option as per your try,

• $reduce to iterate loop of products.attributes array, check condition of type and size and return required fields,
• $mergeObjects to merge cartProducts with new fields

Cart.aggregate([
  { $unwind: "$cartProducts" },
  {
    $lookup: {
      from: "products",
      localField: "cartProducts.productId",
      foreignField: "_id",
      as: "products"
    }
  },
  { $unwind: "$products" },
  {
    $project: {
      cartProducts: {
        $mergeObjects: [
          "$cartProducts",
          {
            $reduce: {
              input: "$products.attributes",
              initialValue: {},
              in: {
                $cond: [
                  { $eq: ["$$this.sise", "$caartProducts.type"] },
                  {
                    name: "$products.name",
                    salePrice: "$$this.salePrice"
                  },
                  "$$value"
                ]
              }
            }
          }
        ]
      }
    }
  }
])

Playground