如何在MongoDB的$lookup聚合中仅返回关联集合的最后一条文档

Got it, I’ll help you tweak your aggregation query so each group returns only the most recent associated post (if any exists). Here’s the revised solution tailored to your needs:

Updated Aggregation Query #

db.groups.aggregate([
  {
    $lookup: {
      from: "posts",
      localField: "_id",
      foreignField: "group",
      as: "latest_post",
      // Add a nested pipeline to filter posts during the lookup
      pipeline: [
        // Sort posts from newest to oldest (use _id if you don't have a createdAt field)
        { $sort: { createdAt: -1 } },
        // Keep only the first (newest) post in the results
        { $limit: 1 }
      ]
    }
  },
  // Optional: Convert the single-element array to a direct document
  {
    $addFields: {
      latest_post: { $first: "$latest_post" }
    }
  }
])

Key Details & Explanations: #

• Pipeline-powered $lookup: Instead of pulling all matching posts for each group, we use a nested pipeline to process the posts collection before joining. This lets us sort and limit results directly within the lookup step, which is more efficient than fetching all posts first.
• Sorting logic: The $sort stage orders posts in descending order by createdAt (assuming you have this timestamp field). If you don’t track creation time explicitly, you can use { $sort: { _id: -1 } } instead—MongoDB’s ObjectId includes a timestamp, so sorting by _id descending will also return the latest post.
• Limiting results: $limit: 1 ensures only the top (newest) post is included in the join output for each group.
• Optional array conversion: The final $addFields step takes the single-element latest_post array and converts it to a direct document (instead of an array with one item). If you prefer to keep it as an array (even with a single entry), you can skip this stage entirely.

Example Output: #

With the optional conversion step included, your results will look like this:

[
  {
    "_id": ObjectId("group1-unique-id"),
    "name": "Book Club",
    // Other group fields...
    "latest_post": {
      "_id": ObjectId("newest-post-id"),
      "group": ObjectId("group1-unique-id"),
      "content": "Next meeting is scheduled for June 5th!",
      "createdAt": ISODate("2024-05-22T09:30:00Z")
    }
  },
  {
    "_id": ObjectId("group2-unique-id"),
    "name": "Hiking Crew",
    // Other group fields...
    "latest_post": null // No posts associated with this group
  }
]

内容的提问来源于stack exchange，提问作者Smit Patel