如何对比MongoDB的Payment与Wallet集合，找出未生成Wallet记录的Payment文档

Hey there! Your current aggregation is on the right track with $lookup, but it's missing a key step to filter out the Payments that actually have no matching Wallet entries. Let's adjust it to get exactly what you need.

The Problem with Your Current Query #

Right now, your $lookup is joining Payment with Wallet, but it returns all Payment documents—including those that do have a matching Wallet record. The empty missedWallet arrays are the ones we care about, but we need to explicitly filter for them.

Fixed Aggregation Query #

Here's the adjusted code that will return only Payment documents with no corresponding Wallet record:

const missedPayments = await Payment.aggregate([
  // Join Payment with Wallet using paymentId as the foreign key
  {
    $lookup: {
      from: "Wallet",
      localField: "_id",
      foreignField: "paymentId",
      as: "walletMatch"
    }
  },
  // Filter only Payments where no Wallet match was found (empty array)
  {
    $match: {
      "walletMatch": []
    }
  },
  // Optional: Remove the empty walletMatch field to clean up results
  {
    $project: {
      walletMatch: 0
    }
  },
  // Keep your original sort to get the most recent missing payments first
  {
    $sort: { createdAt: -1 }
  }
]);

Breakdown of Each Step #

• $lookup: This step links each Payment document to any matching Wallet records (using _id from Payment and paymentId from Wallet). If there's no match, walletMatch will be an empty array.
• $match: This filters the results to only keep documents where walletMatch is empty—these are exactly the Payments that didn't get a corresponding Wallet entry.
• $project (optional): Removes the empty walletMatch field from the final results to keep the output clean (you can skip this if you want to confirm the array is empty).
• $sort: Retains your original sorting to show the most recent missing payments first.

Performance Tip #

To speed up the $lookup operation (especially as your collection grows), make sure you have an index on the paymentId field in the Wallet collection:

db.Wallet.createIndex({ paymentId: 1 });

This will make the join operation much faster, even with large datasets.

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