MongoDB单操作多聚合：合并adminUser与departmentUser全量数据求助

It sounds like you need a full outer join between your two collections—something MongoDB doesn’t support natively—but we can build the exact result you want by combining $lookup, $unionWith, and conditional projection. Here’s a practical, step-by-step solution that returns all entries from both collections, merging matches and preserving non-matching records as separate documents.

Full Aggregation Pipeline #

db.adminUser.aggregate([
  // Step 1: Link adminUser to matching departmentUser entries
  {
    $lookup: {
      from: "departmentUser",
      localField: "adminUserId",
      foreignField: "adminUserId",
      as: "departmentUserinfo"
    }
  },
  // Step 2: Format adminUser entries, only include departmentUserinfo if a match exists
  {
    $project: {
      _id: 0,
      adminUserId: "$adminUserId",
      userName: "$userName",
      position: "$position",
      departmentUserinfo: {
        $cond: {
          if: { $ne: [{ $size: "$departmentUserinfo" }, 0] },
          then: { $arrayElemAt: ["$departmentUserinfo", 0] },
          else: "$$REMOVE"
        }
      }
    }
  },
  // Step 3: Add non-matching departmentUser entries (no corresponding adminUser)
  {
    $unionWith: {
      coll: "departmentUser",
      pipeline: [
        // Check if the departmentUser has a matching adminUser
        {
          $lookup: {
            from: "adminUser",
            localField: "adminUserId",
            foreignField: "adminUserId",
            as: "adminMatch"
          }
        },
        // Filter entries with empty adminUserId or no adminUser match
        {
          $match: {
            $or: [
              { adminUserId: "" },
              { adminMatch: { $size: 0 } }
            ]
          }
        },
        // Format these entries with empty admin fields and full department data
        {
          $project: {
            _id: 0,
            adminUserId: "",
            userName: "",
            position: "",
            departmentUserinfo: "$$ROOT"
          }
        }
      ]
    }
  }
])

How This Works #

Let’s break down the pipeline to align with your desired output:

1. Join and format adminUser entries:

   • The $lookup finds all departmentUser records that share the same adminUserId as the current adminUser.
   • $arrayElemAt grabs the first match (adjust this if multiple departmentUser entries can link to one adminUser).
   • The $cond with $$REMOVE ensures the departmentUserinfo field is only included when a match exists (like your first sample entry for adminUserId "1").

2. Add non-matching departmentUser entries:

   • The sub-pipeline on departmentUser filters records that either have an empty adminUserId or no corresponding adminUser.
   • These entries are formatted with empty admin fields (adminUserId, userName, position) and include the full departmentUser data as departmentUserinfo (like your last two sample entries).

Result Verification #

Running this pipeline against your sample data will produce exactly the output you requested:

• Admin users 1, 2, 3 (with 2 and 3 having matching departmentUserinfo)
• Department users 1 and 4 (with empty admin fields)

Notes #

• If multiple departmentUser entries can match a single adminUser, remove the $arrayElemAt and keep departmentUserinfo as an array.
• $$REMOVE works in MongoDB 3.6+. For older versions, use $ifNull to set the field to null instead.

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