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.

Not totally clear on what you are trying to do. Since you are aggregating on `branchHolidays` and only filtering by `holidayStatus: "PROCESSED"`, you should be getting all documents in `branchHolidays` except those that are not tagged `PROCESSED`. Were you intending to do an additional filter based on the `$lookup` result? — Joe, May 10 '20 at 22:04
Shouldn't it be giving me only those documents where the branchHolidays.custId = branches.custId? Instead it's giving me all documents even if the custId is not present in branches collection. — Pi53, May 11 '20 at 03:22

score 3 · Accepted Answer · answered May 11 '20 at 05:12

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.

Mongo lookup returning all values

1 Answers1