MongoDB聚合管道：排序数组并重组超集结构

以下是针对需求的完整聚合管道实现，分步骤处理两个核心需求：

需求1：排序nonSupersetted并添加训练索引 #

使用$sortArray对数组按code字段字母数字排序，再通过$map结合$range为每个元素添加从1开始的trainingIndex：

{
  $addFields: {
    "workout.nonSupersetted": {
      $let: {
        vars: {
          sortedItems: {
            $sortArray: {
              input: "$workout.nonSupersetted",
              sortBy: { code: 1 } // 按code升序，实现A1、A2、B1...的排序逻辑
            }
          }
        },
        in: {
          $map: {
            input: { $range: [0, { $size: "$$sortedItems" }] },
            as: "idx",
            in: {
              $mergeObjects: [
                { $arrayElemAt: ["$$sortedItems", "$$idx"] },
                { trainingIndex: { $add: ["$$idx", 1] } } // 训练索引从1开始计数
              ]
            }
          }
        }
      }
    }
  }
}

需求2：分组生成supersetted数组 #

基于处理后的nonSupersetted数组，按superset字段分组，再对每组内的元素按supersetIndex排序：

// 先展开排序后的nonSupersetted数组
{ $unwind: "$workout.nonSupersetted" },

// 按superset字段分组，收集对应元素
{
  $group: {
    _id: "$_id",
    nonSupersetted: { $push: "$workout.nonSupersetted" },
    supersettedGroups: {
      $push: {
        k: "$workout.nonSupersetted.superset",
        v: {
          superset: "$workout.nonSupersetted.superset",
          element: "$workout.nonSupersetted"
        }
      }
    }
  }
},

// 将分组转换为键值对，再按superset聚合元素数组
{
  $addFields: {
    supersettedGroups: { $arrayToObject: "$supersettedGroups" }
  }
},
{
  $addFields: {
    supersettedGroups: {
      $map: {
        input: { $objectToArray: "$supersettedGroups" },
        as: "group",
        in: {
          superset: "$$group.k",
          elements: {
            $sortArray: {
              input: "$$group.v.element",
              sortBy: { supersetIndex: 1 } // 按supersetIndex升序排序组内元素
            }
          }
        }
      }
    }
  }
},

// 整理最终的workout结构
{
  $addFields: {
    "workout.nonSupersetted": "$nonSupersetted",
    "workout.supersetted": "$supersettedGroups"
  }
},
{ $project: { nonSupersetted: 0, supersettedGroups: 0 } }

完整聚合管道组合 #

将上述阶段按顺序组合即可完成所有需求：

[
  // 处理nonSupersetted排序和训练索引
  {
    $addFields: {
      "workout.nonSupersetted": {
        $let: {
          vars: {
            sortedItems: {
              $sortArray: {
                input: "$workout.nonSupersetted",
                sortBy: { code: 1 }
              }
            }
          },
          in: {
            $map: {
              input: { $range: [0, { $size: "$$sortedItems" }] },
              as: "idx",
              in: {
                $mergeObjects: [
                  { $arrayElemAt: ["$$sortedItems", "$$idx"] },
                  { trainingIndex: { $add: ["$$idx", 1] } }
                ]
              }
            }
          }
        }
      }
    }
  },
  // 展开数组用于分组
  { $unwind: "$workout.nonSupersetted" },
  // 按superset分组收集元素
  {
    $group: {
      _id: "$_id",
      nonSupersetted: { $push: "$workout.nonSupersetted" },
      supersettedGroups: {
        $push: {
          k: "$workout.nonSupersetted.superset",
          v: {
            superset: "$workout.nonSupersetted.superset",
            element: "$workout.nonSupersetted"
          }
        }
      }
    }
  },
  // 转换分组结构并排序元素
  { $addFields: { supersettedGroups: { $arrayToObject: "$supersettedGroups" } } },
  {
    $addFields: {
      supersettedGroups: {
        $map: {
          input: { $objectToArray: "$supersettedGroups" },
          as: "group",
          in: {
            superset: "$$group.k",
            elements: {
              $sortArray: {
                input: "$$group.v.element",
                sortBy: { supersetIndex: 1 }
              }
            }
          }
        }
      }
    }
  },
  // 整理最终文档结构
  {
    $addFields: {
      "workout.nonSupersetted": "$nonSupersetted",
      "workout.supersetted": "$supersettedGroups"
    }
  },
  { $project: { nonSupersetted: 0, supersettedGroups: 0 } }
]

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