如何用Spring Data MongoDB实现聚合投影中$substr的复杂表达式

一、规范实现（类型安全操作符） #

替代原生表达式写法，用Spring Data提供的StringOperators和ArithmeticOperators完成字段投影，避免硬编码MongoDB语法：

import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationResults;
import org.springframework.data.mongodb.core.aggregation.ArithmeticOperators;
import org.springframework.data.mongodb.core.aggregation.GroupOperation;
import org.springframework.data.mongodb.core.aggregation.ProjectionOperation;
import org.springframework.data.mongodb.core.MongoTemplate;

// 聚合结果映射类
class PartnerOfficeStats {
    private String partnerid;
    private String market;
    private String office;
    private Long clickCount;

    // 生成Getter、Setter方法
}

public class AggregationService {
    private final MongoTemplate mongoTemplate;

    // 通过构造注入MongoTemplate
    public AggregationService(MongoTemplate mongoTemplate) {
        this.mongoTemplate = mongoTemplate;
    }

    public AggregationResults<PartnerOfficeStats> getPartnerClickStats() {
        // 构建market字段：截取pseudocitycode前3位并转大写
        StringOperators.ToUpper marketExpr = StringOperators.valueOf("pseudocitycode")
                .substring(0, 3)
                .toUpper();

        // 构建office字段：计算起始位置（字符串长度-2），截取后2位并转大写
        ArithmeticOperators.Subtract startPos = ArithmeticOperators
                .valueOf(StringOperators.valueOf("pseudocitycode").lengthCP())
                .subtract(2);
        StringOperators.ToUpper officeExpr = StringOperators.valueOf("pseudocitycode")
                .substring(startPos, 2)
                .toUpper();

        // 投影阶段
        ProjectionOperation projection = Aggregation.project("partnerid")
                .and(marketExpr).as("market")
                .and(officeExpr).as("office");

        // 分组阶段：按partnerid、market、office分组，统计点击数
        GroupOperation group = Aggregation.group("partnerid", "market", "office")
                .count().as("clickCount");

        // 构建聚合管道并执行
        Aggregation aggregation = Aggregation.newAggregation(projection, group);
        return mongoTemplate.aggregate(aggregation, "collection", PartnerOfficeStats.class);
    }
}

二、兼容原有表达式写法（快速适配） #

如果想保留你原来的andExpression写法，只需要补充完整的聚合流程即可：

import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationResults;
import org.springframework.data.mongodb.core.aggregation.GroupOperation;
import org.springframework.data.mongodb.core.aggregation.ProjectionOperation;
import org.springframework.data.mongodb.core.MongoTemplate;

public class AggregationService {
    private final MongoTemplate mongoTemplate;

    public AggregationService(MongoTemplate mongoTemplate) {
        this.mongoTemplate = mongoTemplate;
    }

    public AggregationResults<PartnerOfficeStats> getPartnerClickStats() {
        StringOperators.Substr market = StringOperators.valueOf("pseudocitycode").substring(0, 3);
        
        ProjectionOperation projectionOperation = Aggregation.project("partnerid")
                .and(StringOperators.valueOf(market).toUpper()).as("market")
                .andExpression("{$toUpper:[{$substr:['$pseudocitycode',{$subtract:[{$strLenCP:'$pseudocitycode'},2]},2]}]}").as("office");

        GroupOperation group = Aggregation.group("partnerid", "market", "office")
                .count().as("clickCount");

        Aggregation aggregation = Aggregation.newAggregation(projectionOperation, group);
        return mongoTemplate.aggregate(aggregation, "collection", PartnerOfficeStats.class);
    }
}

说明 #

• 两种写法功能完全等价，推荐第一种类型安全的操作符链式写法，便于维护和避免语法错误。
• PartnerOfficeStats类用于映射聚合结果，字段名需和投影、分组后的字段名对应。
• 替换代码中的"collection"为你的实际MongoDB集合名称。

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