mongoose - MongoDB project with lookup

Question

I want to get other collection's field using $lookup. For example, this is my users collection:

{ "_id" : ObjectId("601c4f89049f9d2cc47eece2"), "username" : "Ahmad", "email" : "[email protected]", "password" : "$2a$08$fAT1G.db9LjXRV7bOPKNzuosWZ3QGJs2rdHIGmPbWtusDA0Qko47e", "tokens" : [ { "_id" : ObjectId("601d4de52472f01ef4552e77"), "token" : "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjYwMWM0Zjg5MDQ5ZjlkMmNjNDdlZWNlMiIsImlhdCI6MTYxMjUzMzIyMSwiZXhwIjoxNjEzMTM4MDIxfQ.lEt5mIQkjVCRFvDJsHoK1rwdN-WELSVWMFW3p8itZBU" }, { "_id" : ObjectId("601c4f89049f9d2cc47eece3"), "token" : "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjYwMWM0Zjg5MDQ5ZjlkMmNjNDdlZWNlMiIsImlhdCI6MTYxMjQ2ODEwNSwiZXhwIjoxNjEzMDcyOTA1fQ.WwKCenn7Qh3C1DhJT7H1Kcqu4ZRl0Z7SYJxBV6v87S8" } ], "createdAt" : ISODate("2021-02-04T19:48:25.024Z"), 
"updatedAt" : ISODate("2021-02-05T13:53:41.730Z"), "__v" : 0 }
{ "_id" : ObjectId("601c954a59477b1d38e8a69e"), "username" : "fred", "email" : "[email protected]", "password" : "$2a$08$nZ2kusIoSdwgjBpcZX4WTe2sO3tnieUcVIDGXiUCofLSvkdSdTRFa", "tokens" : [ { "_id" : ObjectId("601c954a59477b1d38e8a69f"), "token" : "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjYwMWM5NTRhNTk0NzdiMWQzOGU4YTY5ZSIsImlhdCI6MTYxMjQ4NTk2MiwiZXhwIjoxNjEzMDkwNzYyfQ.bHkhfBlgswerB25za2y3YODk-cbRgGhlcrtr-zaIG9M" } ], "createdAt" : ISODate("2021-02-05T00:46:02.518Z"), "updatedAt" : ISODate("2021-02-05T00:46:02.518Z"), "__v" : 0 }

And here posts collection:

