mongodb aggregation with filter options-CodePudding

I have two collections where I'm trying to do an aggregation query with filter options. I have looked online but I couldn't find solution for this.

Col 1

[
  {
    _id: ObjectId('st_123'),
    stud_num: 123,
    school: ObjectId('sc_123'),
    gender: 'M'
  },
  {
    _id: ObjectId('st_234'),
    stud_num: 123,
    school: ObjectId('sc_234'),
    gender: 'F'
  },
  {
    _id: ObjectId('st_345'),
    stud_num: 123,
    school: ObjectId('sc_345'),
    gender: 'M'
  }
]

Col 2

[
  {
    _id: ObjectId('f_123'),
    stud_health_id: ObjectId('st_123'),
    schoolYear: ObjectId('sy123')
  },
  {
    _id: ObjectId('f_234'),
    stud_health_id: ObjectId('st_234'),
    schoolYear: ObjectId('sy234')
  },
  {
    _id: ObjectId('f_345'),
    stud_health_id: ObjectId('st_890'),
    schoolYear: ObjectId('sy234')
  },
  {
    _id: ObjectId('f_456'),
    stud_health_id: ObjectId('st_345'),
    schoolYear: ObjectId('sy345')
  }
]

I am trying to filter the records from collection 1 which doesn't have entry in collection 2 with extra params.

If I send {schoolYear: ObjectID('sy234)} then it should return the first and third document of collection 1 because for that year those two students doesn't have record.

CodePudding user response：

One option is using $lookup and $match:

db.col1.aggregate([
  {$lookup: {
      from: "col2",
      as: "col2",
      let: {schoolYear: "sy234", stud_id: "$_id"},
      pipeline: [
        {$match: {$expr: {
              $and: [
                {$eq: ["$schoolYear", "$$schoolYear"]},
                {$eq: ["$stud_health_id", "$$stud_id"]}
              ]
            }
          }
        }
      ]
    }
  },
  {$match: {"col2.0": {$exists: false}}},
  {$unset: "col2"}
])

See how it works on the playground example