Get posts with n latest comments in MongoDB

Question

So this is a common problem, getting posts with comments, which i can't seem to solve in MongoDB, unlike in MySQL which is easily solved with left-join.

Problem:

I would like to fetch the latest 8 posts with 2 recent comments on each post in MongoDB

• I don't want to restructure the post data to contain a list of the matching comment ids because i will expect thousands of comments in the future.

• I really don't want to resort to fetching the posts, then performing 8 separate queries using the post id to find the 2 latest comments. (although i guess is the next best solution)

• I have tried setting postSchema.virtual('comments', ..) and then when performing the query, populating by using Post.find(..).populate('comments', options: {limit: 2}), but unfortunately limit returns inconsistent results.

Data:

Post:
{ body: "post1" }
{ body: "post2" }
{ body: "post3" }
...
{ body: "post8" }

Comment:
{ post_id: 1, message: "comment1" }
{ post_id: 2, message: "comment2" }
{ post_id: 2, message: "comment3" }
{ post_id: 3, message: "comment4" }

All documents have a date field, but is removed due to brevity

Expected result:

{ 
  body:"post1", 
  comments: [
    { post_id: 1, message: "comment1" }
  ]
}
{ 
  body:"post2", 
  comments: [
    { post_id: 2, message: "comment2" },
    { post_id: 2, message: "comment3" }
  ]
}
{ 
  body:"post3", 
  comments: [
    { post_id: 3, message: "comment4" }
  ]
}
...

Answer 1

If you're using MongoDB 3.6 or higher you can use $lookup with custom pipeline to "join" posts with comments and take 2 most recent ones (using $limit)

db.posts.aggregate([
    {
        $sort: { date: -1 }
    },
    {
        $limit: 8
    },
    {
        $lookup: {
            from: "comments",
            let: { postId: "$_id" },
            pipeline: [
                { $match: { $expr: { $eq: [ "$$postId", "$post_id" ] } } },
                { $sort: { date: -1 } },
                { $limit: 2 }
            ],
            as: "comments"
        }
    }
])