$project in $lookup mongodb
32,433
Solution 1
You can use $lookup
3.6 syntax to $project
the fields inside the $lookup
pipeline
User.aggregate([
{ "$lookup": {
"from": "schedules",
"let": { "id": "$_id.phone" },
"pipeline": [
{ "$match": { "$expr": { "$eq": ["$customer.phone", "$$id"] }}},
{ "$project": { "scheduleStart": 1, "scheduleEnd": 1 }}
],
"as": "user_detail"
}}
])
Solution 2
For version of mongo version > 3.6 this query should work for you:
User.aggregate([{
$match: {
storeKey: req.body.store,
}
},
{
$group: {
_id: {
id: "$_id",
name: "$name",
cpf: "$cpf",
phone: "$phone",
email: "$email",
birthday: "$birthday",
lastName: "$lastname"
},
totalServices: {
$sum: "$services"
},
}
},
{
$lookup: {
from: "schedules",
localField: "_id.phone",
foreignField: "customer.phone",
as: "user_detail"
}
},
{
$project: {
_id: 1,
name: 1,
name: 1,
cpf: 1,
phone: 1,
email: 1,
birthday: 1,
totalServices: 1,
totalValue: { $sum : "$user_detail.value" },
count: {
$sum: 1
},
user_detail: {
scheduleEnd: 1,
scheduleStart: 1,
}
}
},
Related videos on Youtube
Comments
-
Matheus Barem almost 2 years
I have a query, that use $lookup to "join" two models, after this i use $project to select olny the fields that i need, but my $project brings an arrray of objects (
user_detail
) that contains more data that i need. I want only two fields (scheduleStart
andscheduleEnd
) of my result.My query:
User.aggregate([{ $match: { storeKey: req.body.store, } }, { $group: { _id: { id: "$_id", name: "$name", cpf: "$cpf", phone: "$phone", email: "$email", birthday: "$birthday", lastName: "$lastname" }, totalServices: { $sum: "$services" }, } }, { $lookup: { from: "schedules", localField: "_id.phone", foreignField: "customer.phone", as: "user_detail" } }, { $project: { _id: 1, name: 1, name: 1, cpf: 1, phone: 1, email: 1, birthday: 1, totalServices: 1, totalValue: { $sum : "$user_detail.value" }, count: { $sum: 1 }, user_detail: 1 } },
Result of query:
count: 1 totalServices: 0 totalValue: 73 user_detail: Array(2) 0: ... paymentMethod: 0 paymentValue: "0" scheduleDate: "2018-10-02" scheduleEnd: "2018-10-02 08:40" scheduleStart: "2018-10-02 08:20" status: 3 store: "5b16cceb56a44e2f6cd0324b" updated: "2018-11-27T13:30:21.116Z" 1: ... paymentMethod: 0 paymentValue: "0" scheduleDate: "2018-11-27" scheduleEnd: "2018-11-27 00:13" scheduleStart: "2018-11-27 00:03" status: 2 store: "5b16cceb56a44e2f6cd0324b" updated: "2018-11-27T19:33:39.498Z" _id: birthday: "1992-03-06" email: "[email protected]" id: "5bfed8bd70de7a383855f09e" name: "Chris Santos G" phone: "11969109995" ...
Result that i need:
count: 1 totalServices: 0 totalValue: 73 user_detail: Array(2) 0: scheduleEnd: "2018-10-02 08:40" scheduleStart: "2018-10-02 08:20" 1: scheduleEnd: "2018-11-27 00:13" scheduleStart: "2018-11-27 00:03" _id: birthday: "1992-03-06" email: "[email protected]" id: "5bfed8bd70de7a383855f09e" name: "Chris Santos G" phone: "11969109995" ...
How can i do this with my query?
-
Adam Reis over 2 yearsHaving to add all the parent fields to the projection is such a pain though...