java mongo 日期范围_mongo复杂数据增删查改

高级查询

多级内嵌数组查询：

二级以上内嵌数组查询

如图，现在只想返回所有公式parseEnd为10的数据

由于mongo无法直接进行二次内嵌数组的过滤

所以我们可以采取层级查询：

完整代码如下：

db.device_management.aggregate([

{

$project: {

formulas: {

$filter: {

input: "$sensors.formulas",

as: "sensors_c",

cond: { }

}

}

}

},{ $unwind : "$formulas" },

{

$project: {

formulas: {

$filter: {

input: "$formulas",

as: "formulas_cp",

cond: { $eq: [ "$$formulas_cp.parseEnd", "10" ]}

}

}

}

}

])

返回数据格式如下：

根据条件查询并返回指定数组或数组之上的字段

数据库如下：

查询语句如下：

db.data_collect.aggregate([

{

$match: {

'imei':'1',

"addressOrKey":"1",

}

},

{

$project: {

resolverValues: {

$filter: {

input: "$resolverValues",

as: "resolverValues_c",

cond: {

$eq: [ "$$resolverValues_c.name", "humidity" ]

}

}

},

"collectTime":"$collectTime"

}

},{ $unwind : "$resolverValues" },

])

返回结果：

MongoDB基于时间段的聚合查询

查询语句如下（查询时会将按日期将时间进行分组，分组后的数据在做平均获取取最大最小值处理）

db.getCollection('data_temp').aggregate([

{"$match":{

"collectTime": {'$gte': ISODate("2019-07-25T15:03:37.078Z") ,'$lt':ISODate("2019-07-26T05:43:07.079Z")}

}},

{"$group": {

"_id": {

"$subtract": [

{ "$subtract": [ "$collectTime", new Date("1970-01-01") ] },

{ "$mod": [

{ "$subtract": [ "$collectTime", new Date("1970-01-01") ] },

1000 * 60 * 30 /*聚合时间段，30分钟*/

]}

]

},

"value": {'$avg': '$value'},

"collectTime":{"$max":'$collectTime'}

}},

{"$project": {

"_id": 0,

"value": 1,

"collectTime":1

}},

{"$sort": {

'collectTime': 1

}}

])

查询结果如下：

Spring boot 代码：

spring boot 代码：

Aggregation agg = Aggregation.newAggregation( Aggregation.match(Criteria.where("collectTime").gte(startDate).lt(endDate)), Aggregation.project("collectTime", "value").andExpression("ceil((collectTime - [0]) / [1])", new Date(0), interval).as("group_date"), Aggregation.group("group_date").avg("value").as("value").max("collectTime").as("collectTime"), Aggregation.sort(Sort.Direction.ASC,"collectTime"));List<Temp> list=mongoTemplate.aggregate(agg,"data_temp",Temp.class).getMappedResults();return list;

参考文档：

https://docs.mongodb.com/manual/reference/operator/aggregation/match/

MongoDB基于时间段的聚合查询（内嵌数组符合条件查询）

参考数据库：

Js代码：

db.getCollection('data_collect').aggregate([

{"$match":{

'imei':'1',

"collectTime": {'$gte': ISODate("2019-07-25T15:03:37.078Z") ,'$lt':ISODate("2020-07-26T05:43:07.079Z")},

"addressOrKey":"1"

}},

{

"$project": {

"resolverValues": {

"$filter": {

"input": "$resolverValues",

"as": "resolverValues_c",

"cond": {

"$eq": [ "$$resolverValues_c.name", "humidity" ]

}

}

},

"collectTime":"$collectTime"

}

},

{ "$unwind" : "$resolverValues" },

{

"$group": {

"_id": {

"$subtract": [

{ "$subtract": [ "$collectTime", new Date("1970-01-01") ] },

{ "$mod": [

{ "$subtract": [ "$collectTime", new Date("1970-01-01") ] },

1000 * 60

]}

]

},

"resolverValue": {'$first': '$resolverValues'},

"collectTime":{"$first":'$collectTime'}

}},

{

"$project": {

"collectTime":"$collectTime",

"status":"$resolverValue.status",

"value":"$resolverValue.value",

"unit" : "$resolverValue.unit",

"_id":0

}

},

{"$sort": {

'collectTime': 1

}}

])

