Spring Data MongoDB使用users.$位置投影结合分页报错求助

错误日志 #

org.springframework.data.mongodb.UncategorizedMongoDbException: Query failed with error code 31324 with name 'Location31324' and error message 'Invalid $project :: caused by :: Cannot use positional projection in aggregation projection' on server localhost:27017; nested exception is com.mongodb.MongoQueryException: Query failed with error code 31324 with name 'Location31324' and error message 'Invalid $project :: caused by :: Cannot use positional projection in aggregation projection' on server localhost:27017
    at org.springframework.data.mongodb.core.MongoExceptionTranslator.translateExceptionIfPossible(MongoExceptionTranslator.java:140)
    at org.springframework.data.mongodb.core.MongoTemplate.potentiallyConvertRuntimeException(MongoTemplate.java:3048)
    at org.springframework.data.mongodb.core.MongoTemplate.executeFindMultiInternal(MongoTemplate.java:2984)
    at org.springframework.data.mongodb.core.MongoTemplate.doFind(MongoTemplate.java:2670)
    at org.springframework.data.mongodb.core.MongoTemplate.doFind(MongoTemplate.java:2652)
    at org.springframework.data.mongodb.core.MongoTemplate.find(MongoTemplate.java:902)
    at AlertService.getAlertByUserLogin(AlertService.java:90)
    at AlertResource.findAlertsByUser(AlertResource.java:32)

业务代码 #

Document alerts = new Document();
Query query = new Query(Criteria.where("users.userId").is(userLogin));
query
    .fields()
    .include("name", "condition", "locationId", "ruleId", "severity", "timestamp", "users.$");
query.with(pageable);
List<Alert> result = mongo.find(query, Alert.class, "alert");

需求 #

查询所有匹配users.userId = userLogin的文档，同时仅返回users数组中匹配的对象，而非整个数组。

错误原因 #

当查询中使用分页（pageable会生成skip和limit参数）时，MongoDB禁止在投影中使用位置操作符$。位置操作符依赖查询阶段的匹配结果直接关联数组元素，分页会破坏这种关联逻辑，导致投影规则失效。

替代实现：使用聚合管道 #

通过MongoDB聚合管道可以同时实现文档筛选、数组元素过滤和分页需求，代码示例如下：

Aggregation aggregation = Aggregation.newAggregation(
    // 1. 筛选出包含目标用户的告警文档
    Aggregation.match(Criteria.where("users.userId").is(userLogin)),
    // 2. 投影指定字段，并过滤users数组只保留匹配的元素
    Aggregation.project("name", "condition", "locationId", "ruleId", "severity", "timestamp")
        .and(Aggregation.filter("users", 
            Criteria.where("userId").is(userLogin).getCriteriaObject()
        )).as("users"),
    // 3. 应用分页逻辑
    Aggregation.skip(pageable.getOffset()),
    Aggregation.limit(pageable.getPageSize())
);

AggregationResults<Alert> results = mongoTemplate.aggregate(aggregation, "alert", Alert.class);
List<Alert> result = results.getMappedResults();

可选优化：返回单个匹配元素而非数组 #

如果需要将users字段返回为单个对象（而非数组），可以在投影阶段结合$arrayElemAt获取过滤后数组的第一个元素：

Aggregation.project("name", "condition", "locationId", "ruleId", "severity", "timestamp")
    .and(Aggregation.arrayElemAt(
        Aggregation.filter("users", Criteria.where("userId").is(userLogin).getCriteriaObject()),
        0
    )).as("users")

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