MongoDB：如何基于$project字段分组，按年份聚合求和并合并结果？

Got it, let's work through this step by step. First, let's anchor this with concrete examples since you didn't share your exact document structure, but based on your description, I'll cover two common scenarios you might be dealing with.

Scenario 1: Your a field directly represents the year #

Let's say your documents look like this:

Example documents:

{ "a": "2022", "c": 200 },
{ "a": "2022", "c": 300 },
{ "a": "2023", "c": 400 },
{ "a": "2023", "c": 350 }

And you want to group by the year (from a) and sum all c values for that year. The simplest aggregation pipeline for this is:

db.yourCollection.aggregate([
  // Optional: Filter documents if needed (e.g., exclude invalid years)
  { $match: { a: { $regex: /^\d{4}$/ } } },
  // Group by year and sum the c values
  {
    $group: {
      _id: "$a", // Use the year value as the group key
      total_c: { $sum: "$c" } // Sum all c entries for the group
    }
  },
  // Optional: Sort results chronologically
  { $sort: { _id: 1 } }
])

This will give you your expected result:

[
  { "_id": "2022", "total_c": 500 },
  { "_id": "2023", "total_c": 750 }
]

Scenario 2: Your a field includes year + a type (x/y) that you need to merge #

It sounds like this might be your actual situation, since you mentioned merging x and y after a $project. Let's assume your a field looks like "2022_x" or "2022_y", and you want to sum c values across both types for the same year.

Example documents:

{ "a": "2022_x", "c": 200 },
{ "a": "2022_y", "c": 300 },
{ "a": "2023_x", "c": 400 },
{ "a": "2023_y", "c": 350 }

Here's how to extract the year, then merge the x/y values:

db.yourCollection.aggregate([
  // Step 1: Extract just the year from the `a` field
  {
    $project: {
      year: { $substr: ["$a", 0, 4] }, // Grab the first 4 characters as the year
      c: 1 // Keep the c value for summing
    }
  },
  // Step 2: Group by the extracted year and sum all c values
  {
    $group: {
      _id: "$year",
      total_c: { $sum: "$c" }
    }
  },
  // Optional: Sort results
  { $sort: { _id: 1 } }
])

If you also need to keep separate sums for x and y while merging them into the same year document, use this pipeline instead:

db.yourCollection.aggregate([
  // Split `a` into year and type (x/y)
  {
    $project: {
      year: { $substr: ["$a", 0, 4] },
      type: { $substr: ["$a", 5, -1] }, // Grab everything after the underscore
      c: 1
    }
  },
  // First group by year + type to get individual sums
  {
    $group: {
      _id: { year: "$year", type: "$type" },
      type_total: { $sum: "$c" }
    }
  },
  // Merge all types under the same year
  {
    $group: {
      _id: "$_id.year",
      type_sums: {
        $push: {
          k: "$_id.type",
          v: "$type_total"
        }
      }
    }
  },
  // Convert the type_sums array into key-value pairs and calculate total
  {
    $addFields: {
      total_c: { $sum: "$type_sums.v" },
      type_sums: { $arrayToObject: "$type_sums" }
    }
  },
  // Merge the type sums into the root document for clean output
  {
    $replaceRoot: {
      newRoot: { $mergeObjects: ["$$ROOT.type_sums", { _id: "$_id", total_c: "$total_c" }] }
    }
  }
])

This will give you a merged result like:

[
  { "_id": "2022", "x": 200, "y": 300, "total_c": 500 },
  { "_id": "2023", "x": 400, "y": 350, "total_c": 750 }
]

Why your previous attempt might have failed #

If you were trying to split x and y into separate fields in $project first, you probably overcomplicated things. The key is to either:

1. Ignore the x/y distinction entirely when extracting the year (if you just need a total per year), or
2. Use a nested $group to first calculate per-type sums, then merge them under the same year.

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