{ "_id" : ObjectId("601c5190939a1e294c688267"), "title" : "abdulila", "content" : "wqd", "category" : "Health", "owner" : ObjectId("601c4f89049f9d2cc47eece2"), "comments" : [ { 
"_id" : ObjectId("601c532a5b746f1ad081c57e"), "content" : "Marchgisio", "owner" : ObjectId("601c4f89049f9d2cc47eece2"), "createdAt" : ISODate("2021-02-04T20:03:54.055Z"), "updatedAt" : ISODate("2021-02-04T20:03:54.055Z") }, { "_id" : ObjectId("601c53445b746f1ad081c57f"), "content" : "Balotelli", "owner" : ObjectId("601c4f89049f9d2cc47eece2"), "createdAt" : ISODate("2021-02-04T20:04:20.967Z"), "updatedAt" : ISODate("2021-02-04T20:04:20.967Z") } ], "createdAt" : ISODate("2021-02-04T19:57:04.902Z"), "updatedAt" : ISODate("2021-02-04T20:04:20.968Z"), "__v" : 2 }
{ "_id" : ObjectId("601c958259477b1d38e8a6a0"), "title" : "ohh mighty spurs", "content" : "harry kane", "category" : "Sports", "owner" : ObjectId("601c954a59477b1d38e8a69e"), "comments" : [ { "_id" : ObjectId("601c958f59477b1d38e8a6a1"), "content" : "eorje", "owner" : ObjectId("601c954a59477b1d38e8a69e"), "createdAt" : ISODate("2021-02-05T00:47:11.461Z"), "updatedAt" : ISODate("2021-02-05T00:47:11.461Z") }, { "_id" : ObjectId("601c95cac3b0131328e29344"), "content" : "qwe", "owner" : ObjectId("601c954a59477b1d38e8a69e"), "createdAt" : ISODate("2021-02-05T00:48:10.207Z"), "updatedAt" : ISODate("2021-02-05T00:48:10.207Z") }, { "_id" : ObjectId("601c95e3af31ab1608abd23f"), "content" : "wef", "owner" : ObjectId("601c954a59477b1d38e8a69e"), "createdAt" : ISODate("2021-02-05T00:48:35.168Z"), "updatedAt" : ISODate("2021-02-05T00:48:35.168Z") } ], "createdAt" : ISODate("2021-02-05T00:46:58.962Z"), "updatedAt" : ISODate("2021-02-05T00:48:35.168Z"), "__v" : 3 }
{ "_id" : ObjectId("601d210fbf41fd262c9172c4"), "title" : "bobo", "content" : "hiv is a lie and in god name we will kill all the jews in balastin XD UwU
", "category" : "Movies", "owner" : ObjectId("601c954a59477b1d38e8a69e"), "comments" : [ ], "createdAt" : ISODate("2021-02-05T10:42:23.773Z"), "updatedAt" : ISODate("2021-02-05T10:42:23.773Z"), "__v" 
: 0 }
{ "_id" : ObjectId("601d4a2f1f169d3d9cbfcbfe"), "title" : "Abdallah", "content" : "dfsv", "category" : "Health", "owner" : ObjectId("601c954a59477b1d38e8a69e"), "comments" : [ { "_id" : ObjectId("601d50d80678f626105f943e"), "content" : "dvd", "owner" : ObjectId("601c4f89049f9d2cc47eece2"), "createdAt" : ISODate("2021-02-05T14:06:16.941Z"), "updatedAt" 
: ISODate("2021-02-05T14:06:16.941Z") }, { "_id" : ObjectId("601d531fcc80c13fb832af40"), "content" : "svsf", "owner" : ObjectId("601c4f89049f9d2cc47eece2"), "createdAt" : ISODate("2021-02-05T14:15:59.873Z"), "updatedAt" : ISODate("2021-02-05T14:15:59.873Z") } ], "createdAt" : ISODate("2021-02-05T13:37:51.787Z"), "updatedAt" : ISODate("2021-02-05T14:15:59.874Z"), "__v" : 2 }

My purpose is to get all comments of 1 specific post. Then, for each comment - I want its content and owner username.

This is my try:

Post.aggregate([
    {
        $match: { _id: mongoose.Types.ObjectId(req.query.postID) },
    },
    {
        $limit: 1,
    },
    {
        $project: {
            _id: 0,
            'comments.owner': 1,
            'comments.content': 1,
        },
    },
]);

So this gives me back the content and the owner id. But I want the owner username which presents in the users collection. How could I achieve this?

question from:https://stackoverflow.com/questions/66067512/mongodb-project-with-lookup

Answer 1

I came up with this pipeline. I'm sure it cloud be improve.

The steps are (commented in the pipeline)

1. Split comments array so we could concentrate on each one
2. Lookup for the user info
3. The lookup brings an array of one element, so I unwind it.
4. Replace the owner object id with the owner username.
5. Group comments
6. Project the needed fields

I test this directly on mongodb with one of your post.

[
    {
        $match: { _id: ObjectId("601d4a2f1f169d3d9cbfcbfe") },
    },
    {
        $unwind: '$comments' //split comments array so we could concentrate on each one
    },
    {
      $lookup: {
        from: 'user',
        localField: 'comments.owner', // lookup for the user info
        foreignField: '_id',
        as: 'owner'
      }  
    },
    {
        $unwind: '$owner' // the lookup brings an array of one element, so I unwind it.
    },    
    {
      $addFields: {
       'comments.owner': "$owner.username"  //replace the owner object id with the owner username
      }
    },
    {
      $group: {
         _id: '_id', // group comments
         'comments': {$push: '$comments'}
      }
    },
    {
      $project:{
         _id: 0,  // project the needed fields
         'comments.content':1,
         'comments.owner': 1,
      }    
    }
])