如何在MongoDB聚合管道中同时计算不同年份范围的书籍价格分组总和

Got it, let's tackle this problem. The core issue with your current pipeline is that using $match to filter for one year range discards the data needed for the other range—you can't get both sums if you've already filtered out half the records. Instead, we can filter out only the unwanted 2012 data first, then use conditional sums in the $group stage to calculate both totals in one pass.

Here's the complete aggregation pipeline that will give you the desired output:

[
  // First, filter out the 2012 entries we don't need
  {
    $match: {
      BookDate: { $not: /2012/ }
    }
  },
  // Group by BookName and calculate conditional sums for each year range
  {
    $group: {
      _id: "$BookName",
      "PriceSum2010-2011": {
        $sum: {
          $cond: [
            // Check if the BookDate falls in 2010-2011
            { $regexMatch: { input: "$BookDate", regex: /201[0-1]/ } },
            // Convert Price string to integer (critical since Price is stored as string)
            { $toInt: "$Price" },
            // Add 0 if the entry doesn't belong to this range
            0
          ]
        }
      },
      "PriceSum2013-2014": {
        $sum: {
          $cond: [
            // Check if the BookDate falls in 2013-2014
            { $regexMatch: { input: "$BookDate", regex: /201[3-4]/ } },
            { $toInt: "$Price" },
            0
          ]
        }
      }
    }
  },
  // Optional: Rename _id to BookName to match your exact desired output
  {
    $project: {
      _id: 0,
      BookName: "$_id",
      "PriceSum2010-2011": 1,
      "PriceSum2013-2014": 1
    }
  }
]

Key Details to Note: #

• Quoted Field Names: We wrap sum field names like "PriceSum2010-2011" in quotes because they contain hyphens—this avoids JavaScript syntax errors where hyphens would be interpreted as subtraction.
• $toInt Conversion: Your Price values are stored as strings (e.g., "1"), so we convert them to integers with $toInt before summing—otherwise, MongoDB would treat them as strings and concatenate instead of adding.
• Conditional Sums with $cond: This operator lets us check if a document belongs to a year range, then add its price to the corresponding total (or 0 if it doesn't belong). This way, we keep all relevant data and calculate both sums in one group operation.

Why This Fixes Your Problem: #

Instead of filtering out data for one range upfront (which breaks the other sum), we only exclude the 2012 entries we don't need. Then, in the $group stage, we dynamically route each document's price to the correct sum total.

When you run this pipeline, you'll get exactly the output you requested:

[
  { "BookName": "BOOKA", "PriceSum2010-2011": 3, "PriceSum2013-2014": 9 },
  { "BookName": "BOOKB", "PriceSum2010-2011": 13, "PriceSum2013-2014": 19 }
]

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