node.jsmongodbmongodb-queryaggregation-frameworkloopbackjs

Find/aggregate from another collection based on its _id in mongodb


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?


Solution

  • 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