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