Mongo DB filter on other collection without projecting any fields from foreign collection

Question

User Collection

{
   "_id: : "123"
   "name" : "John Doe",
   "age"  : 40,
}

Audit collection

{
  "_id" : "456",
  "region": "IND"
  "userId" : 123
}

I need to perform aggregation on User collection where "region" is "IND" but don't want fields from foreign collection to be projected. What I tried so far is this a lookup like below

db.User.aggregate([
{
   $lookup:
     {
       from: "Audit",
       localField: "_id",
       foreignField: "userId",
       as: "auditTrail"
     }
},
{
    $unwind: "$auditTrail"
},
{
    $match : {
            "auditTrail.region": "IND"
        }
}, 
{
   $project : {"auditTrail.region": 0}
}
])

Other way is to use lookup with pipeline and not project the foreign fields

db.User.aggregate([
   {
      $lookup:
         {
           from: "Audit",
           let: { user_id: "$_id"},
           pipeline: [
              { $match:
                 { $expr:
                    { $and:
                       [
                         { $eq: [ "$userId",  "$$user_id" ] }
                       ]
                    }
                 }
              },
              { $project: { _id: 1} }
           ],
           as: "stockdata"
         }
    }
])

Both the collections mentioned here are simplified, in production can be a huge document with thousands of records in each collection where there can be match stage to filter fields on source collection in addition to filtering on foreign collection. Is there any better way to accomplish this?

Mohammad Faisal Mohammad Faisal · Accepted Answer · 2021-02-09T05:27:12

You can modify your 2nd Query by adding region condition inside $match of $lookup pipeline.

db.User.aggregate([
   {
      $lookup:
         {
           from: "Audit",
           let: { user_id: "$_id"},
           pipeline: [
              { $match:
                 { $expr:
                    { $and:
                       [
                         { $eq: [ "$userId", "$$user_id" ] },
                         { $eq: [ "$region", "IND" ] }  // region Condition added
                       ]
                    }
                 }
              },
              { $project: { _id: 1} }
           ],
           as: "stockdata"
         }
    }
])

Mongo DB filter on other collection without projecting any fields from foreign collection

1 Answers