MongoDB查询：投影文档直至discount累加和等于指定值n

Got it, here's how you can pull off this requirement using MongoDB's aggregation pipeline—we'll leverage window functions to calculate the running total of discounts and filter accordingly:

Full Aggregation Query #

// Replace 'your_collection' with your actual collection name
// Swap out 25 with your target value n
db.your_collection.aggregate([
  // 1. Sort documents by discount (ascending, matching your example order)
  { $sort: { discount: 1 } },
  
  // 2. Calculate cumulative discount sum with a window function
  {
    $setWindowFields: {
      partitionBy: null, // Apply to the entire collection (no grouping)
      sortBy: { discount: 1 }, // Keep the sorted order consistent
      output: {
        cumulative_discount: {
          $sum: "$discount",
          window: { documents: ["unbounded", "current"] } // Sum from first doc to current one
        }
      }
    }
  },
  
  // 3. Filter only documents where cumulative sum doesn't exceed n
  { $match: { cumulative_discount: { $lte: 25 } } },
  
  // 4. Remove the temporary cumulative field from final results
  { $project: { cumulative_discount: 0 } }
])

How This Works (With Your Examples) #

Let’s walk through what each stage does using your sample data:

• Sort Stage: Makes sure documents are ordered by discount (ascending) so our cumulative sum is calculated correctly.
• Window Function Stage: Adds a temporary cumulative_discount field that tracks the running total:
  • For document A: cumulative = 10
  • For document B: cumulative = 10 + 15 = 25
  • For document C: cumulative = 25 + 20 = 45
  • For document D: cumulative = 45 + 30 = 75
• Match Stage: Filters out any documents where the cumulative sum exceeds your target n. For n=25, this keeps A and B; for n=45, it keeps A, B, and C.
• Project Stage: Cleans up the results by removing the temporary cumulative_discount field, returning only your original document structure.

Quick Notes #

• This requires MongoDB 5.0 or later (when $setWindowFields was introduced). If you’re stuck on an older version, you’ll need a more complex workaround with $accumulator, but upgrading is strongly recommended for simplicity.
• If you need to sort by a different field instead of discount, just adjust the $sort and sortBy parameters to match your desired order.

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