跨多文档对MongoDB数组数值按位置分组求和

Got it, let's break down how to solve this problem. You need to group your MongoDB documents by the group field, then sum the values at each corresponding index in the value_list arrays across all documents in the same group. Here's a step-by-step implementation using MongoDB's aggregation framework:

Step-by-Step Aggregation Pipeline #

This pipeline will first unpack your arrays while tracking their indices, compute the sums per group and index, then reconstruct the summed arrays in the correct order.

db.your_collection_name.aggregate([
  // 1. Unwind the value_list array and keep track of each element's index
  {
    $unwind: {
      path: "$value_list",
      includeArrayIndex: "element_index"
    }
  },
  // 2. Group by both group and element index to calculate the sum for each position
  {
    $group: {
      _id: {
        group: "$group",
        index: "$element_index"
      },
      total: { $sum: "$value_list" }
    }
  },
  // 3. Group back by the main group to collect all summed index values
  {
    $group: {
      _id: "$_id.group",
      indexed_totals: {
        $push: {
          index: "$_id.index",
          value: "$total"
        }
      }
    }
  },
  // 4. Unwind and sort by index to ensure the array maintains original order
  { $unwind: "$indexed_totals" },
  { $sort: { "indexed_totals.index": 1 } },
  // 5. Reconstruct the final summed value_list array
  {
    $group: {
      _id: "$_id",
      value_list: { $push: "$indexed_totals.value" }
    }
  },
  // 6. Optional: Format the output to make it cleaner (rename _id to group)
  {
    $project: {
      _id: 0,
      group: "$_id",
      value_list: 1
    }
  }
])

What Each Stage Does #

• $unwind with includeArrayIndex: This breaks down each value_list array into individual documents, each containing one element from the array plus its original index. This lets us target specific positions in the array for summing.
• First $group: Groups the unwound documents by both group and element index, calculating the total sum for that position across all documents in the group.
• Second $group: Collects all the summed index values back into an array per group, keeping track of which index each sum belongs to.
• $unwind + $sort: Ensures we reorder the summed values to match the original array's index order (critical for maintaining the correct position of each sum).
• Final $group: Reconstructs the final value_list array using the sorted summed values.
• $project: Optional step to rename the _id field to group for a more readable output.

Example Output #

For your sample documents, running this pipeline will return:

[
  { "group": "A", "value_list": [2, 4, 6, 8, 10, 12, 14] },
  { "group": "B", "value_list": [20, 40, 60, 80, 100, 120, 140] }
]

Notes on Edge Cases #

• If your documents have value_list arrays of different lengths, this pipeline will only sum values for indices that exist in at least one document in the group. If you need to fill missing indices with 0, you can add an extra stage using $fill or adjust the grouping logic to account for the maximum array length in each group.

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