Home > Mobile >  How to prevent $lookup return duplicate value in Mongodb?
How to prevent $lookup return duplicate value in Mongodb?

Time:10-19

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)
  • Related