I am using $lookup aggregate to join collection but I am facing a problem from the result. Specifically, I have three collections look like:

Post:

     id: 1,   
     title: 'my title',
     content: 'bla bla bla',
     ...

Comment:

     post_id: 1,
     user_id: '123',
     content: 'great article!!!',

User:

     id: '123',
     name: 'Ferb'

My code to query post and this post also contain comment:

        $lookup: {
            from: 'comments',
            localField: 'id',
            foreignField: 'post_id',
            as: 'comments',
        },

        $project: {
           id: 1,
           title: 1,
           content: 1,
           comments: '$comments'
        }

According to the results i got:

           id: 1,
           title: 'my title',
           content: 'bla bla bla',
           comments: [
              post_id: 1,
              user_id: '123', // needs to $lookup from User model
              content: 'great article!!!',
           ]

But this is not what I expected, I want to $lookup user_id in comments list with nested $lookup. What should I do to get the expected results?

CodePudding user response：

Personally, I am against the idea of nested $lookup, as that is likely to reduce the readability of query.

I prefer something simple as $lookup twice.

db.posts.aggregate([
  {
    "$lookup": {
      "from": "comments",
      "localField": "id",
      "foreignField": "post_id",
      "as": "commentsLookup"
    }
  },
  {
    "$unwind": "$commentsLookup"
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "commentsLookup.user_id",
      "foreignField": "id",
      "as": "commentsLookup.usersLookup"
    }
  },
  {
    "$unwind": "$commentsLookup.usersLookup"
  },
  {
    $group: {
      _id: "$_id",
      title: {
        $first: "$title"
      },
      content: {
        $first: "$content"
      },
      commentsLookup: {
        $push: "$commentsLookup"
      }
    }
  }
])

Mongo Playground

However, if you insists in nested $lookup, here is one way.

db.posts.aggregate([
  {
    "$lookup": {
      "from": "comments",
      "localField": "id",
      "foreignField": "post_id",
      "pipeline": [
        {
          "$lookup": {
            "from": "users",
            "localField": "user_id",
            "foreignField": "id",
            "as": "usersLookup"
          }
        },
        {
          "$unwind": "$usersLookup"
        }
      ],
      "as": "commentsLookup"
    }
  },
  {
    "$unwind": "$commentsLookup"
  }
])

Mongo Playground