MongoDB聚合按年月(%m%Y)排序异常的解决方法咨询

问题根源 #

你用%m%Y生成的是MMYYYY格式的字符串（比如012024），字符串排序是按字符逐个比较的——01开头的字符串比06开头的小，所以2024年的月份会被排在2023年之前，不符合时间顺序。

方案一：改用YYYYMM格式（最简单直接） #

把日期格式化的格式从%m%Y改成%Y%m，生成YYYYMM格式的字符串（比如202306、202401），此时字符串排序的结果就是正确的时间顺序。

修改后的聚合管道代码：

Aggregation aggregation = Aggregation.newAggregation(
    match(Criteria.where(STATUS).nin("NEW", CANCELLED, REJECTED).and(DATE_CREATED).gte(startDate)),
    project(sumField)
        .andExpression("toDate(" + expression + ")")
        .as("formattedDate"),
    project(sumField)
        // 替换格式为%Y%m
        .and("formattedDate").dateAsFormattedString("%Y%m").as(MONTH),
    group(MONTH).sum(sumField).as(SUM),
    project(SUM).and(MONTH).as(DESCRIPTION),
    project(SUM).and(DESCRIPTION).previousOperation(),
    sort(Sort.by(Sort.Direction.ASC, DESCRIPTION))
);

AggregationResults<SumModel> results = mongoTemplate
    .aggregate(aggregation, collectionName, SumModel.class);
return results.getMappedResults();

如果需要最终返回的description还是MMYYYY格式，可以在排序后再做格式转换：

Aggregation aggregation = Aggregation.newAggregation(
    match(Criteria.where(STATUS).nin("NEW", CANCELLED, REJECTED).and(DATE_CREATED).gte(startDate)),
    project(sumField)
        .andExpression("toDate(" + expression + ")")
        .as("formattedDate"),
    // 保留原始日期，同时生成排序用的YYYYMM键
    project(sumField, "formattedDate")
        .and("formattedDate").dateAsFormattedString("%Y%m").as("sortKey"),
    group("sortKey", "formattedDate").sum(sumField).as(SUM),
    // 按YYYYMM键排序
    sort(Sort.by(Sort.Direction.ASC, "sortKey")),
    // 转换回MMYYYY格式作为description
    project(SUM)
        .and("formattedDate").dateAsFormattedString("%m%Y").as(DESCRIPTION)
);

方案二：按年份和月份数值排序 #

如果必须保留MMYYYY的分组键，可以在分组时同时提取年份和月份的数值，先按年份排序，再按月份排序：

修改后的聚合管道：

Aggregation aggregation = Aggregation.newAggregation(
    match(Criteria.where(STATUS).nin("NEW", CANCELLED, REJECTED).and(DATE_CREATED).gte(startDate)),
    project(sumField)
        .andExpression("toDate(" + expression + ")")
        .as("formattedDate"),
    project(sumField)
        .and("formattedDate").dateAsFormattedString("%m%Y").as(MONTH)
        // 提取年份、月份的数值用于排序
        .and("formattedDate").month().as("monthNum")
        .and("formattedDate").year().as("yearNum"),
    group(MONTH).sum(sumField).as(SUM)
        // 保留年份、月份数值到分组结果
        .first("yearNum").as("yearNum")
        .first("monthNum").as("monthNum"),
    // 先按年份升序，再按月份升序
    sort(Sort.by(Sort.Direction.ASC, "yearNum", "monthNum")),
    // 最终只保留需要的字段
    project(SUM).and(MONTH).as(DESCRIPTION)
);

这样排序时会先按年份从小到大，再按月份从小到大，既能得到正确的时间顺序，又能保留MMYYYY格式的description。

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