结果：

查询嵌套文档并只返回嵌套对象：

Js代码：

db.device_management.aggregate([ { $match: { '_id':ObjectId("5ea79af3dd905336d0df1865"), } }, { $project: { bindSensorInstructs: { $filter: { input: "$bindSensorInstructs", as: "bindSensorInstruct", cond: { $and:[ {$eq: [ '$$bindSensorInstruct.sensorId', '5ead607bdd905332f0c1085b' ]}, {$eq: [ '$$bindSensorInstruct.addressOrKey', '12' ]}, ] } } } } }, { $unwind : "$bindSensorInstructs" }, { $project : { "addressOrKey": "$bindSensorInstructs.addressOrKey", "mid": "$bindSensorInstructs.mid", "sensorId": "$bindSensorInstructs.sensorId" } }])

Java代码

Aggregation agg = Aggregation.newAggregation( Aggregation.match(Criteria.where(DeviceManagement.DeviceManagementField.id.getValue()).is(id)), Aggregation.project().and(new AggregationExpression() { @Override public Document toDocument(AggregationOperationContext aggregationOperationContext) { Document filterExpression = new Document(); filterExpression.put("input", "$instructs"); filterExpression.put("as","instruct"); filterExpression.put("cond", new Document("$eq", Arrays.<Object> asList("$$instruct.instructId", instructId))); return new Document("$filter", filterExpression); } }).as(DeviceManagement.DeviceManagementField.instructs.getValue()), Aggregation.unwind(DeviceManagement.DeviceManagementField.instructs.getValue()), Aggregation.project() .and("$instructs.instructId").as("instructId") .and("$instructs.mid").as("mid") .and("$instructs.status").as("status"));Instruct instruct=mongoTemplate.aggregate(agg, MongoCollectionNameEnum.DEVICE_MANAGEMENT.getValue(), Instruct.class).getUniqueMappedResult();

子文档聚合分页查询

Js代码

先查询子文档符合条件总数

db.device_management.aggregate([ { $match: { '_id':ObjectId("5ea79af3dd905336d0df1865"), } }, { $unwind : "$bindSensorInstructs" }, { $project : { "addressOrKey": "$bindSensorInstructs.addressOrKey", "mid": "$bindSensorInstructs.mid", "sensorId": "$bindSensorInstructs.sensorId" } }, {$count: "count"}])

再进行分页查询

db.device_management.aggregate([ { $match: { '_id':ObjectId("5ea79af3dd905336d0df1865"), } }, { $unwind : "$bindSensorInstructs" }, { $project : { "addressOrKey": "$bindSensorInstructs.addressOrKey", "mid": "$bindSensorInstructs.mid", "sensorId": "$bindSensorInstructs.sensorId" } }, {$skip:0},{$limit:2}])

子文档数组去重模糊匹配查询

需求：对字段为所属用户的数组进行去重并模糊匹配返回数据

Js代码：

db.device_management.aggregate([{ $unwind: "$ownerUsers" }, { $group: { _id: "$ownerUsers", "userName": { '$first': '$ownerUsers' } } }, { $match: { '_id': { $regex: "小" } } }, { "$project": { "_id": 0, "userName": 1 } }])

Java代码：

public List<UserNameModel> likeUserNameList(String userName) { Aggregation agg = Aggregation.newAggregation( Aggregation.unwind(DeviceManagement.DeviceManagementField.ownerUsers.getValue()), Aggregation.group("ownerUsers") .first("ownerUsers").as(UserNameModel.UserNameModelField.userName.getValue()), Aggregation.match(Criteria.where(UserNameModel.UserNameModelField.userName.getValue()).regex(userName)), Aggregation.project() .and(UserNameModel.UserNameModelField.userName.getValue()).as(UserNameModel.UserNameModelField.userName.getValue()), Aggregation.limit(10) ); List<UserNameModel>userNameModels=mongoTemplate.aggregate(agg, MongoCollectionNameEnum.DEVICE_MANAGEMENT.getValue(), UserNameModel.class).getMappedResults(); return userNameModels;}

