[心缘地方]同学录
首页 | 功能说明 | 站长通知 | 最近更新 | 编码查看转换 | 代码下载 | 常见问题及讨论 | 《深入解析ASP核心技术》 | 王小鸭自动发工资条VBA版
登录系统:用户名: 密码: 如果要讨论问题,请先注册。

[备忘]mongo聚合查询,多个group字段,_id别名alias,springboot的mongoTemplate例子

上一篇:[备忘]springboot不扫描我的TaskExecutor呢
下一篇:[备忘]红米K30 打开usb调试备忘。

添加日期:2020-5-21 21:42:07 快速返回   返回列表 阅读397次
费了牛劲才弄成功:


/**
 * 查询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.

添加评论 Add new comment.
昵称 Name:
评论内容 Comment:
验证码(不区分大小写)
Validation Code:
(not case sensitive)
看不清?点这里换一张!(Change it here!)
 
评论由管理员查看后才能显示。the comment will be showed after it is checked by admin.
CopyRight © 心缘地方 2005-2999. All Rights Reserved