I am fetching list of records having some nested reference to other collection, I want to populate that nested ObjectId which is inside array of objects, with mongoDb aggregate lookup query.
This is how DB collection structure is:
{
subject: {type: String},
body: {type: String},
recipients: [{
userId: {type: mongoose.Schema.Types.ObjectId, ref: 'User'},
stutus: {type: String, enum: ['pending','accepted','rejected'], default:'pending'}
}],
sender: {type: mongoose.Schema.Types.ObjectId, ref: 'User'}
}
What I am expecting:
[{
subject: 'Some subject here.',
body: 'Lorem ipsum dolor emit set',
recipients: [{
userId: {firstName: 'John', lastName: 'Doe'},
status: 'accepted'
},{
userId: {firstName: 'Jane', lastName: 'Doe'},
status: 'accepted'
}],
sender: {firstName: 'Jacobs', 'lastName': 'Doe'}
},{
subject: 'Some subject here.',
body: 'Lorem ipsum dolor emit set',
recipients: [{
userId: {firstName: 'Jane', lastName: 'Doe'},
status: 'rejected'
},{
userId: {firstName: 'John', lastName: 'Doe'},
status: 'accepted'
}],
sender: {firstName: 'Jacobs', 'lastName': 'Doe'}
}]
Any kind help will be greatly appreciated.
$unwind deconstruct recipients array$lookup with users collection for recipients.userId$unwind deconstruct recipients.userId array$lookup with users collection for sender$unwind deconstruct sender array$group by _id and reconstruct recipients arraydb.mails.aggregate([
{ $unwind: "$recipients" },
{
$lookup: {
from: "users",
localField: "recipients.userId",
foreignField: "_id",
as: "recipients.userId"
}
},
{ $unwind: "$recipients.userId" },
{
$lookup: {
from: "users",
localField: "sender",
foreignField: "_id",
as: "sender"
}
},
{ $unwind: "$sender" },
{
$group: {
_id: "$_id",
recipients: { $push: "$recipients" },
subject: { $first: "$subject" },
body: { $first: "$body" },
sender: { $first: "$sender" }
}
}
])
Try This:
db.emails.aggregate([
{ $unwind: "$recipients" },
{
$lookup: {
from: "users",
let: { userId: "$recipients.userId", status: "$recipients.stutus" },
pipeline: [
{
$match: {
$expr: { $eq: ["$_id", "$$userId"] }
}
},
{
$project: {
"_id": 0,
"userId": {
"firstName": "$firstName",
"lastName": "$lastName",
},
"status": "$$status"
}
}
],
as: "recipient"
}
},
{
$lookup: {
from: "users",
let: { userId: "$sender" },
pipeline: [
{
$match: {
$expr: { $eq: ["$_id", "$$userId"] }
}
},
{
$project: {
"_id": 0,
"firstName": 1,
"lastName": 1
}
}
],
as: "sender"
}
},
{
$group: {
_id: "$_id",
subject: { $first: "$subject" },
body: { $first: "$body" },
recipients: { $push: { $arrayElemAt: ["$recipient", 0] } },
sender: { $first: { $arrayElemAt: ["$sender", 0] } }
}
}
]);