查询分组结果放到数组中并且进行分页

Js查询代码

db.sensor.aggregate([{ $unwind: "$ownerUsers" }, { $match: { 'ownerUsers': { $regex: "小" }, 'name':{ $regex: "传感" } } }, { $group: { _id: "$ownerUsers", "userName":{ '$first': '$ownerUsers'}, "names": { $push: "$name" } } }, { "$project": { "_id": 0, "userName":1, "names": 1 } }])

Java代码

Aggregation agg = Aggregation.newAggregation( Aggregation.unwind(Sensor.SensorField.ownerUsers.getValue()), Aggregation.match(Criteria.where(Sensor.SensorField.name.getValue()).regex(Pattern.compile(name)) .andOperator(Criteria.where(Sensor.SensorField.ownerUsers.getValue()).regex(Pattern.compile(userName)))), Aggregation.group(Sensor.SensorField.ownerUsers.getValue()), Aggregation.count().as("count") ); Document document=mongoTemplate.aggregate(agg, MongoCollectionNameEnum.SENSOR.getValue(), Document.class).getUniqueMappedResult(); final PageResult<AuthUserBingSensorModel> pageResult = new PageResult<>(); if(document==null){ pageResult.setTotal(0L); pageResult.setPages(0); pageResult.setPageSize(row); pageResult.setPageNum(pageNo); pageResult.setList(new ArrayList<AuthUserBingSensorModel>()); return pageResult; } long count=document.getInteger("count"); final Integer pages = (int) Math.ceil(count / (double) row); pageResult.setTotal(count); pageResult.setPages(pages); pageResult.setPageSize(row); pageResult.setPageNum(pageNo); int skip = row * (pageNo - 1); Aggregation aggc = Aggregation.newAggregation( Aggregation.unwind(Sensor.SensorField.ownerUsers.getValue()), Aggregation.match(Criteria.where(Sensor.SensorField.name.getValue()).regex(Pattern.compile(name)) .andOperator(Criteria.where(Sensor.SensorField.ownerUsers.getValue()).regex(Pattern.compile(userName)))), Aggregation.group(Sensor.SensorField.ownerUsers.getValue()) .first("ownerUsers").as("userName") .push( new AggregationExpression() { @Override public Document toDocument(AggregationOperationContext aggregationOperationContext) { Document obj = new Document(); obj.put("name","$name"); obj.put("_id","$_id"); return obj; } } ).as("bingSensors"), Aggregation.project() .and("userName").as("userName") .and("bingSensors").as("bingSensors"), Aggregation.skip(skip), Aggregation.limit(row) ); List<AuthUserBingSensorModel>list=mongoTemplate.aggregate(aggc, MongoCollectionNameEnum.SENSOR.getValue(), AuthUserBingSensorModel.class).getMappedResults(); pageResult.setList(list); return pageResult;

更新操作

更新嵌套数组（可更新n层）

Js代码

db.getCollection('device_management').update( // query { '_id':ObjectId("5e9fb2f4dd905353bcb81fdb"), }, // update { '$set': { "instructs.$[elem1].status": NumberInt(3) } }, { multi: true, arrayFilters:[ {"elem1.instructId":{$eq:"123456"}}, ] });

Java代码（可以更新单个值）

UpdateOptions updateOptions=new UpdateOptions(); List<Bson> arrayFilters=new ArrayList<>(); arrayFilters.add(new Document("elem1.instructId",new Document("$eq",instructId))); updateOptions.arrayFilters(arrayFilters); Document document=new Document(); document.put("instructs.$[elem1].status",status);// document.put("sensors.$[elem1].formulas.$[elem2].parseEnd",formula.getParseEnd()); document=new Document("$set",document); UpdateResult updateResult=mongoTemplate.getCollection(MongoCollectionNameEnum.DEVICE_MANAGEMENT.getValue()).updateMany(new Document(DeviceManagement.DeviceManagementField.id.getValue(),new ObjectId(id)),document,updateOptions); return updateResult.getModifiedCount()>0;