SpringBoot 2.X MongoTemplate 分组聚合查询一网打尽_mongotemplate分组查询_Flying9001的博客

2 按照固定字段分组查询

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
     * 按照作者分组查询博客数据
     * @param queryGroupByAuthorParam
     * @return
    @Override
    public ApiResult queryGroupByAuthor(BlogQueryGroupByAuthorParam queryGroupByAuthorParam) {
        // 查询条件
        Criteria criteria = new Criteria();
        if (StrUtil.isNotBlank(queryGroupByAuthorParam.getAuthor())) {
            criteria.and("author").is(queryGroupByAuthorParam.getAuthor());
        MatchOperation matchOperation = Aggregation.match(criteria);
        // 查询字段
        ProjectionOperation projectionOperation = Aggregation.project("id", "author", "countRead", "countLike");
        // 分组统计
        GroupOperation groupOperation = Aggregation.group("author")
                .first("author").as("author")
                .count().as("totalBlog")
                .sum("countRead").as("totalRead")
                .avg("countRead").as("aveRead")
                .sum("countLike").as("totalLike")
                .avg("countLike").as("aveLike");
        // 查询结果
        AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
                BlogEntity.class,matchOperation, projectionOperation, groupOperation), BlogSummaryVo.class);
        log.info("查询结果: {}", results.getMappedResults());
        return ApiResult.success(results.getMappedResults());
注意事项: 
分组的字段必须要在 ProjectionOperation 中 
3 分组分页查询
 
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
     * 按照作者分组分页查询你博客数据
     * @param queryGroupByAuthorPageParam
     * @return
    @Override
    public ApiResult queryGroupByAuthorPage(BlogQueryGroupByAuthorPageParam queryGroupByAuthorPageParam) {
        // 查询条件
        Criteria criteria = new Criteria();
        MatchOperation matchOperation = Aggregation.match(criteria);
        // 查询字段
        ProjectionOperation projectionOperation = Aggregation.project("id", "author","countRead", "countLike");
        // 分组统计
        GroupOperation groupOperation = Aggregation.group("author")
                .first("author").as("author")
                .count().as("totalBlog")
                .sum("countRead").as("totalRead")
                .avg("countRead").as("aveRead")
                .sum("countLike").as("totalLike")
                .avg("countLike").as("aveLike");
        // 查询总条数
        GroupOperation groupOperation2 = Aggregation.group().count().as("totalAuthor");
        AggregationResults<Map> totalAuthorResult = mongoTemplate.aggregate(Aggregation.newAggregation(BlogEntity.class,
                matchOperation, projectionOperation, groupOperation, groupOperation2), Map.class);
        int totalAuthor = (int) totalAuthorResult.getUniqueMappedResult().getOrDefault("totalAuthor", 0);
        // 设置分页信息
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.DESC, "author"));
        SkipOperation skipOperation = Aggregation.skip((queryGroupByAuthorPageParam.getCurrentPage() - 1)
                * (long) queryGroupByAuthorPageParam.getPageSize());
        LimitOperation limitOperation = Aggregation.limit(queryGroupByAuthorPageParam.getPageSize());
        // 查询结果
        AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
                BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation,
                skipOperation, limitOperation), BlogSummaryVo.class);
        // 组装分页结果
        Page<BlogSummaryVo> page = PageableExecutionUtils.getPage(results.getMappedResults(),
                PageRequest.of(queryGroupByAuthorPageParam.getCurrentPage() -1,
                        queryGroupByAuthorPageParam.getPageSize()), () -> totalAuthor);
        return ApiResult.success(page);
注意事项:




    
 
