Mongo lookup returning all values

Question

I have 2 collections as shown below:

branches
{
  _id: ..., 
  custId: "abc123",
  branchCode: "AA",
...other fields
}

branchHolidays
{
  _id: ..., 
  custId: "abc123", 
  holidayDate: ISODate("2019-06-01T00:00:00:0000"),
  holidayStatus: "PROCESSED",
..other fields
}

Need to get all branchHolidays with the custId available in branches collection along with the branchCode from branches collection. (branches.custId = branchHolidays.custId)

For the first part of join I tried the below query but I'm getting all the fields from branchHolidays collection.

db.getCollection('branchHolidays').aggregate([
{
  $lookup: {
    localField: "custId",
    from: "branches",
    foreignField: "custId",
    as: "holidays"
  }
},
$match: {  holidayStatus: "PROCESSED" }
])

The above query returns all the documents from branchHolidays collection. I'm new to mongo but I'm not able to figure out what the problem is. Have gone through most of the SO queries but haven't found anything which helped.

Note: There are multiple branchCodes mapped to 1 custId in branches collection.

Answer 1

The $lookup stage is similar to a left outer join. The sample aggregation should return all documents from the branchHolidays collection that have holidayStatus: "PROCESSED", and each document will have an added field holidays containing all documents from the branches collection that have the same custId. For those documents that do not match any braches, the holidays field will contain an empty array.

If you want to return only document that have matching branches, match on size, like:

  holidays:{$not:{$size:0}}

Also note placing the $match: { holidayStatus: "PROCESSED" } before the $lookup will avoid querying the branches collection for documents that would be eliminated, which may improve performance.