Why "as" in $lookup is replacing the complete set?

user_12125692 0
Viewed 168

Let me first introduce you to the 2 collections I am using :

Collection 1 : users

> db.users.find().pretty()
{
    "_id" : ObjectId("5ee4e727d04e4b4ac1ef115b"),
    "name" : "Ashutosh Tiwari",
    "age" : 21,
    "email" : "ashutosh@gmail.com"
}
{
    "_id" : ObjectId("5ee4e727d04e4b4ac1ef115c"),
    "name" : "Maximilian",
    "age" : 32,
    "email" : "max@yahoo.com"
}

Collection 2 : posts

> db.posts.find().pretty()
{
    "_id" : ObjectId("5ee51b7ed9f661cad505fcc6"),
    "title" : "First One",
    "text" : "Hey this is the first Author",
    "author" : ObjectId("5ee4e727d04e4b4ac1ef115c"),
    "comments" : [
        {
            "user" : ObjectId("5ee4e727d04e4b4ac1ef115b"),
            "comment" : "This is my comment"
        }
    ]
}
{
    "_id" : ObjectId("5ee5353cd9f661cad505fcc8"),
    "title" : "First One",
    "author" : ObjectId("5ee4e727d04e4b4ac1ef115c"),
    "comments" : [
        {
            "user" : ObjectId("5ee4e727d04e4b4ac1ef115b"),
            "comment" : "This is my comment"
        }
    ]
}

I want to have the user inside comments array in 2nd Collection(posts) to be replaced by the user who has written that comment.

I have tried the query below but it is replacing the comments section !

 > db.posts.aggregate([
         { $lookup: 
                  {from: "users", 
                   localField:"comments.user",
                   foreignField:"_id",
                   as:"comments.user"
                   }   
         }  ]).pretty()

{
    "_id" : ObjectId("5ee51b7ed9f661cad505fcc6"),
    "title" : "First One",
    "text" : "Hey this is the first Author",
    "author" : ObjectId("5ee4e727d04e4b4ac1ef115c"),
    "comments" : {
        "user" : [
            {
                "_id" : ObjectId("5ee4e727d04e4b4ac1ef115b"),
                "name" : "Ashutosh Tiwari",
                "age" : 21,
                "email" : "ashutosh@gmail.com"
            }
        ]
    }
}
{
    "_id" : ObjectId("5ee5353cd9f661cad505fcc8"),
    "title" : "First One",
    "author" : ObjectId("5ee4e727d04e4b4ac1ef115c"),
    "comments" : {
        "user" : [
            {
                "_id" : ObjectId("5ee4e727d04e4b4ac1ef115b"),
                "name" : "Ashutosh Tiwari",
                "age" : 21,
                "email" : "ashutosh@gmail.com"
            }
        ]
    }
}

So, here, whole comments section is now replaced whereas I wanted to have the details in comments.user section so I could see the comment and the user who has posted that comment.

aggregation-framework mongodb-query mongodb aggregate lookup
2 Answers

you need to unwind the comments array first

your query may look something like this

db.posts.aggregate([
  {
    $unwind: "$comments" // unwind the comments array to get a stream of documents, each document has only one comment
  },
  {
    $lookup: {
      from: "users",
      localField: "comments.user",
      foreignField: "_id",
      as: "comments.user"
    }
  },
  {
    $unwind: "$comments.user" // we know there is only one user inside a single comment, so we can unwind this user array to be an object too (as the lookup returns an array)
  },
  {
    $group: { // then do a group by the document _id to get unique documents with comments array instead of the same document duplicated with different comments 
      _id: "$_id",
      author: {
        $first: "$author"
      },
      text: {
        $first: "$text"
      },
      title: {
        $first: "$title"
      },
      comments: {
        $push: "$comments"
      }
    }
  }
])

you can test it here

hope it helps

user_123714750

You can handle it in the projection. 

db.posts.aggregate([
         { $lookup: 
                  {from: "users", 
                   localField:"comments.user",
                   foreignField:"_id",
                   as:"cu"
                   }   
         },
         {$unwind:{path:"$cu"}},
         {
             $project:{
                 "title":1,
                 "text":1,
                 "author":1,
                 "comments":{
                     user: "$cu",
                     comment: { $arrayElemAt: [ "$comments.comment", 0 ] },
                     }
                 }
             }
         ])
user_75148740
Related