How to select only few values from lookup table

Question

I am trying to using aggregate function for fetching data from two documents. I am able to do it but i am finding a solution how can i apply $project in lookup table only

below is my approach

app.get('/getAllDetailById',(req,res)=>{
    if(db){
        // lookup
        db.collection("points").aggregate(
            [
                { "$addFields": { "enquiry_by": { "$toObjectId": "$enquiry_by" }}},
                { 
                "$lookup" : {
                            from: "user",
                            localField: "enquiry_by",
                            foreignField: "_id",
                            as: "userDetails"
                            }
                },
                { $unwind: "$userDetails"},
             ]
        ).toArray()
        .then(result=>{
            console.log(result[0])
        }).catch(err=>{
                res.send(err)
        })
    }
})

What i want is get all fields from points table and from user table i just want name and username. I have used $project but than its return only fields defined in this.

{ $project: {"userDetails.name":1, "userDetails.username":1,"_id":0} }

Is there any way that $project can be applied separately for user table

Does this answer your question? [$project in $lookup mongodb](https://stackoverflow.com/questions/53710203/project-in-lookup-mongodb) — VtoCorleone, Mar 19 '20 at 04:30
You can specify the _exclude fields_ only in the projection - this will keep all the fields you need . — prasad_, Mar 19 '20 at 04:30

score 1 · Accepted Answer · answered Mar 19 '20 at 04:39

You can use pipeline in the lookup if you are using mongodb >= 3.6: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#join-conditions-and-uncorrelated-sub-queries

So your code will look like:

app.get('/getAllDetailById',(req,res)=>{
    if(db){
        // lookup
        db.collection("points").aggregate(
            [
                { "$addFields": { "enquiry_by": { "$toObjectId": "$enquiry_by" }}},
                { 
                    "$lookup" : {
                            from: "user",
                            let: { "enquiry_by": "$enquiry_by" },
                            pipeline: [
                                {
                                    "$match": {
                                        "$expr": {
                                            "$eq": ["$_id", "$$enquiry_by"] 
                                        }
                                    },
                                    "$project": {
                                        "$name": 1,
                                        "$username": 1,
                                    }
                                },
                            ],
                            as: "userDetails"
                       }
                },
                { $unwind: "$userDetails"},
             ]
        ).toArray()
        .then(result=>{
            console.log(result[0])
        }).catch(err=>{
                res.send(err)
        })
    }
})

How to select only few values from lookup table

1 Answers1