Mongo多集合合并分页：如何将invites与users集合合并为单一分页列表

Alright, let's figure out how to solve this. You want to merge your invites and users collections into a single paginated table (like a MySQL view) without changing your existing data structures, right? MongoDB's aggregation framework is exactly what you need here—let's break this down step by step.

Approach #

The core idea is to:

• Standardize the schema from both collections so fields match (e.g., map users.firstName to name to align with invites.name).
• Merge the two datasets together.
• De-duplicate records where the same email exists in both collections.
• Sort the combined results by email.
• Apply offset/limit pagination.

Full Aggregation Query #

Here's the complete pipeline that implements this:

db.invites.aggregate([
  // 1. Project invites into a standardized schema
  {
    $project: {
      email: 1,
      name: "$name",
      source: "invite", // Optional: track if the record came from invites or users
      inviteId: "$_id",
      userId: null
    }
  },
  // 2. Union with users (also converted to the same schema)
  {
    $unionWith: {
      coll: "users",
      pipeline: [
        {
          $project: {
            email: 1,
            name: "$firstName", // Map firstName to name for consistency
            source: "user",
            userId: "$_id",
            inviteId: null
          }
        }
      ]
    }
  },
  // 3. Group by email to de-duplicate, prioritize user records when available
  {
    $group: {
      _id: "$email",
      name: { $last: "$name" }, // Picks user's name if both exist (since users come after invites in the union)
      source: { $last: "$source" },
      userId: { $max: "$userId" }, // Keeps userId if it exists, else null
      inviteId: { $max: "$inviteId" } // Keeps inviteId if it exists, else null
    }
  },
  // 4. Clean up the output schema (rename _id back to email)
  {
    $project: {
      _id: 0,
      email: "$_id",
      name: 1,
      source: 1,
      userId: 1,
      inviteId: 1
    }
  },
  // 5. Sort the combined results by email
  {
    $sort: { email: 1 }
  },
  // 6. Apply pagination: replace skip/limit with your values
  {
    $skip: 0 // Offset: (pageNumber - 1) * pageSize
  },
  {
    $limit: 10 // Number of records per page
  }
])

Key Explanations #

• Schema Standardization: The $project stages ensure both collections output the same field names, so merging works seamlessly. We also add optional fields like source to track where each record originated, and keep both userId and inviteId to reference the original documents if needed.
• Union: $unionWith combines the two projected datasets, similar to SQL's UNION ALL.
• De-duplication: The $group stage groups records by email to remove duplicates. Using $last here prioritizes user records (since we added users after invites in the union)—if you want to prioritize invite data instead, swap the order in $unionWith or use $first instead.
• Sorting & Pagination: $sort orders the results by email, then $skip (offset) and $limit handle pagination just like MySQL's LIMIT offset, count.

Notes for Adjustments #

• Remove Optional Fields: If you don't need to track record sources or original IDs, you can simplify the $project stages to only include email and name.
• Performance Optimization: For large datasets, $skip can be slow because MongoDB has to traverse all preceding records. If you can avoid strict offset-based pagination, consider using the last email from the current page to filter the next page (e.g., { $match: { email: { $gt: "lastEmailFromPage" } } } with $limit), which is much faster.
• Custom Deduplication Logic: If you need more control over which fields to keep when duplicates exist (e.g., always use the user's name over the invite's), you can use $cond in the $group stage, like:

  name: { $cond: { if: { $ne: ["$userId", null] }, then: "$name", else: "$name" } }
  

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