Spring Data Couchbase Reactive嵌套查询：返回计数与对象列表修正

原查询通过笛卡尔积关联统计子查询和分页数据子查询，导致每条Person记录都重复携带totalCount，无法直接映射到包含totalCount和persons列表的QueryResult对象。可以通过将分页数据聚合为数组的方式修正查询：

修正后的N1QL查询 #

WITH subquery1 AS (
  SELECT COUNT(*) AS total_count
  FROM bucketName
  WHERE condition1
),
subquery2 AS (
  SELECT name, address, email
  FROM bucketName
  WHERE condition2
  OFFSET $offset LIMIT $limit
)
SELECT s1.total_count AS totalCount, ARRAY_AGG(s2) AS persons
FROM subquery1 AS s1, subquery2 AS s2
GROUP BY s1.total_count;

关键修改说明 #

• 使用ARRAY_AGG(s2)将subquery2返回的所有Person记录聚合为一个数组，命名为persons
• 通过GROUP BY s1.total_count确保只返回一条包含总计数和人员列表的结果，彻底避免笛卡尔积带来的重复数据

Spring Data Reactive Couchbase 适配 #

确保仓库接口的方法返回类型为Mono<QueryResult>，Spring Data会自动将单条查询结果映射到你的POJO：

@Repository
public interface PersonRepository extends ReactiveCouchbaseRepository<Person, String> {
    @Query("WITH subquery1 AS (SELECT COUNT(*) AS total_count FROM bucketName WHERE condition1), subquery2 AS (SELECT name, address, email FROM bucketName WHERE condition2 OFFSET $offset LIMIT $limit) SELECT s1.total_count AS totalCount, ARRAY_AGG(s2) AS persons FROM subquery1 AS s1, subquery2 AS s2 GROUP BY s1.total_count")
    Mono<QueryResult> findPersonsWithTotalCount(@Param("offset") int offset, @Param("limit") int limit);
}

注意：请将bucketName、condition1、condition2替换为实际的桶名和查询条件，确保POJO字段名与查询返回的别名（totalCount、persons）完全一致。

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