What you have done is correct. After the lookup between Product
and Comment
, you need to have another lookup to join User
also.
You need to add following stages to achieve your target
unwind
to unstructured thecomments[]
arraylookup
to joinUser
collection- Since lookup provides an array, we can get the first element of the array using
$arrayElemAt
with the help of safety operator$ifNull
. (If thecomments []
is empty, then the script throws error. To handle that we use$ifNull
) - We already unstructured the array,
group
helps to regroup it
The stages are given below
{
$unwind: "$comments"
},
{
$lookup: {
from: "User",
let: {
id: "$comments.commented_by"
},
pipeline: [
{
$match: {
$expr: {
$eq: [
"$_id",
"$$id"
]
}
}
},
],
as: "comments.commented_by"
}
},
{
$addFields: {
"comments.commented_by": {
$ifNull: [
{
$arrayElemAt: [
"$comments.commented_by",
0
]
},
{}
]
}
}
},
{
$group: {
_id: "$_id",
title: {
$first: "$title"
},
hasCommented: {
$first: "$hasCommented"
},
comments: {
$push: "$comments"
}
}
}
Working Mongo playground
Note : FYI,The variable and collection name may be different.
CLICK HERE to find out more related problems solutions.