Here is the code in mongodb playground.
I have a query that looks like below:
db.posts.aggregate({
$lookup: {
from: "users",
let: {comments: "$comments"},
pipeline: [
{$match: {$expr: {$in: ["$_id", "$$comments.userId"]} }},
{"$addFields":{"text": "$$comments.text"}}
],
as: "comments"
}
})
The problem with this query is that the text value I get is is an array containing the texts from all comments. If I try to filter by userId, I get another problem. If a user has commented more than once the text value will be an array containing all of their comments.
How can I get the looked up user and their comment as in one object?
EDIT
I expect a result that looks like below:
[
{
"_id": ObjectId("5a934e000102030405000000"),
"id": "1",
"comments": [
{
"_id": "u1",
"name": "James",
"username": "jamo",
"text": "Hi there, who are you?"
},
{
"_id": "u1",
"name": "James",
"username": "jamo",
"text": "Hi! Are you still there?"
}
],
}
]
$unwind deconstruct comments array$lookup join users collection,$unwind get object of user$group by _id and reconstruct comments with required fieldsdb.posts.aggregate([
{ $unwind: "$comments" },
{
$lookup: {
from: "users",
localField: "comments.userId",
foreignField: "_id",
as: "comments.user"
}
},
{ $unwind: "$comments.user" },
{
$group: {
_id: "$_id",
id: { $first: "$id" },
comments: {
$push: {
_id: "$comments._id",
text: "$comments.text",
username: "$comments.user.username",
name: "$comments.user.name"
}
}
}
}
])
Second option, without $unwind,
$lookup with users collection$map to iterate loop of comments array$filter to iterate look of users array and find matching user info$arrayElemAt return first object from filtered user from $filter$mergeObjects will merge comment object and user infodb.posts.aggregate([
{
$lookup: {
from: "users",
localField: "comments.userId",
foreignField: "_id",
as: "users"
}
},
{
$project: {
comments: {
$map: {
input: "$comments",
as: "c",
in: {
$mergeObjects: [
"$$c",
{
$arrayElemAt: [
{
$filter: {
input: "$users",
cond: { $eq: ["$$c.userId", "$$this._id"] }
}
},
0
]
}
]
}
}
}
}
}
])
I have not tested performance of both queries, Try and use suitable query