1 Summary
How does MongoDB implement aggregate queries? Group query? Group paging query? User defined time interval query? Time format conversion query? Don't panic. This article will introduce the implementation of grouping and aggregation query of various dimensions based on SpringBoot 2.X MongoTemplate, and catch all the complex query difficulties of MongoDB.
2. Group query by fixed field
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* Query blog data by author group
*
* @param queryGroupByAuthorParam
* @return
*/
@Override
public ApiResult queryGroupByAuthor(BlogQueryGroupByAuthorParam queryGroupByAuthorParam) {
// query criteria
Criteria criteria = new Criteria();
if (StrUtil.isNotBlank(queryGroupByAuthorParam.getAuthor())) {
criteria.and("author").is(queryGroupByAuthorParam.getAuthor());
}
MatchOperation matchOperation = Aggregation.match(criteria);
// Query field
ProjectionOperation projectionOperation = Aggregation.project("id", "author", "countRead", "countLike");
// Grouping statistics
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");
// Query results
AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
BlogEntity.class,matchOperation, projectionOperation, groupOperation), BlogSummaryVo.class);
log.info("Query results: {}", results.getMappedResults());
return ApiResult.success(results.getMappedResults());
}
matters needing attention:
Grouped fields must be in ProjectionOperation
3 grouping and paging query
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* Query your blog data in groups and pages according to authors
*
* @param queryGroupByAuthorPageParam
* @return
*/
@Override
public ApiResult queryGroupByAuthorPage(BlogQueryGroupByAuthorPageParam queryGroupByAuthorPageParam) {
// query criteria
Criteria criteria = new Criteria();
MatchOperation matchOperation = Aggregation.match(criteria);
// Query field
ProjectionOperation projectionOperation = Aggregation.project("id", "author","countRead", "countLike");
// Grouping statistics
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");
// Total number of queries
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);
// Set paging information
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());
// Query results
AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation,
skipOperation, limitOperation), BlogSummaryVo.class);
// Assembly paging results
Page<BlogSummaryVo> page = PageableExecutionUtils.getPage(results.getMappedResults(),
PageRequest.of(queryGroupByAuthorPageParam.getCurrentPage() -1,
queryGroupByAuthorPageParam.getPageSize()), () -> totalAuthor);
return ApiResult.success(page);
}
matters needing attention:
Spring's paging class org.springframework.data.domain.Pageable calculates the first page from 0
4. Query by date group (the field is the timestamp accurate to seconds)
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* Query blog data by client time group
*
* @param queryGroupByClientTimestampParam
* @return
*/
@Override
public ApiResult queryGroupByClientTimestamp(BlogQueryGroupByClientTimestampParam
queryGroupByClientTimestampParam) {
// Get date grouping information
BlogGroupDateVo groupDateVo = getGroupDate(queryGroupByClientTimestampParam.getDateType());
// query criteria
Criteria criteria = Criteria.where("clientTimestamp").gte(queryGroupByClientTimestampParam
.getMinClientTimestamp()).lte(queryGroupByClientTimestampParam.getMaxClientTimestamp());
MatchOperation matchOperation = Aggregation.match(criteria);
// Query field
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());
// Grouping statistics
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");
// sort
SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "date"));
// Query results
AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation),
BlogSummaryVo.class);
return ApiResult.success(results.getMappedResults());
}
matters needing attention:
MongoDB time is calculated according to UTC time zone. The basic unit is milliseconds. The domestic time zone is 8 hours earlier than UTC time, so it needs to be multiplied by 1000 and then 8 hours. 28800000 = 8 * 60 * 60 * 1000 (MS)
5 query by date group (the field is the timestamp accurate to milliseconds)
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* Query blog data by creation time
*
* @param queryGroupByCreateTimeParam
* @return
*/
@Override
public ApiResult queryGroupByCreateTime(BlogQueryGroupByCreateTimeParam queryGroupByCreateTimeParam) {
// Get date grouping information
BlogGroupDateVo groupDateVo = getGroupDate(queryGroupByCreateTimeParam.getDateType());
// query criteria
Criteria criteria = Criteria.where("createTime").gte(queryGroupByCreateTimeParam.getMinCreateTime())
.lte(queryGroupByCreateTimeParam.getMaxCreateTime());
MatchOperation matchOperation = Aggregation.match(criteria);
// Query field
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());
// Grouping statistics
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");
// sort
SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "date"));
// Query results
AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation),
BlogSummaryVo.class);
return ApiResult.success(results.getMappedResults());
}
matters needing attention:
The operation tool class of date to string provided by Spring for MongoDB is org.springframework.data.mongodb.core.aggregation.DateOperators. This class provides time intervals of multiple dimensions, including minutes, hours, days, weeks, months, custom date formats, etc
6 group query by date (field is Date)
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* Query blog data in groups according to update time
*
* @param queryGroupByUpdateTimeParam
* @return
*/
@Override
public ApiResult queryGroupByUpdateTime(BlogQueryGroupByUpdateTimeParam queryGroupByUpdateTimeParam) {
// query criteria
Criteria criteria = Criteria.where("updateTime").gte(new Date(queryGroupByUpdateTimeParam.getMinUpdateTime()))
.lte(new Date(queryGroupByUpdateTimeParam.getMaxUpdateTime()));
MatchOperation matchOperation = Aggregation.match(criteria);
// Query field
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");
// Grouping statistics
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");
// sort
SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "_id.year",
"_id.month", "_id.day"));
// Query results
AggregationResults<BlogSummaryDiyVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation),
BlogSummaryDiyVo.class);
return ApiResult.success(results.getMappedResults());
}
matters needing attention:
-
If the database field is java.util.Date, the date information can be obtained directly from the field without conversion
-
To sort multiple fields, you need to sort by_ Write in id.field format, otherwise an error may be reported; Sorting priority is to sort the fields from left to right
7 group query by user-defined time interval
The commonly used time grouping intervals are hour, day and month. The time interval supported by Spring is enough. However, when the time interval needs to be customized for specific scenarios, the time interval provided by Spring is not enough. The author also checked the whole network to find a solution. Write it down quickly so as not to forget it next time
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* Query blog data by user-defined interval based on creation time
*
* @param queryGroupByCreateTimeDiyParam
* @return
*/
@Override
public ApiResult queryGroupByCreateTimeDiy(BlogQueryGroupByCreateTimeDiyParam queryGroupByCreateTimeDiyParam) {
// query criteria
Criteria criteria = Criteria.where("createTime").gte(queryGroupByCreateTimeDiyParam.getMinCreateTime())
.lte(queryGroupByCreateTimeDiyParam.getMaxCreateTime());
MatchOperation matchOperation = Aggregation.match(criteria);
// Query field
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");
// Grouping statistics
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");
// sort
SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "day", "hour", "halfHour"));
// Query results
AggregationResults<BlogSummaryDiyVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
BlogEntity.class, matchOperation, projectionOperation, projectionOperation2,
groupOperation, sortOperation), BlogSummaryDiyVo.class);
return ApiResult.success(results.getMappedResults());
}
matters needing attention:
-
All fields of the previous must be included in the second ProjectionOperation
-
The time interval in this example is 30 minutes
8 database entity class and other related classes
8.1 database entity class
./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: Base entity class
* @Author: junqiang.lu
* @Date: 2021/9/24
*/
@Data
public class BaseEntity implements Serializable {
private static final long serialVersionUID = -3003658740476069858L;
/**
* id,Primary key
*/
@Id
@ApiModelProperty(value = "id,Primary key", name = "id")
private String id;
/**
* Creation time
*/
@Field
@CreatedDate
@ApiModelProperty(value = "Creation time", name = "createTime")
private Long createTime;
/**
* Modification time
*/
@Field
@LastModifiedDate
@ApiModelProperty(value = "Modification time", 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: Blog information
* @Author: junqiang.lu
* @Date: 2021/11/13
*/
@Data
@ToString(callSuper = true)
@Document(value = "blog")
@ApiModel(value = "Blog information", description = "Blog information")
public class BlogEntity extends BaseEntity {
private static final long serialVersionUID = -2124422309475024490L;
/**
* title
*/
@Field
@ApiModelProperty(value = "title", name = "title")
private String title;
/**
* author
*/
@Field
@ApiModelProperty(value = "author", name = "author")
private String author;
/**
* content
*/
@Field
@ApiModelProperty(value = "content", name = "content")
private String content;
/**
* Number of readings
*/
@Field
@ApiModelProperty(value = "Number of readings", name = "countRead")
private Integer countRead;
/**
* Number of likes
*/
@Field
@ApiModelProperty(value = "Number of likes", name = "countLike")
private Integer countLike;
/**
* Client timestamp (accurate to seconds)
*/
@Field
@ApiModelProperty(value = "Client timestamp(Accurate to seconds)", name = "clientTimestamp")
private Integer clientTimestamp;
}
8.2 statistical results
./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: Blog statistics
* @Author: junqiang.lu
* @Date: 2021/11/15
*/
@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@ApiModel(value = "Blog statistics", description = "Blog statistics")
public class BlogSummaryVo implements Serializable {
private static final long serialVersionUID = 6350918498981106620L;
/**
* author
*/
@ApiModelProperty(value = "author", name = "author")
private String author;
/**
* Total number of blogs
*/
@ApiModelProperty(value = "Total number of blogs", name = "totalBlog")
private Long totalBlog;
/**
* Total reading
*/
@ApiModelProperty(value = "Total reading", name = "totalRead")
private Long totalRead;
/**
* Average reading
*/
@ApiModelProperty(value = "Average reading", name = "aveRead")
private Long aveRead;
/**
* Total likes
*/
@ApiModelProperty(value = "Total likes", name = "totalLike")
private Long totalLike;
/**
* Average number of likes
*/
@ApiModelProperty(value = "Average number of likes", name = "aveLike")
private Long aveLike;
/**
* date
*/
@ApiModelProperty(value = "date", 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: Blog custom time interval statistics results
* @Author: junqiang.lu
* @Date: 2021/11/18
*/
@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@ApiModel(value = "Blog custom time interval statistics results", description = "Blog custom time interval statistics results")
public class BlogSummaryDiyVo extends BlogSummaryVo {
private static final long serialVersionUID = 6688850910361408341L;
/**
* year
*/
@ApiModelProperty(value = "year", name = "year")
private String year;
/**
* month
*/
@ApiModelProperty(value = "month", name = "month")
private String month;
/**
* hour
*/
@ApiModelProperty(value = "hour", name = "day")
private String hour;
/**
* minute
*/
@ApiModelProperty(value = "minute", name = "minute")
private String minute;
}
8.3 date grouping information
./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: Blog date grouping information
* @Author: junqiang.lu
* @Date: 2021/11/19
*/
@Data
@ApiModel(value = "Blog date grouping information", description = "Blog date grouping information")
public class BlogGroupDateVo implements Serializable {
private static final long serialVersionUID = -4828325421504898453L;
/**
* Date grouping field
*/
@ApiModelProperty(value = "Date grouping field", name = "dateGroupField")
private String dateGroupField;
/**
* Date format
*/
@ApiModelProperty(value = "Date format", name = "dateFormat")
private String dateFormat;
}
8.4 method of obtaining date grouping information
./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java
/**
* Get blog date grouping information
*
* @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 recommended references
Aggregate query using mongoTemplate
springboot uses MongoTemplate to group statistics
How can I query the MongoTemplate of SpringBoot
Group by time interval spring-data-mongo
Usage Summary of MongoDB time format conversion and time period aggregation statistics
Mongodb series - spring data mongodb uses MongoTemplate to realize paging query
Remember to use the Aggregation class of mongoTemplate once for grouping and paging operations
mongodb advanced aggregate query
mongoDB's processing of time ISODate differs from our time zone by 8 hours
5 Github source code
Gtihub source address: https://github.com/Flying9001/springBootDemo
The official account number: 404Code, sharing half of the technology and thinking of Internet people, and interested in it.