Spring 的分页类 org.springframework.data.domain.Pageable 是从 0 开始计算第一页的 
4 按照日期分组查询(字段为精确到秒的时间戳)
 
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
     * 按照客户端时间分组查询博客数据
     * @param queryGroupByClientTimestampParam
     * @return
    @Override
    public ApiResult queryGroupByClientTimestamp(BlogQueryGroupByClientTimestampParam
                                                             queryGroupByClientTimestampParam) {
        // 获取日期分组信息
        BlogGroupDateVo groupDateVo = getGroupDate(queryGroupByClientTimestampParam.getDateType());
        // 查询条件
        Criteria criteria = Criteria.where("clientTimestamp").gte(queryGroupByClientTimestampParam
                        .getMinClientTimestamp()).lte(queryGroupByClientTimestampParam.getMaxClientTimestamp());
        MatchOperation matchOperation = Aggregation.match(criteria);
        // 查询字段
        ProjectionOperation projectionOperation = Aggregation.project("countRead","countLike","clientTimestamp")
                .and(DateOperators.DateToString.dateOf(aggregationOperationContext ->
                                new Document("$add", Arrays.asList(new Date(28800000),
                                        new Document("$multiply", Arrays.asList("$clientTimestamp", 1000)))))
                        .toString(groupDateVo.getDateFormat())).as(groupDateVo.getDateGroupField());
        // 分组统计
        GroupOperation groupOperation = Aggregation.group(groupDateVo.getDateGroupField())
                .first(groupDateVo.getDateGroupField()).as("date")
                .count().as("totalBlog")
                .sum("countRead").as("totalRead")
                .avg("countRead").as("aveRead")
                .sum("countLike").as("totalLike")
                .avg("countLike").as("aveLike");
        // 排序
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "date"));
        // 查询结果
        AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
                BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation),
                BlogSummaryVo.class);
        return ApiResult.success(results.getMappedResults());
注意事项: 
MongoDB 的时间是按照 UTC 的时区进行计算的，基本单位为毫秒，国内的时区相对于 UTC 时间要早 8小时，所以在计算时间的时候需要先乘以 1000，再加 8 小时。28800000 = 8 * 60 * 60 * 1000 (毫秒) 
5 按照日期分组查询(字段为精确到毫秒的时间戳)
 
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
     * 按照创建时间分组查询博客数据
     * @param queryGroupByCreateTimeParam
     * @return
    @Override
    public ApiResult queryGroupByCreateTime(BlogQueryGroupByCreateTimeParam queryGroupByCreateTimeParam) {
        // 获取日期分组信息
        BlogGroupDateVo groupDateVo = getGroupDate(queryGroupByCreateTimeParam.getDateType());
        // 查询条件
        Criteria criteria = Criteria.where("createTime").gte(queryGroupByCreateTimeParam.getMinCreateTime())
                .lte(queryGroupByCreateTimeParam.getMaxCreateTime());
        MatchOperation matchOperation = Aggregation.match(criteria);
        // 查询字段
        ProjectionOperation projectionOperation = Aggregation.project("countRead", "countLike","createTime")
                .and(DateOperators.DateToString.dateOf(aggregationOperationContext ->
                                new Document("$add", Arrays.asList(new Date(28800000),"$createTime")))
                        .toString(groupDateVo.getDateFormat())).as(groupDateVo.getDateGroupField());
        // 分组统计
        GroupOperation groupOperation = Aggregation.group(groupDateVo.getDateGroupField())
                .first(groupDateVo.getDateGroupField()).as("date")
                .count().as("totalBlog")
                .sum("countRead").as("totalRead")
                .avg("countRead").as("aveRead")
                .sum("countLike").as("totalLike")
                .avg("countLike").as("aveLike");
        // 排序
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "date"));
        // 查询结果
        AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
                BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation),
                BlogSummaryVo.class);
        return ApiResult.success(results.getMappedResults());
注意事项:




    
 
Spring 为 MongoDB 提供的日期转字符串的操作工具类为 org.springframework.data.mongodb.core.aggregation.DateOperators ,该类提供了多种维度的时间区间，包括: 分钟、小时、天、周、月、自定义日期格式等 
6 按照日期分组查询(字段为Date)
 
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
     * 按照更新时间分组查询博客数据
     * @param queryGroupByUpdateTimeParam
     * @return
    @Override
    public ApiResult queryGroupByUpdateTime(BlogQueryGroupByUpdateTimeParam queryGroupByUpdateTimeParam) {
        // 查询条件
        Criteria criteria = Criteria.where("updateTime").gte(new Date(queryGroupByUpdateTimeParam.getMinUpdateTime()))
                .lte(new Date(queryGroupByUpdateTimeParam.getMaxUpdateTime()));
        MatchOperation matchOperation = Aggregation.match(criteria);
        // 查询字段
        ProjectionOperation projectionOperation = Aggregation.project("countRead", "countLike", "updateTime")
                .and(DateOperators.DayOfMonth.dayOfMonth("updateTime")).as("day")
                .and(DateOperators.Month.monthOf("updateTime")).as("month")
                .and(DateOperators.Year.yearOf("updateTime")).as("year");
        // 分组统计
        GroupOperation groupOperation = Aggregation.group("year","month","day")
                .addToSet("year").as("year")
                .addToSet("month").as("month")
                .addToSet("day").as("date")
                .count().as("totalBlog")
                .sum("countRead").as("totalRead")
                .avg("countRead").as("aveRead")
                .sum("countLike").as("totalLike")
                .avg("countLike").as("aveLike");
        // 排序
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "_id.year",
                        "_id.month", "_id.day"));
        // 查询结果
        AggregationResults<BlogSummaryDiyVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
                        BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation),
                BlogSummaryDiyVo.class);
        return ApiResult.success(results.getMappedResults());
