| [备忘]mongo聚合查询，多个group字段，_id别名alias，springboot的mongoTemplate例子 | ||
上一篇:[备忘]springboot不扫描我的TaskExecutor呢 |
||
| 添加日期：2020/5/21 21:42:07 | 快速返回 返回列表 | 阅读2280次 |
/** * 查询Mongo数据. * * @param startTime * 开始时间 * @param endTime * 结束时间 * @return */ public List<QueryAggregateResult> queryByJson(DateTime startTime, DateTime endTime) { ConditionalOperators.Switch.CaseOperator case1 = ConditionalOperators.Switch.CaseOperator .when(ComparisonOperators.Gt.valueOf("queryCostTime").greaterThanValue(5000)).then(1); ConditionalOperators.Switch w = ConditionalOperators.switchCases(case1).defaultTo("0"); // 聚合条件，相当于拼一个groupBy的sql语句 Aggregation agg = Aggregation.newAggregation( Aggregation.match(Criteria.where("queryTime").exists(true).andOperator( Criteria.where("queryTime").gte(startTime.toDate()), Criteria.where("queryTime").lte(endTime.toDate()))), // where条件 Aggregation.group("cid","ipcc").count().as("totalCount").sum(w).as("queryGT5").sum("useCache") .as("cacheCount").sum("hasData").as("hasDataCount"), // group // by Aggregation.project("totalCount", "queryGT5", "cacheCount", "hasDataCount") .and("$_id.cid").as("cid").and("$_id.ipcc").as("ipcc").andExclude("_id"), // select字段 Aggregation.sort(Sort.Direction.DESC, "totalCount")); // 排序 // 执行查询，并把结果转成bean MongoTemplate mongoTemplate = SpringContextHolder.getBean("mongoTemplate"); AggregationResults<QueryAggregateResult> result = mongoTemplate.aggregate(agg, "gdsQueryLogInfoNew", QueryAggregateResult.class); return result.getMappedResults(); } 难点：.and("$_id.cid").as("cid")这句，找了半天才找到。 注意，最后project时把_id排除掉了，否则会报错： org.springframework.data.mapping.MappingException: Expected to read Document Document{{cid=xxx, ipcc=yyy}} into type class java.lang.Integer but didn't find a PersistentEntity for the latter! 莫名其妙的~~估计死心眼非要映射到bean里吧。 实现的聚合大概如下： ---------------------- [ { "$match": { "queryTime": { "$exists": true }, "$and": [ { "queryTime": { "$gte": { "$date": "2020-05-21T13:19:00Z" } } }, { "queryTime": { "$lte": { "$date": "2020-05-21T13:20:00Z" } } } ] } }, { "$group": { "_id": { "cid": "$cid", "ipcc": "$ipcc" }, "totalCount": { "$sum": 1 }, "queryGT5": { "$sum": { "$switch": { "branches": [ { "case": { "$gt": [ "$queryCostTime", 5000 ] }, "then": 1 } ], "default": "0" } } }, "cacheCount": { "$sum": "$useCache" }, "hasDataCount": { "$sum": "$hasData" } } }, { "$project": { "totalCount": 1, "queryGT5": 1, "cacheCount": 1, "hasDataCount": 1, "cid": "$_id.cid", "ipcc": "$_id.ipcc" } }, { "$sort": { "totalCount": -1 } } ] ---------------------- 竟然有个switch，我靠， mongoTemplate还给实现了，估计程序员也憋头大了。 |
||
| 评论 COMMENTS | |
| 没有评论 No Comments. |
