Watch keynotes and sessions from MongoDB.live, our virtual developer conference.

$lookup aggregation issue with Let

Hello Devs,

Today i had a bad issue.

I have this query, look at Lookup from inventory, if i use hardcoded data inside query done, with let operator this not work ( no take results )

db.shopping_cart.aggregate(
    {"$match" : {"userId" : "2"}},
    {"$unwind" : "$items"}, 
    {"$lookup" : {"from" : "variants", "localField" : "items.variant", "foreignField" : "_id", "as" : "items.variantObject"},
    {"$unwind" : "$items.variantObject"},
    {"$lookup" : {"from" : "inventory", "let" :{"shop_id" : "$shopId", "variant_id" : "$items.variant"}, 
            pipeline: [ 
                { $match: { "shop" : "$$shop_id", "variants" : {$elemMatch : {"variant" : "$$variant_id"}}}},
                { $unwind: "$variants" }, 
                { $match: {"variants.variant" : "$$variant_id"},
                { $project : {"shop" : "$$shop_id", "shopId" : "shop",  "variant" : "$$variant_id", "price" : "$variants.price", "discount" : "$variants.discount", "sizes" : "$variants.sizes", "quantity" : "$variants.quantity"}}
            ],
                as : "items.inventory"
    }
    }
)

Data To replicate - inventory:

{
	"_id" : "5ec8d2924688d8310b909eee",
	"product" : "5ec43ac6bbe7852b815fb844",
	"shop" : 13,
	"version" : 1,
	"variants" : [
		{
			"variant" : "5ec43ac6bbe7852b815fb845",
			"quantity" : 22,
			"intSku" : "Iphone11_bianco64",
			"price" : 840,
			"iva" : 22,
			"discount" : {
				"salePrice" : 722,
				"saleEndTime" : "2020-05-31T07:36:00.000Z"
			},
			"deliverable" : true,
			"sizes" : [ ],
			"createDate" : "2020-05-23T07:36:50.557Z"
		}
	]
}

Data To replicate - variants:

{
  "_id": "5ec43ac6bbe7852b815fb845",
  "ean": null,
  "product": "5ec43ac6bbe7852b815fb844",
  "name": "Iphone 11 - touchscreen 5.8 \" 64 GB IOS Bianco ",
  "attributes": [
    {
      "attribute": "5e8d937ffa3fd358af0a20d4",
      "value": "touchscreen",
      "multiValues": []
    },
    {
      "attribute": "5ea2a5b3316b10162e706963",
      "value": "5.8",
      "multiValues": []
    },
    {
      "attribute": "5e8d940cfa3fd36d2256c5d3",
      "value": "64",
      "multiValues": []
    },
    {
      "attribute": "5e8d9412fa3fd358af0a20d6",
      "value": "IOS",
      "multiValues": []
    },
    {
      "attribute": "5e8d9404fa3fd317fc401d65",
      "value": "12",
      "multiValues": []
    },
    {
      "attribute": "5ea150e497a2ed5e54536789",
      "multiValues": [
        "5ea15819fb654b46605436b3",
        "5ea15819fb654b46605436b4",
        "5ea15819fb654b46605436b5"
      ]
    },
    {
      "attribute": "5e8efe4ffa3fd36c9e055103",
      "multiValues": [
        "5ea2ba77316b10162e706964",
        "5ea2ba77316b10162e706965",
        "5ea2ba77316b10162e706966"
      ]
    },
    {
      "attribute": "5e88bbf6fa3fd3294d252992",
      "value": "Bianco",
      "multiValues": []
    }
  ],
  "images": [
    {
      "imageLink": "0f651f533b2342889ee8480956181eb7"
    },
    {
      "imageLink": "16324710207d4514a37ed78c3ae05564"
    },
    {
      "imageLink": "5459cc2aa2fa496bba551252042f55a2"
    }
  ],
  "slugName": "iphone-11-touchscreen-5-8-64-gb-ios-bianco-108649"
}

Data To replicate - shopping_cart:

{
  "_id": "5ed111db474d4752a52d5ac0",
  "shopId": 13,
  "userId": "2",
  "items": [
    {
      "variant": "5ec43ac6bbe7852b815fb845",
      "quantity": 1
    }
  ]
}

Hello, @Antonio_Dell_Arte! Welcome to the community!

To be able to use let-variables in $lookup.pipeilne.$match stage, you need to use $expr operator inside $match stage, like this:

db.shopping_cart.aggregate([
  // ...
  {
    $lookup: {
      from : 'inventory',
      let: {
        shop_id: '$shopId',
        variant_id: '$items.variant',
      },
      pipeline: [
        {
          // put this $unwind before $match stage,
          // so $eq operators inside $match stage would work
          $unwind: '$variants', 
        },
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ['$shop', '$$shop_id'] },
                { $eq: ['$variants.variant', '$$variant_id'] },
              ],
            },
          },
        },
        { $project: { /* ... */ }},
      ],
      as : 'items.inventory',
    },
  },
]);