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" : "mail@mail.com",
        }
    ]
}

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?

use a `$project` stage. `{ $project : { _id1 : 1, campId : 1, articleId : 1, campaign._id : 1, campaign.clientid : 1, campaign.client._id : 1, campaign.client.username : 1 } }` — SiddAjmera, Feb 23 '16 at 17:11
@SiddharthAjmera you should transform it in an answer, you just saved my day! — Rafael Beckel, Jun 29 '17 at 15:48

score 105 · Accepted Answer · edited May 24 '18 at 15:13

105

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
        }
      }
]);

edited May 24 '18 at 15:13

Samuel Rondeau-Millaire

1,100
2
13
24

answered Aug 17 '17 at 14:35

Rodrigo Lopetegui

1,191
1
10
8

4

why do we add $unwind? why it is needed? – Faisal Jul 11 '18 at 06:44
20

@Faisal it will return campaign as an array, if you don't do $unwind – Yuriy Chachora Jul 14 '18 at 03:11
1

Hey... is there a way to just return everything (in project) from the parent and only specific fields from the child. ( I used " * " as indicator ) eg: $project: { " * ": 1, "campaign._id": 1, "campaign.clientid": 1, "campaign.client._id": 1, "campaign.client.username": 1 } – MLissCetrus Apr 10 '20 at 01:25
@MLissCetrus did you find way to do so? – AlpeshVasani Jun 26 '20 at 11:42
@MLissCetrus AlpeshVasani did you find way to do ? – Arif Fathurrohman Jul 28 '20 at 17:05
using $project at the end can be hectic, one has to mention a lot of fields to select/unselect. Rather use pipeline within $lookup and write a $project wihtin the pipeline instead. – Saurav Ghimire Nov 19 '22 at 16:10

score 27 · Answer 2 · answered Aug 20 '20 at 10:55

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"
    }
}]);

score 5 · Answer 3 · answered Jul 01 '20 at 19:45

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
    }
  }
])

score 4 · Answer 4 · edited Sep 05 '20 at 09:44

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.

Andrew Zagarichuk · Answer 5 · 2023-08-27T16:17:14.110

After trying several options, I found this way to solve this problem, it seems more compact and readable:

const query = [
  { $match: match },
  {
    $lookup: {
      'from': 'accounts',
      'localField': 'course_account_owner_id',
      'foreignField': '_id',
      'as': 'accountTmp'
    }
  },
  { $unwind: { path: '$accountTmp', preserveNullAndEmptyArrays: true } },
  {
    $addFields: {
      'account.account_name': '$accountTmp.account_name',
      'account.account_logo': '$accountTmp.account_logo',
    }
  }, {
    $project: {
        'accountTmp': 0
    }
  }
];

It works because of specific behavior of $project stage which, when excluding unnecessary fields, still leaves all the main fields in the output, but as soon as you specify at least one subdocument field that you want to include, it immediately turns off all the main fields.

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

5 Answers5

Linked

Related