注意事项:




    
 
 如果数据库字段为 java.util.Date，则可以直接从字段中获取到日期信息，不需要再进行转换
 
 多个字段排序，则需要按照 _id.field 格式书写，否则可能报错；排序优先级是按照字段从左到右的顺序，进行排序
  
7 按照自定义时间区间分组查询
 
常用的时间分组区间为小时、天、月，Spring 支持的时间区间已经足够应对，但是针对特定场景，需要自定义时间区间的时候，Spring 自带的时间区间就不够用了。作者也是查遍全网才找到解决方案，赶紧把它记下来，以免下次忘记 
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
     * 按照创建时间自定义区间分组查询博客数据
     * @param queryGroupByCreateTimeDiyParam
     * @return
    @Override
    public ApiResult queryGroupByCreateTimeDiy(BlogQueryGroupByCreateTimeDiyParam queryGroupByCreateTimeDiyParam) {
        // 查询条件
        Criteria criteria = Criteria.where("createTime").gte(queryGroupByCreateTimeDiyParam.getMinCreateTime())
                .lte(queryGroupByCreateTimeDiyParam.getMaxCreateTime());
        MatchOperation matchOperation = Aggregation.match(criteria);
        // 查询字段
        ProjectionOperation projectionOperation = Aggregation.project("countRead", "countLike","createTime")
                .and(DateOperators.Minute.minuteOf(aggregationOperationContext ->
                                new Document("$add", Arrays.asList(new Date(28800000),"$createTime"))))
                        .as("minute")
                .and(DateOperators.Hour.hourOf(aggregationOperationContext ->
                        new Document("$add", Arrays.asList(new Date(28800000),"$createTime"))))
                .as("hour")
                .and(DateOperators.DateToString.dateOf(aggregationOperationContext ->
                                new Document("$add", Arrays.asList(new Date(28800000),"$createTime")))
                        .toString("%Y-%m-%d")).as("day");
        ProjectionOperation projectionOperation2 = Aggregation.project("countRead", "countLike",
                "createTime", "minute", "hour","day")
                .andExpression("minute - minute % 30").as("halfHour");
        // 分组统计
        GroupOperation groupOperation = Aggregation.group("day","hour","halfHour")
                .addToSet("hour").as("hour")
                .addToSet("day").as("date")
                .addToSet("halfHour").as("minute")
                .count().as("totalBlog")
                .sum("countRead").as("totalRead")
                .avg("countRead").as("aveRead")
                .sum("countLike").as("totalLike")
                .avg("countLike").as("aveLike");
        // 排序
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "day", "hour", "halfHour"));
        // 查询结果
        AggregationResults<BlogSummaryDiyVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
                        BlogEntity.class, matchOperation, projectionOperation, projectionOperation2,
                        groupOperation, sortOperation), BlogSummaryDiyVo.class);
        return ApiResult.success(results.getMappedResults());
注意事项:




    
 
 在第二个 ProjectionOperation 中必须包含上一个的所有字段
 
 本示例中的时间区间为 30 分钟
  
8 数据库实体类及其他相关类
 
8.1 数据库实体类
 
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/entity/BaseEntity.java
package com.ljq.demo.springboot.mongodb.model.entity;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.Id;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.mongodb.core.mapping.Field;
import java.io.Serializable;
import java.util.Date;
 * @Description: 基础实体类
 * @Author: junqiang.lu
 * @Date: 2021/9/24
