I using aggregate to get the result from Posts collection in Mongodb, I trying with $lookup to get data reference to the users collection but result not what i am expected
This's my post model Schema:
const Posts = new mongoose.Schema({
postTitle: {
type: String,
required: [true, "Post title must be not empty!"],
trim: true,
maxlength: [150, "Post title can not be more than 150 characters!"]
},
postContent: {
type: String,
required: [true, "Post content must be not empty!"],
trim: true,
maxlength: [50000, "Post content can not be more than 50,000 characters!"]
},
postAuthor: {
type: Schema.Types.ObjectId,
required: true,
ref: 'users'
}
}, {
timestamps: { createdAt: true, updatedAt: true },
versionKey: false
})
export const PostModel = mongoose.model('posts', Posts);
This's my code in declared aggregate:
const doc = await PostCollection.aggregate([
{
"$lookup": {
"from": "users",
"localField": "users._id",
"foreignField": "postAuthor",
"as": "postAuthor"
}
},
{
"$project": {
"_id": 0,
"id": "$_id",
"title": "$postTitle",
"content": "$postContent",
"user": "$postAuthor"
}
}
])
console.log(doc)
I got the result after executed:
[
{
id: "616e9df6039f4b9f66ce07e0",
title: "title book 1",
content: "content book 1",
user: [
{
_id: "616e9e27cf2f12ba06dd92c9", // author of book 1
name: "John"
},
{
_id: "616e9e3354973970851d1591",
name: "Robinson"
}
]
},
{
id: "616e9e16e2db14f41b82fd8f",
title: "title book 2",
content: "content book 2",
user: [
{
_id: "616e9e27cf2f12ba06dd92c9",
name: "John"
},
{
_id: "616e9e3354973970851d1591", // author of book 2
name: "Robinson"
}
]
}
]
I just wanna get the result like this:
[
{
id: "616e9df6039f4b9f66ce07e0",
title: "my title book 1",
content: "my content book 1",
user: [
{
_id: "616e9e27cf2f12ba06dd92c9", // author of book 1
name: "John"
}
]
},
{
id: "616e9e16e2db14f41b82fd8f",
title: "my title book 2",
content: "my content book 2",
user: [
{
_id: "616e9e3354973970851d1591", // author of book 2
name: "Robinson"
}
]
}
]
And sample data inserted in Posts Collection:
_id | postTitle | postContent | postAuthor |
---|---|---|---|
616e9df6039f4b9f66ce07e0 | title book 1 | my content book 1 | 616e9e27cf2f12ba06dd92c9 |
616e9e16e2db14f41b82fd8f | title book 2 | my content book 2 | 616e9e3354973970851d1591 |
CodePudding user response:
As per docs https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/, try this
const doc = await PostCollection.aggregate([
{
"$lookup": {
"from": "users",
"localField": "postAuthor",
"foreignField": "_id",
"as": "postAuthor"
}
},
{
"$project": {
"_id": 0,
"id": "$_id",
"title": "$postTitle",
"content": "$postContent",
"user": "$postAuthor"
}
}
])
console.log(doc)