Double nested aggregation

Working with Data
#1

Hi guys I have 3 collections like below. I need to aggregate from showcase collection and put products inside and for each product I want to match every discount.

Showcase Collection

{
	"_id": {
		"$oid": "61f16fdc62c4d2deead39216"
	},
	"title": "Exclusive Offer",
	"products": [{
		"$oid": "61ee8df32bd310de954a2712"
	}, {
		"$oid": "61ee8da12bd310de954a2708"
	}, {
		"$oid": "61ee8e162bd310de954a2718"
	}],
	"categories": [],
	"__v": 0
}

Product Collection

{
	"_id": {
		"$oid": "61ee8da12bd310de954a2708"
	},
	"name": "Sprite Can",
	"shortDescription": "325ml, Price",
	"description": "",
	"price": 4.99,
	"images": [{
		"$oid": "61ee8c992bd310de954a26f4"
	}],
	"labels": [{
		"$oid": "61ee8d732bd310de954a2704"
	}],
	"freeShipping": true,
	"stock": {
		"available": true,
		"total": 42
	},
	"category": {
		"$oid": "61ee8bb32bd310de954a26ef"
	},
	"status": true,
	"nutritions": {
		"Fat": "120g",
		"Iron": "0.3mg"
	},
	"__v": 0
}

Discount Collection

{
	"_id": {
		"$oid": "61f3c19204fcf9025febf148"
	},
	"productId": {
		"$oid": "61ee8da12bd310de954a2708"
	},
	"discountPrice": 3.99,
	"__v": 0
}

Response Needed

{
	"_id": {
		"$oid": "61f16fdc62c4d2deead39216"
	},
	"title": "Exclusive Offer",
	"products": [{
	"_id": {
		"$oid": "61ee8da12bd310de954a2708"
	},
	"name": "Sprite Can",
	"shortDescription": "325ml, Price",
	"description": "",
	"price": 4.99,
	"images": [{
		"$oid": "61ee8c992bd310de954a26f4"
	}],
	"labels": [{
		"$oid": "61ee8d732bd310de954a2704"
	}],
	"freeShipping": true,
	"stock": {
		"available": true,
		"total": 42
	},
	"category": {
		"$oid": "61ee8bb32bd310de954a26ef"
	},
	"status": true,
	"nutritions": {
		"Fat": "120g",
		"Iron": "0.3mg"
	},
	"__v": 0
        "discountPrice": 3.99 //here
}],
	"categories": [],
	"__v": 0
}
#2

What I have so far is below but I think I am having issues with nested _ids

            {
                $lookup: {
                    from: 'products',
                    let: { products: '$products' },
                    pipeline: [
                        { $match: { $expr: { $in: ['$_id', '$$products'] } } },
                        {
                            $lookup: {
                                from: 'discounts',
                                let: { discounts: '$discounts' },
                                pipeline: [{ $match: { $expr: { $in: ['$_id', '$$discounts'] } } }],
                                as: 'discounts'
                            }
                        }
                    ],
                    as: 'products'
                }
            }
#3

Someone on stackoverflow helped me.

db.Showcase.aggregate([
  {
    $lookup: {
      from: "Product",
      let: { products: "$products" },
      pipeline: [
        {
          $match: { $expr: { $in: [ "$_id", "$$products" ] } }
        },
        {
          $lookup: {
            from: "Discount",
            localField: "_id",
            foreignField: "productId",
            as: "discountPrice"
          }
        },        
        {
          $set: { "discountPrice": { $first: "$discountPrice.discountPrice"} }
        }
      ],
      as: "products"
    }
  }
])
#4

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.