Condition on Mongoose $Lookup

Question

I am trying to do Join in Mongoose.

I have two collections

1. Master Challenge

2. Challenge History

My Challenge History Contains a filed master_id which is a reference to _id of Master Challenge

Master Challenge Model

const mongoose = require('mongoose');

const masterChallenge = mongoose.Schema({
  title:{
    type:String,
  },
  descripton:{
    type:String,
  },
  challengeType:{
    type:Number,
  },
  
});

module.exports = mongoose.model('masterChallenge',masterChallenge);

Challenge History Model

const mongoose = require('mongoose');

const challangeHistory= mongoose.Schema({

  master_id:{
    type: mongoose.Schema.Types.ObjectId,
    ref:'masterchallenges'
  },
  host_user_id:{
    type: mongoose.Schema.Types.ObjectId,
    ref:'useraccounts'
  },
  compete_with_id:{
    type: mongoose.Schema.Types.ObjectId,
    ref:'useraccounts'
  },
  adjucator_id:{
    type: mongoose.Schema.Types.ObjectId,
    ref:'useraccounts'
  },
  status:{
    type:Number,
  },
  created_at:{
    type:Date,
  }
});

module.exports = mongoose.model('challangeHistory',challangeHistory);

id = 'user _id' (for below paragraph)

I want to perform join on by Master Challange to fetch only those records from Master Challenge which has host_user_id==id or compete_with_id==id or adjucator_id==id if my Master Challenge type is 1 else challenge status 0 then it should not perform any operation for host_user_id or compete_with_id or adjucator_id and return that document with Challenge 0.

My Query

findMasterOnHistory:async function(id){

let dbResponse = await MASTER_CHALLENGE.aggregate([
       {$lookup:{from:'challangehistories',localField:'_id',foreignField:'master_id',as:'challangehistories'}},
     ]);
   
return dbResponse;
}

I have tried lots of stuff that does not seems to work and I don't know how to add conditions on $lookup.

Answer 1

Here is answer to my question. I used Pipeline inside $lookUp

1. Saved _id of Master Challenge into master.
2. Performed $and to match challenge History's -> master_id to _id [ $$master ] of Challenge Master.
3. Performed $or inside $and to get data on base of host_user_id or compete_with_id or adjucator_id.

{
  from: 'challangehistories',
  let: {
               master:"$_id",
               
             },
   pipeline:[
                {
                  $match:{
                    $expr:{
                      $and:[
                        {$eq:["$$master","$master_id"]},
                        {$or:[
                            {
                              $or:[
                                  {$eq:["$host_user_id",ObjectId(id)]},
                                  {$eq:["$compete_with_id",ObjectId(id)]},
                                  {$eq:["$adjucator_id",ObjectId(id)]},
                                ]
                            }
                            
                          ]},
                      ],
                    },
                    
                    
                  }
                },
             ],
  as: 'challangehistories'
}

Answer 2

You can use pipeline in your $lookup stage:

For example your host_user_id stored in variable I call hostUserId.

let dbResponse = await MASTER_CHALLENGE.aggregate([{
                         $lookup: {
                            from: 'challangehistories',
                            'let': {masterId: '$_id'},
                            pipeline: [{
                              $and: [{
                                $eq: ['$master_id', '$$masterId']
                              }, {
                                $eq: ['$host_user_id', hostUserId]
                              }]
                            }],
                            as: 'challangehistories'
                         }
                       }])