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

MongodbAggregation FrameworkLookup

Mongodb Problem Overview


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?

Mongodb Solutions


Solution 1 - Mongodb

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 - Mongodb

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 - Mongodb

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 - Mongodb

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionSamuel Rondeau-MillaireView Question on Stackoverflow
Solution 1 - MongodbRodrigo LopeteguiView Answer on Stackoverflow
Solution 2 - MongodbKaushik R BangeraView Answer on Stackoverflow
Solution 3 - Mongodbuser2367101View Answer on Stackoverflow
Solution 4 - MongodbH. GoyalView Answer on Stackoverflow