mongoose - MongoDB - Join on multiple condition

Question

For the below query

SQL:

SELECT * FROM TableA as A LEFT JOIN TableB as B ON (A.id = B.id AND A.name =B.name)

Mongo:

TableA.aggregate([
    {"$lookup": {
                "from": "TableB",
                "localField": "_id",
                "foreignField": "_id",
                "as": "b"
                }},
                { "$match": {  "name":"b.name" } }

])

For the above query, required output is not coming.

question from:https://stackoverflow.com/questions/65880825/mongodb-join-on-multiple-condition

Answer 1

You could refer this sample playground

db.orders.aggregate([
  {
    "$lookup": {
      from: "inventory",
      let: {
        id: "$_id",
        item: "$item"
      },
      pipeline: [
        {
          "$match": {
            $expr: {
              "$eq": [
                "$_id",
                "$$id"
              ],
              "$eq": [
                "$sku",
                "$$item"
              ]
            }
          }
        }
      ],
      as: "outputs"
    }
  }
])

Reference