$lookup to iterate specific Fields

Question

I am trying to join specific fields from product while performing all request in addtocart. I don't know how to update lookup for this requirement. below I have updated my product collection and add to cart collection. Can anyone suggest me how to do this?

Add to Cart Collection:

 add_to_cart_products: [
      {
        product: ObjectId('5f059f8e0b4f3a5c41c6f54d'),
        product_quantity: 5,
        product_item: ObjectId('5f4dddaf8596c12de258df20'),
      },
    ],
    add_to_cart_product_total: 5,
    add_to_cart_discount: 50,

Product Collection:

  {
    _id: ObjectId('5f059f8e0b4f3a5c41c6f54d'),
    product_name: 'La Gioiosa Prosecco',
    product_description: 'No Description',
    product_overview: 'No Overview',
    product_items: [
      {
        product_item_number: '781239007465',
        product_price: 14.99,
        product_images: ['pro03655-781239007465-1.png'],
        product_item_is_active: true,
        _id: ObjectId('5f4dddaf8596c12de258f021'),
      },
      {
        product_item_number: '850651005110',
        product_price: 12.99,
        product_images: ['default.png'],
        product_item_is_active: true,
        _id: ObjectId('5f4dddaf8596c12de258df20'),
      },
    ],
    product_created_date: ISODate('2020-07-08T10:29:05.892Z'),
    product_status_is_active: true,
  },

In my AddToCart Schema Lookup

    lookups: [
      {
        from: 'shop_db_products',
        let: { productId: '$add_to_cart_products.product', purchaseQuantity: '$add_to_cart_products.product_quantity' },
        pipeline: [
          {
            $match: { $expr: { $in: ['$_id', '$$productId'] } },
          },
          {
            $lookup: {
              from: 'shop_db_products',
              localField: 'product_id',
              foreignField: '_id',
              as: 'product',
            },
          },
          {
            $project: {
              product_id: '$$productId',
              product_purchase_quantity: '$$purchaseQuantity',
              product_name: true,
            },
          },
          {
            $unwind: '$product_id',
          },
          {
            $unwind: '$product_purchase_quantity',
          },
        ],
        as: 'add_to_cart_products',
        model: 'ProductModel',
      },
    ],

Current Result:

     "add_to_cart_products": [
                {
                    "product_name": "Avery Coconut Porter",
                    "product_id": "5f059f8e0b4f3a5c41c6f54d",
                    "product_purchase_quantity": 5
                }
            ],
            "add_to_cart_product_total": 5,
            "add_to_cart_discount": 50,

Expected Result:

     "add_to_cart_products": [
                {
                    "product_name": "Avery Coconut Porter",
                    "product_id": "5f059f8e0b4f3a5c41c6f54d",
                    "product_item":[
                          "product_price": 12.99,
                           "product_images": ["default.png"],
                      ],
                    "product_purchase_quantity": 5
                }
            ],
            "add_to_cart_product_total": 5,
            "add_to_cart_discount": 50,

Answer 1

You can try,

• $unwind deconstruct add_to_cart_products array
• $lookup with shop_db_products collection pass required fields in let
  • $match productId equal condition
  • $project to show required fields, and get product item from array product_items using $filter to match product_item_id, and $reduct to get specific fields from product_item
• $unwind deconstruct add_to_cart_products array
• $group by _id and get specific fields and construct add_to_cart_products array

db.add_to_cart.aggregate([
  { $unwind: "$add_to_cart_products" },
  {
    $lookup: {
      from: "shop_db_products",
      let: {
        productId: "$add_to_cart_products.product",
        purchaseQuantity: "$add_to_cart_products.product_quantity",
        product_item_id: "$add_to_cart_products.product_item"
      },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$productId"] } } },
        {
          $project: {
            product_name: 1,
            product_id: "$_id",
            product_purchase_quantity: "$$purchaseQuantity",
            product_item: {
              $reduce: {
                input: {
                  $filter: {
                    input: "$product_items",
                    cond: { $eq: ["$$product_item_id", "$$this._id"] }
                  }
                },
                initialValue: {},
                in: {
                  product_price: "$$this.product_price",
                  product_images: "$$this.product_images"
                }
              }
            }
          }
        }
      ],
      as: "add_to_cart_products"
    }
  },
  { $unwind: "$add_to_cart_products" },
  {
    $group: {
      _id: "$_id",
      add_to_cart_discount: { $first: "$add_to_cart_discount" },
      add_to_cart_product_total: { $first: "$add_to_cart_product_total" },
      add_to_cart_products: { $push: "$add_to_cart_products" }
    }
  }
])

Playground