CODEWITHSUNDEEP
mongodbaggregation-frameworklookup
Samuel Rondeau-Millaire
Samuel Rondeau-Millaire

Reputation: 1130

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

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?

Upvotes: 71

Views: 104319

Answers (5)

Andrew Zagarichuk
Andrew Zagarichuk

Reputation: 509

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.

Upvotes: 0

H. Goyal
H. Goyal

Reputation: 181

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.

Upvotes: 5

Kaushik R Bangera
Kaushik R Bangera

Reputation: 581

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

Upvotes: 31

user2367101
user2367101

Reputation: 141

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

Upvotes: 5

Rodrigo Lopetegui
Rodrigo Lopetegui

Reputation: 1251

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

Upvotes: 110

Related Questions