如何用MongoDB聚合结果过滤查询？AngularJS+Node中筛选特定状态文档

Got it, let's break down how to solve this problem. You need to fetch all documents where every entry with the same documentId has a status of either closed or canceled—and none of them have an open status. MongoDB's aggregation framework is the perfect tool for this, since it lets you group documents by documentId and validate conditions across the entire group.

Single Aggregation Pipeline (Atomic & Reliable) #

This approach does everything in one database call, so it's atomic and avoids race conditions if data changes between steps. Here's the pipeline:

db.yourCollection.aggregate([
  // Step 1: Group documents by documentId to track statuses
  {
    $group: {
      _id: "$documentId",
      allStatuses: { $push: "$status" }, // Collect all statuses for the group
      hasOpen: { $max: { $eq: ["$status", "open"] } }, // Flag if any status is "open"
      companyId: { $first: "$companyId" } // Optional: Preserve companyId if needed
    }
  },
  // Step 2: Filter groups that meet your criteria
  {
    $match: {
      hasOpen: false, // No documents in the group have "open" status
      $expr: {
        // All statuses in the group are either "closed" or "canceled"
        $allElementsTrue: {
          $map: {
            input: "$allStatuses",
            as: "status",
            in: { $in: ["$$status", ["closed", "canceled"]] }
          }
        }
      }
    }
  },
  // Step 3: Join back to the original collection to get full documents
  {
    $lookup: {
      from: "yourCollection",
      localField: "_id",
      foreignField: "documentId",
      as: "matchingDocuments"
    }
  },
  // Step 4: Unwrap the array of matching documents
  { $unwind: "$matchingDocuments" },
  // Step 5: Format output to return original document structure
  {
    $replaceRoot: { newRoot: "$matchingDocuments" }
  }
])

How Each Stage Works: #

• $group: Groups all documents by documentId, collects every status in the group, and sets a flag if any document has open status.
• $match: Keeps only groups where there are no open statuses, and every status is either closed or canceled.
• $lookup: Fetches all original documents that belong to the valid documentIds we found.
• $unwind: Converts the array of matching documents into individual entries.
• $replaceRoot: Restores the original document structure so your output looks like a standard find() result.

Alternative Two-Step Approach #

If you prefer a more explicit two-step process (with separate calls), you can first identify invalid documentIds, then exclude them. Note that this isn't atomic, so data could change between the two calls:

Step 1: Get Invalid Document IDs #

First, find all documentIds that have an open status or any status outside closed/canceled:

const invalidDocumentIds = db.yourCollection.aggregate([
  {
    $group: {
      _id: "$documentId",
      hasInvalidStatus: {
        $max: {
          $or: [
            { $eq: ["$status", "open"] },
            { $not: { $in: ["$status", ["closed", "canceled"]] } }
          ]
        }
      }
    }
  },
  { $match: { hasInvalidStatus: true } },
  { $project: { _id: 1 } }
]).map(doc => doc._id);

Step 2: Fetch Valid Documents #

Now retrieve all documents where documentId isn't in the invalid list, and status is valid:

db.yourCollection.find({
  documentId: { $nin: invalidDocumentIds },
  status: { $in: ["closed", "canceled"] }
});

Node.js Implementation Example #

Here's how you'd use the single aggregation pipeline with the official MongoDB driver in Node.js:

const { MongoClient } = require('mongodb');

async function fetchValidDocuments() {
  const client = await MongoClient.connect('your-mongodb-connection-string');
  const db = client.db('your-database-name');
  const collection = db.collection('your-collection-name');

  const aggregationPipeline = [
    {
      $group: {
        _id: "$documentId",
        allStatuses: { $push: "$status" },
        hasOpen: { $max: { $eq: ["$status", "open"] } }
      }
    },
    {
      $match: {
        hasOpen: false,
        $expr: {
          $allElementsTrue: {
            $map: {
              input: "$allStatuses",
              as: "status",
              in: { $in: ["$$status", ["closed", "canceled"]] }
            }
          }
        }
      }
    },
    {
      $lookup: {
        from: "your-collection-name",
        localField: "_id",
        foreignField: "documentId",
        as: "matchingDocuments"
      }
    },
    { $unwind: "$matchingDocuments" },
    { $replaceRoot: { newRoot: "$matchingDocuments" } }
  ];

  const validDocuments = await collection.aggregate(aggregationPipeline).toArray();
  await client.close();
  return validDocuments;
}

// Usage
fetchValidDocuments()
  .then(docs => console.log('Valid documents:', docs))
  .catch(err => console.error('Error fetching documents:', err));

If you're using Mongoose, the implementation is nearly identical—just call aggregate() on your Mongoose model instead of the collection object.

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