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; |