Home:ALL Converter>MongoDB $lookup: Multiple Join Conditions on field in array

MongoDB $lookup: Multiple Join Conditions on field in array

Ask Time:2018-01-18T23:56:05         Author:peterschrott

Json Formatter

I have an issue with joining documents in mongo based on conditions on a nested field in an array. Basically, I want to filter the documents of the foreign collection.

I was following the example on the official MongoDB documentation for Multiple Join Conditions with $lookup and extended it to fit my requirements.

Testdata (Note, the warehouse documents are extended by the field foo):

db.orders.insert([
  { "_id" : 1, "item" : "almonds", "price" : 12, "ordered" : 2 },
  { "_id" : 2, "item" : "pecans", "price" : 20, "ordered" : 1 },
  { "_id" : 3, "item" : "cookies", "price" : 10, "ordered" : 60 }
])

db.warehouses.insert([
  { "_id" : 1, "stock_item" : "almonds", warehouse: "A", "instock" : 120, 'foo': [{ 'bar': 1 }] },
  { "_id" : 2, "stock_item" : "pecans", warehouse: "A", "instock" : 80, 'foo': [{ 'bar': 1 }] },
  { "_id" : 3, "stock_item" : "almonds", warehouse: "B", "instock" : 60, 'foo': [{ 'bar': 1 }] },
  { "_id" : 6, "stock_item" : "almonds", warehouse: "A", "instock" : 61, 'foo': [{ 'bar': 2 }] },
  { "_id" : 4, "stock_item" : "cookies", warehouse: "B", "instock" : 40, 'foo': [{ 'bar': 1 }] },
  { "_id" : 5, "stock_item" : "cookies", warehouse: "A", "instock" : 80, 'foo': [{ 'bar': 1 }] }
])

On the lookup, I want to add the additional condition foo.bar: 1:

db.orders.aggregate([
  {
    $lookup: {
      from: "warehouses",
      let: { order_item: "$item", order_qty: "$ordered" },
      pipeline: [{ 
        $match: { 
          $expr: { 
            $and: [
              { $eq: [ "$stock_item",  "$$order_item" ] },
              { $gte: [ "$instock", "$$order_qty" ] },
              { $eq: [ "$foo.bar", 1 ] }
            ]
          }
        }
      }, { 
        $project: { stock_item: 0, _id: 0 } 
      }],
      as: "stockdata"
    }
  }
])

Unfortunately this extra condition does not work. Furthermore not a sigle document is returned by the lookup.

Can someone point me in the right direction? I know the problem can also be done by a pipeline using unwind, filter and group.

Thanks!

Author:peterschrott,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/48325359/mongodb-lookup-multiple-join-conditions-on-field-in-array
yy