set field in array of objects after lookup. MongoDb

Question

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?

Tom Slabbaert · Accepted Answer · 2021-11-02 13:05:01Z

5

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

answered Nov 2, 2021 at 13:05

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Loudrous Over a year ago

after the group stage i have only the _id and the comments. How can I get all the others just like they were before?

Tom Slabbaert Over a year ago

You can restructure the data in many ways, using operators like $first in the group stage. but if you have a lot of field this is the most generic way to support them all.

Collectives™ on Stack Overflow

set field in array of objects after lookup. MongoDb

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related