Aggregation聚合查询 金山竹影几千秋，云索高飞水自流，万里长江飘玉带，一轮银月滚金球，远自湖北三千里，近到江南十六州，美景一时观不透，天缘有分画中游！

需求：在订单表中，根据buyerNick分组，统计每个buyerNick的电话、地址、支付总金额以及总商品数，返回结果是CustomerDetail。

/* * project:列出所有本次查询的字段，包括查询条件的字段和需要搜索的字段； * match:搜索条件criteria * unwind：某一个字段是集合，将该字段分解成数组 * group：分组的字段，以及聚合相关查询 *sum：求和(同sql查询) *count：数量(同sql查询) *as:别名(同sql查询) *addToSet：将符合的字段值添加到一个集合或数组中 * sort：排序 * skip&limit：分页查询 */ public List customerDetailList(Integer pageNum,String userId,String buyerNick,String itemId,List phones) throws Exception{ Criteria criteria = Criteria.where("userId").is(userId); Integer pageSize = 10; Integer startRows = (pageNum - 1) * pageSize; if(buyerNick != null && !"".equals(buyerNick)){ criteria.and("buyerNick").is(buyerNick); } if(phones != null && phones.size() > 0){ criteria.and("mobile").in(phoneList); } if(itemId != null && !"".equals(itemId)){ criteria.and("orders.numIid").is(itemId); } Aggregation customerAgg = Aggregation.newAggregation( Aggregation.project("buyerNick","payment","num","tid","userId","address","mobile","orders"), Aggregation.match(criteria), Aggregation.unwind("orders"), Aggregation.group("buyerNick").first("buyerNick").as("buyerNick").first("mobile").as("mobile"). first("address").as("address").sum("payment").as("totalPayment").sum("num").as("itemNum").count().as("orderNum"), Aggregation.sort(new Sort(new Sort.Order(Sort.Direction.DESC, "totalPayment"))), Aggregation.skip(startRows), Aggregation.limit(pageSize) ); List customerList = tradeRepository.findAggregateList(new Query(criteria), userId, customerAgg,CustomerDetail.class); return customerList; }

public List findAggregateList(Query query,String userNickName, Aggregation aggregation,Class clazz) { AggregationResults aggregate = this.mongoTemplate.aggregate(aggregation, collectionName, clazz); List customerDetails = aggregate.getMappedResults(); return customerDetails; }

Trade表：

public class TradeInfo{ private String tid; //订单id private Double payment; //支付金额 private String buyerNick; //买家昵称 private String address; //地址 private String mobile; //手机号 private Long num; //购买商品数量 private List orders; 子订单 }

CustomerDetail：

public class CustomerDetail{ private String buyerNick; //买家昵称 private Double totalPayment; //订单金额 private Integer orderNum; //订单数 private Integer itemNum; //商品数 private String address; //地址 }

聚合分组，先过滤字段为type=Constants.DataSetOpt.OPT_VIEW,通过dateSetId进行分组，并计算每组的个数，.first是需要显示的字段。

//浏览分组 private List flowGroup() { Criteria criteria = Criteria.where("type").is(Constants.DataSetOpt.OPT_VIEW); Aggregation agg = Aggregation.newAggregation( Aggregation.match(criteria), Aggregation.group("dataSetId") .first("dataSetId").as("dataSetId") .count() .as("dataSetIdCount") .first("dataSetType").as("dataSetType")); AggregationResults result = mongoTemplate.aggregate(agg,Access.class ,Document.class); return result.getMappedResults(); }

也可以使用TypedAggregation的方式进行聚合分组，如下：

@Test public void statTest(){ TypedAggregation agg = Aggregation.newAggregation(Statistics.class, Aggregation.group("month") .sum("totalVisit") .as("sumTotalVisit") .sum("totalDownload") .as("sumTotalDownload")); AggregationResults result = mongoTemplate.aggregate(agg, Document.class); //return result.getMappedResults(); result.getMappedResults().forEach(document -> System.out.println(document)); }

addToSet使用方法示例

addToSet：将符合的字段值添加到一个集合或数组中\

【使用mongoTemplate进行Aggregation聚合查询】聚合示例

private List groupCountryOfOrigin() { Aggregation agg = Aggregation.newAggregation( Aggregation.group("countryOfOrigin") .first("countryOfOrigin").as("countryOfOrigin") .addToSet("portEntry").as("portEntryList") .addToSet("province").as("provinceList") .addToSet("customName").as("customNameList") ); AggregationResults result = mongoTemplate.aggregate(agg,CertificationBatchInfo.class , Document.class); return result.getMappedResults(); }

聚合内如获取

List