mongodb在Java中条件分组聚合查询并且分页(时间戳，按日期分组，年月日...)

废话不多说，先看效果图：

• SQL查询结果示例：
  
• 多种查询结果示例：
  

原SQL：

db.getCollection("hbdd_order").aggregate([{// 把时间戳格式化$addFields: {orderDate: {"$dateToString": {"format": "%Y-%m-%d","date": {"$toDate": "$hzdd_order_addtime"}}}}},{$match: {// 筛选条件hzdd_order_addtime: {$gte: 1722441600000,$lt: 1725120000000}}},{// 按格式过的时间分组$group: {"_id": "$orderDate",paidAmount: {$sum: { // 统计$cond: [{ // 条件类似if true =1 else =0$eq: ["$hzdd_order_ispay", 1]}, "$hzdd_order_amount", 0]}},paidCount: {$sum: {$cond: [{$eq: ["$hzdd_order_ispay", 1]}, 1, 0]}},unpaidAmount: {$sum: {$cond: [{$eq: ["$hzdd_order_ispay", 0]}, "$hzdd_order_amount", 0]}},unpaidCount: {$sum: {$cond: [{$eq: ["$hzdd_order_ispay", 0]}, 1, 0]}}}},{$project: {date: "$_id",paidAmount: 1,paidCount: 1,unpaidAmount: 1,unpaidCount: 1}},{$sort: { // 排序date: 1}}
]);

Java语句：

代码中多了些内容，但是和SQL语句大差不差
（懒得替换类名，大家看到陌生的类就是自己建的）

import com.mongodb.client.result.UpdateResult;
import jodd.util.StringUtil;
import org.bson.Document;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.*;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.*;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.stereotype.Service;import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.Optional;public Page<OrderStatVo> orderStatistical(OrderStatQuery query) { Pageable pageable = PageRequest.of(query.getPageNum() - 1, query.getPageSize());MongoTemplate mongoTemplate = mongoFactory.mongoTemplate(OrderConstants.ORDER_DB);// 时间筛选Long startTime = query.getStartTime();Long endTime = query.getEndTime();// 区分 1年,2月,3日int type = query.getType();// 按商家idString shopId = query.getShopId();// 按code筛选Integer areaCode = query.getAreaCode();Integer provinceCode = query.getProvinceCode();Integer cityCode = query.getCityCode();Integer countyCode = query.getCountyCode();// 基础匹配条件：按年初和年末 时间戳Criteria baseCriteria = new Criteria();// 额外的筛选条件List<Criteria> additionalCriteria = new ArrayList<>();if (startTime != null && endTime != null) {additionalCriteria.add(Criteria.where("hzdd_order_addtime").gte(startTime).lt(endTime));}if (StringUtil.isNotEmpty(shopId)) {additionalCriteria.add(Criteria.where("hzdd_order_sjid").is(shopId));}if (areaCode != null && areaCode != 0) {additionalCriteria.add(Criteria.where("hzdd_order_area_code").is(areaCode));}if (provinceCode != null && provinceCode != 0) {additionalCriteria.add(Criteria.where("hzdd_order_province_code").is(provinceCode));}if (cityCode != null && cityCode != 0) {additionalCriteria.add(Criteria.where("hzdd_order_city_code").is(cityCode));}if (countyCode != null && countyCode != 0) {additionalCriteria.add(Criteria.where("hzdd_order_county_code").is(countyCode));}// 合并所有条件if (!additionalCriteria.isEmpty()) {baseCriteria.andOperator(additionalCriteria.toArray(new Criteria[0]));}// 构建匹配操作MatchOperation matchOperation = Aggregation.match(baseCriteria);// 添加字段操作，将 Unix 时间戳转换为日期字符串String expression = switch (type) {case 1 -> "{$dateToString: { format: '%Y', date: { $toDate: '$hzdd_order_addtime' }}}";case 2 -> "{$dateToString: { format: '%Y-%m', date: { $toDate: '$hzdd_order_addtime' }}}";case 3 -> "{$dateToString: { format: '%Y-%m-%d', date: { $toDate: '$hzdd_order_addtime' }}}";default -> "{$dateToString: { format: '%Y-%m-%d', date: { $toDate: '$hzdd_order_addtime' }}}";};AddFieldsOperation addFieldsOperation = Aggregation.addFields().addField("orderDate").withValueOfExpression(expression).build();// 分组操作GroupOperation groupOperation = Aggregation.group("orderDate").sum(ConditionalOperators.Cond.when(Criteria.where("hzdd_order_ispay").is(1)).then("$hzdd_order_amount").otherwise(0)).as("paidAmount").sum(ConditionalOperators.Cond.when(Criteria.where("hzdd_order_ispay").is(1)).then(1).otherwise(0)).as("paidCount").sum(ConditionalOperators.Cond.when(Criteria.where("hzdd_order_ispay").is(0)).then("$hzdd_order_amount").otherwise(0)).as("unpaidAmount").sum(ConditionalOperators.Cond.when(Criteria.where("hzdd_order_ispay").is(0)).then(1).otherwise(0)).as("unpaidCount");// 投影操作ProjectionOperation projectionOperation = Aggregation.project().and("_id").as("date").andInclude("paidAmount", "paidCount", "unpaidAmount", "unpaidCount");// 排序操作SortOperation sortOperation = Aggregation.sort(Sort.Direction.ASC, "date");// 分页操作SkipOperation skipOperation = Aggregation.skip((long) pageable.getPageNumber() * pageable.getPageSize());LimitOperation limitOperation = Aggregation.limit(pageable.getPageSize());// 构建不包含分页的聚合查询以获取总条数Aggregation countAggregation = Aggregation.newAggregation(matchOperation,addFieldsOperation,groupOperation,Aggregation.group("orderDate").count().as("totalCount"), // 添加计数操作Aggregation.project("totalCount").andExclude("_id") // 只包含 totalCount 字段);// 执行聚合查询以获取总条数AggregationResults<Document> totalCountResults = mongoTemplate.aggregate(countAggregation, "hbdd_order", Document.class);Document document = totalCountResults.getMappedResults().stream().findFirst().orElse(null);int total = document != null ? (int) document.get("totalCount") : 0;// 构建包含分页的聚合查询Aggregation aggregation = Aggregation.newAggregation(matchOperation,addFieldsOperation,groupOperation,projectionOperation,sortOperation,skipOperation,limitOperation);// 第二个参数是文档名(表名)，第三个参数是接收的类，字段对应上面代码中的as别名字段AggregationResults<OrderStatVo> results = mongoTemplate.aggregate(aggregation, "hbdd_order", OrderStatVo.class);List<OrderStatVo> everyDayOrderStats = results.getMappedResults();// 分页操作return new PageImpl<>(everyDayOrderStats, pageable, total);}

** OrderStatQuery 类就不展示了，就是传值进来的筛选条件 **

OrderStatVo类

import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;@Data
@Schema(description = "订单统计")
public class OrderStatVo {@Schema(description = "周期")private String date;@Schema(description = "已支付金额")private Double paidAmount;@Schema(description = "已支付订单数")private Long paidCount;@Schema(description = "未支付金额")private Double unpaidAmount;@Schema(description = "未支付订单数")private Long unpaidCount;}

Java中使用mongoDB小技巧：

配置文件中加上下面这行，可以打印出mongo的SQL语句

logging:level:org.springframework.data.mongodb.core.MongoTemplate: DEBUG