@Data
public class BaseEntity implements Serializable {
    private static final long serialVersionUID = -3003658740476069858L;
     * id,主键
    @ApiModelProperty(value = "id,主键", name = "id")
    private String id;
     * 创建时间
    @Field
    @CreatedDate
    @ApiModelProperty(value = "创建时间", name = "createTime")
    private Long createTime;
     * 修改时间
    @Field
    @LastModifiedDate
    @ApiModelProperty(value = "修改时间", name = "updateTime")
    private Date updateTime;
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/entity/BlogEntity.java
package com.ljq.demo.springboot.mongodb.model.entity;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.ToString;
import org.springframework.data.mongodb.core.mapping.Document;
import org.springframework.data.mongodb.core.mapping.Field;
 * @Description: 博客信息
 * @Author: junqiang.lu
 * @Date: 2021/11/13
@Data
@ToString(callSuper = true)
@Document(value = "blog")
@ApiModel(value = "博客信息", description = "博客信息")
public class BlogEntity extends BaseEntity {
    private static final long serialVersionUID = -2124422309475024490L;
    @Field
    @ApiModelProperty(value = "标题", name = "title")
    private String title;
    @Field
    @ApiModelProperty(value = "作者", name = "author")
    private String author;
    @Field
    @ApiModelProperty(value = "内容", name = "content")
    private String content;
     * 阅读数量
    @Field
    @ApiModelProperty(value = "阅读数量", name = "countRead")
    private Integer countRead;
     * 点赞数量
    @Field
    @ApiModelProperty(value = "点赞数量", name = "countLike")
    private Integer countLike;
     * 客户端时间戳(精确到秒)
    @Field
    @ApiModelProperty(value = "客户端时间戳(精确到秒)", name = "clientTimestamp")
    private Integer clientTimestamp;
8.2 统计结果类
 
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/vo/BlogSummaryVo.java
package com.ljq.demo.springboot.mongodb.model.vo;
import com.fasterxml.jackson.annotation.JsonInclude;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
 * @Description: 博客统计结果
 * @Author: junqiang.lu
 * @Date: 2021/11/15
@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@ApiModel(value = "博客统计结果", description = "博客统计结果")
public class BlogSummaryVo implements Serializable {
    private static final long serialVersionUID = 6350918498981106620L;
    @ApiModelProperty(value = "作者", name = "author")
    private String author;
     * 总博客数量
    @ApiModelProperty(value = "总博客数量", name = "totalBlog")
    private Long totalBlog;
     * 总阅读量
    @ApiModelProperty(value = "总阅读量", name = "totalRead")
    private Long totalRead;
     * 平均阅读量
    @ApiModelProperty(value = "平均阅读量", name = "aveRead")
    private Long aveRead;
     * 总点赞数量
    @ApiModelProperty(value = "总点赞数量", name = "totalLike")
    private Long totalLike;
     * 平均点赞数量
    @ApiModelProperty(value = "平均点赞数量", name = "aveLike")
    private Long aveLike;
    @ApiModelProperty(value = "日期", name = "date")
    private String date;
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/vo/BlogSummaryDiyVo.java
package com.ljq.demo.springboot.mongodb.model.vo;
import com.fasterxml.jackson.annotation.JsonInclude;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
 * @Description: 博客自定义时间区间统计结果
 * @Author: junqiang.lu
 * @Date: 2021/11/18
@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@ApiModel(value = "博客自定义时间区间统计结果", description = "博客自定义时间区间统计结果")
public class BlogSummaryDiyVo extends BlogSummaryVo {
    private static final long serialVersionUID = 6688850910361408341L;
    @ApiModelProperty(value = "年", name = "year")
    private String year;
    @ApiModelProperty(value = "月", name = "month")
    private String month;
    @ApiModelProperty(value = "小时", name = "day")
    private String hour;
    @ApiModelProperty(value = "分钟", name = "minute")
    private String minute;
8.3 日期分组信息类
 
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/vo/BlogGroupDateVo.java
package com.ljq.demo.springboot.mongodb.model.vo;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
 * @Description: 博客日期分组信息
 * @Author: junqiang.lu
 * @Date: 2021/11/19
@Data
@ApiModel(value = "博客日期分组信息", description = "博客日期分组信息")
public class BlogGroupDateVo implements Serializable {
    private static final long serialVersionUID = -4828325421504898453L;
     * 日期分组字段
    @ApiModelProperty(value = "日期分组字段", name = "dateGroupField")
    private String dateGroupField;
     * 日期格式
    @ApiModelProperty(value = "日期格式", name = "dateFormat")
    private String dateFormat;
8.4 获取日期分组信息的方法
 
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
     * 获取博客日期分组信息
     * @param dateType
     * @return
    private BlogGroupDateVo getGroupDate(Integer dateType) {
        BlogGroupDateVo groupDateVo = new BlogGroupDateVo();
        switch (dateType) {
            case BlogConst.DATE_TYPE_DAY:
                groupDateVo.setDateGroupField("day");
                groupDateVo.setDateFormat("%Y-%m-%d");
                break;
            case BlogConst.DATE_TYPE_MONTH:
                groupDateVo.setDateGroupField("month");
                groupDateVo.setDateFormat("%Y-%m");
                break;
            default:
                groupDateVo.setDateGroupField("day");
                groupDateVo.setDateFormat("%Y-%m-%d");
                break;
        return groupDateVo;
9 推荐参考资料
 
使用mongoTemplate进行Aggregation聚合查询




    
 
springboot使用MongoTemplate分组统计数据 
SpringBoot之MongoTemplate的查询可以怎么耍 
java 使用mongoTemplate 按月分组、聚合的实现 （要求返回非分组字段）_deelness的博客-程序员宅基地 
Group by time interval spring-data-mongo 
关于MongoDB时间格式转换和时间段聚合统计的用法总结 
spring mongodb 模糊查询 
Mongodb系列- spring-data-mongodb使用MongoTemplate实现分页查询 
记一次使用mongoTemplate的Aggregation类进行分组，分页操作 
mongodb高级聚合查询 
mongoDB对时间的处理ISODate与咱们时区相差8小时 
5 Github 源码
 
Gtihub 源码地址 : https://github.com/Flying9001/springBootDemo 
个人公众号:404Code,分享半个互联网人的技术与思考，感兴趣的可以关注.
 
                    文章目录1 摘要2 按照固定字段分组查询3 分组分页查询4 按照日期分组查询(字段为精确到秒的时间戳)5 按照日期分组查询(字段为精确到毫秒的时间戳)6 按照日期分组查询(字段为Date)7 按照自定义时间区间分组查询8 数据库实体类及其他相关类8.1 数据库实体类8.2 统计结果类8.3 日期分组信息类8.4 获取日期分组信息的方法9 推荐参考资料10 本次提交记录1 摘要MongoDb 如何实现聚合查询？分组查询？分组分页查询？自定义时间区间查询？时间格式转换查询？不要慌，本文将介绍基于 S
//条件一 where gradeId in (集合) and mark=true                   
Criteria criteria=Criteria.where("gradeId").in(gradeIds).and("mark").is(true);
        if(null != status && status.size() >0){
//object是泛型，是定义的数据库映射类
private List<Object> getArchiveDataList(Request request) {
        TypedAggregation<Object> tagg = TypedAggregation.newAggregation(Object.class,
                Arrays.asList
@Document("Schedule")
@Accessors(chain = true)
public class Schedule extends BaseMongoEntity {
	private static final long serialVersionUID = 1L;
	@ApiModelProperty(value = "医院编号")
	@Indexed //.
1.聚合的表达式
MongoDB中聚合(aggregate)主要用于处理数据(诸如统计平均值,求和等)，并返回计算后的数据结果。有点类似sql语句中的 count(*)。
下表展示了一些聚合的表达式:
表达式描述实例
计算总和。
db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial...
				好的，我可以回答这个问题。在使用 Spring Boot 和 MongoDB 进行三表连接时，可以使用 MongoTemplate 类来实现。首先，需要定义三个实体类，分别对应三个集合。然后，使用 MongoTemplate 的 aggregate 方法进行聚合操作，通过 $lookup 和 $match 等操作符实现三表连接。具体实现可以参考以下代码：
@Autowired
private MongoTemplate mongoTemplate;
public List<ThirdTable> findThirdTableByJoin() {
    Aggregation aggregation = Aggregation.newAggregation(
            Aggregation.lookup("secondTable", "secondTableId", "id", "secondTable"),
            Aggregation.unwind("secondTable"),
            Aggregation.lookup("firstTable", "secondTable.firstTableId", "id", "firstTable"),
            Aggregation.unwind("firstTable"),
            Aggregation.match(Criteria.where("firstTable.name").is("xxx"))
    AggregationResults<ThirdTable> results = mongoTemplate.aggregate(aggregation, "thirdTable", ThirdTable.class);
    return results.getMappedResults();
以上代码中，"thirdTable"、"secondTable"、"firstTable" 分别对应三个集合的名称，"secondTableId"、"firstTableId" 分别对应第二个表和第三个表中与第一个表关联的字段，"name" 是第一个表中的一个字段，用于筛选符合条件的记录。最终返回的是符合条件的第三个表的记录列表。