I have two collections users and posts.

users has documents like this:

{
  _id: ObjectId('611142303c409b5dc826e563'),
  name: 'foo'
}

posts has documents like this:

{
  _id: ObjectId('611142303c409b5dc826e111'),
  comments:[
    {
      owner: ObjectId('611142303c409b5dc826e563'),
      description: "my description"
    },
    {
      owner: ObjectId('611142303c409b5dc826e333'),
      description: "my description2"
    }
  ]
}

When I receive a request server side, I need to return the owner's whole document and not just its id.

for example to a get request I have to return:

{
  _id: ObjectId('611142303c409b5dc826e111'),
  comments:[
    {
      owner:{
        _id: ObjectId('611142303c409b5dc826e563'),
        name: 'foo'
    },
      description: "my description"
    },
    {
      owner:     {
        _id: ObjectId('611142303c409b5dc826e555'),
        name: 'foo2'
      },
      description: "my description2"
    }
  ]
}

To do that I did the following pipeline:

[
  $lookup:{
    from: 'owners',
    localField: 'comments.owner',
    foreignField: '_id',
    as: 'owners_comments'
  }
]

Doing this way I get an array of owners that has comments in one specific document.

My question is how to get the right owner profile for each comment? I know I can do that server side easyer but I prefer doing it DB side.

I thought to map each comment and inside filter the owners_comments, but I have few problems to that in mongo aggregation.

Do you have any suggestion?

CodePudding user response：

You have to $unwind the comments array, only then execute the $lookup and then you want to $group to restore the original structure, like so:

db.posts.aggregate([
  {
    $unwind: "$comments"
  },
  {
    $lookup: {
      from: "owners",
      localField: "comments.owner",
      foreignField: "_id",
      as: "owners"
    }
  },
  {
    $group: {
      _id: "$_id",
      comments: {
        $push: {
          owner: "$comments.owner",
          description: "$comments.description",
          owner_profile: {
            "$arrayElemAt": [
              "$owners",
              0
            ]
          },
          
        }
      }
    }
  }
])

Mongo Playground