PetTable {
  _id: ObjectId,
  bdTable: string,
  status: ObjectId(ref=Status)
}

Status {
    _id : ObjectId,
    statusCode : number, --> exple: (1, 2, ...7)
    petId : ObjectId(ref=PetTable),
}

i want to find all objects of PetTable with bdTable = "zzzz" and status =5;

I have no idea how to go about it?

CodePudding user response：

Run an aggregate operation with $lookup pipeline stage as follows:

db.PetTable.aggregate([
  { $match: { bdTable: 'zzzz' } },
  { $lookup: {
    from: 'Status',
    let: { status_id: '$status' },
    pipeline: [
      { $match: {
        $expr: { 
          $and: [
            { $eq: [ 5, '$statusCode' ] },
            { $eq: [ '$_id', '$$status_id' ] }
          ] 
        }
      } }
    ],
    as: 'status'
  } },
  { $match: { 
    $expr: { $gt: [ { $size: '$status' }, 0] }
  } },
]);

Mongo Playground