MongoDB aggregation with $lookup only include (or project) some fields to return from query

Question

In mongo, after doing an aggregation with $lookup, I would like the request to return only some fields and not the whole document.

I have the following query :

db.somecollection.aggregate([{
    $lookup: {
        from: "campaigns",
        localField: "campId",
        foreignField: "_id",
        as: "campaign"
    }
}, {
    $unwind: "$campaign"
}, {
    $lookup: {
        from: "entities",
        localField: "campaign.clientid",
        foreignField: "_id",
        as: "campaign.client"
    }
}]);

This request will return me this :

{
"_id" : ObjectId("56cc7cd1cc2cf62803ebfdc7"),
"campId" : ObjectId("56c740e4479f46e402efda84"),
"articleId" : ObjectId("56c742c06094640103ba3843"),
"campaign" : {
    "_id" : ObjectId("56c740e4479f46e402efda84"),
    "clientid" : ObjectId("56c740b8479f46e402efda83"),
    "client" : [
        {
            "_id" : ObjectId("56c740b8479f46e402efda83"),
            "username" : "someusername",
            "shhh" : "somehashedpassword",
            "email" : "[email protected]",
        }
    ]
}

The request works well, but I would like to filter the fields in campaign.client to only get for example _id and username. Is there a way to do this in a MongoDB aggregate request?

Solution 1

Just to help others with this, @SiddhartAjmera has the right answer, I only needed to add double quotes for nested values like "campaign.clientid".

The final code should be:

db.somecollection.aggregate([
      {
        "$lookup": {
          "from": "campaigns",
          "localField": "campId",
          "foreignField": "_id",
          "as": "campaign"
        }
      },
      {
        "$unwind": "$campaign"
      },
      {
        "$lookup": {
          "from": "entities",
          "localField": "campaign.clientid",
          "foreignField": "_id",
          "as": "campaign.client"
        }
      },
      {
        "$project": {
          "_id": 1,
          "campId": 1,
          "articleId": 1,
          "campaign._id": 1,
          "campaign.clientid": 1,
          "campaign.client._id": 1,
          "campaign.client.username": 1
        }
      }
]);

Solution 2

Using pipeline and $project inside $lookup

db.somecollection.aggregate([{
    $lookup: {
        from: "campaigns",
        localField: "campId",
        foreignField: "_id",
        as: "campaign"
    }
}, {
    $unwind: "$campaign"
}, {
    $lookup: {
        from: "entities",
        let: { client_id: "$campaign.clientid" },    
        pipeline : [
            { $match: { $expr: { $eq: [ "$_id", "$$client_id" ] } }, },
            { $project : { _id:1, username:1 } }
        ],
        as: "campaign.client"
    }
}]);

Solution 3

Just to add a little thing to the previous answer: You can put a 0 to a project item that you want to ignore and the rest will be retrieved, so you don´t need to write all the list with 1:

db.somecollection.aggregate([
  {
    "$lookup": {
      "from": "campaigns",
      "localField": "campId",
      "foreignField": "_id",
      "as": "campaign"
    }
  },
  {
    "$unwind": "$campaign"
  },
  {
    "$lookup": {
      "from": "entities",
      "localField": "campaign.clientid",
      "foreignField": "_id",
      "as": "campaign.client"
    }
  },
  {
    "$project": {
      "campaign.client.shhh": 0
    }
  }
])

Solution 4

I know this is very late to answer to this question. But in my opinion, an update can sometimes prove to be very beneficial.
The project stage is great but you'd still be requesting for the entire dock in the $lookup stage. The fields are only filtered in the projection stage following it.

After the release of MongoDB 3.6, you can now add a pipeline to a $lookup stage, to specify multiple join conditions. Find more details in their official docs.
Specify Multiple Join Conditions with $lookup

You can transform your aggregation pipeline as follows, to get the desired result:

db.somecollection.aggregate([{
    $lookup: {
        from: "campaigns",
        localField: "campId",
        foreignField: "_id",
        as: "campaign"
    }
}, {
    $unwind: "$campaign"
}, {
    $lookup: {
        from: "entities",
        let: {clientid: '$campaign.clientid'},
        pipeline: [
           { '$match': 
             { '$expr': 
               { 
                  '$eq': ['$_id', '$$clientid']   
               }
             }
           },
           { '$project': 
              '_id': 1,
              'username': 1
           }
        ]
        as: "campaign.client"
    }
}]);

This way you can filter the fields of the joined collection right inside the $lookup stage.
Notice the $$ sign inside the $match stage of inner pipeline. It is used to denote a custom field defined inside the let block.

View more solutions