mongo存储时为了查看方便将数据存在了数组内
查看某一storeId下的登陆信息没问题,但是当需要将loginDate作为条件筛选时,只要某个storeId下有一条满足记录,那么该storeId下所有记录都会好被查询出来,无奈只能通过聚合查询的方式将storeId下的数据拆分开来如下:
之后在对loginDate进行筛选,例如查询2018-11-13的记录
脚本: db.getCollection('base_user_login_record').aggregate([{"$unwind":"$recordList"},{"$match":{"recordList.loginDate":{"$gte": ISODate("2018-11-13T00:00:00.000Z"),"$lte": ISODate("2018-11-13T23:59:59.000Z")}}}])
注:mongoDb中存储的时间都是标准时间,存储时都是先转换为UTC时间(0时区),然后存储到数据库中,因此可能会与本地时间不相符
对应到java中
SimpleDateFormat smpr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date startDate = smpr.parse("2018-11-13 00:00:00");
Date endDate = smpr.parse("2018-11-13 23:59:59");
MatchOperation match = Aggregation.match(
Criteria.where("recordList.loginSource").ne("zabbix_web_test").and("recordList.loginDate")
.gte(startDate).lte(endDate));
AggregationResults<BasicDBObject> queryResult = mongoTemplate
.aggregate(Aggregation.newAggregation(Aggregation.unwind("recordList"), match),
collection_name, BasicDBObject.class);
List<BasicDBObject> list = queryResult.getMappedResults();
mongo 分组查询:
原始数据 [ { "_id" : ObjectId("5cce854e0aa0ff785c8105c0"), "offerId" : NumberLong(98), "name" : "1", "nav" : false, "status": "enabled" }, { "_id" : ObjectId("5cce854e0aa0ff785c8105c1"), "offerId" : NumberLong(98), "name" : "2", "nav" : false, "status": "enabled" }, { "_id" : ObjectId("5cce854e0aa0ff785c8105c2"), "offerId" : NumberLong(98), "name" : "1", "nav" : true, "status": "enabled" }] 为了查询相同name且状态为enabled中最新的值的全部信息 db.getCollection('base_setting_activity').aggregate( [ { "$sort" : { "_id" : -1}} , { "$match" : { "status" : { "$eq" : "enabled"}}} , { "$group" : { "_id" : "$name" , "all" : { "$first" : "$$ROOT"}}}] ); 对应java SortOperation sort = Aggregation.sort(Direction.DESC, "_id"); MatchOperation match = Aggregation.match(Criteria.where("status").is("enabled")); GroupOperation group = Aggregation.group("name").first("$$ROOT").as("all"); //SkipOperation skipOperation = Aggregation.skip((page - 1) * rows); //LimitOperation limitOperation = Aggregation.limit(rows); AggregationResults<HashMap> queryResult = mongoTemplate .aggregate(Aggregation.newAggregation(sort, match, group), COLL_NAME_ACTIVITY, HashMap.class); List<HashMap> bs = queryResult.getMappedResults();