高级查询
多级内嵌数组查询:
二级以上内嵌数组查询
如图,现在只想返回所有公式parseEnd为10的数据
![1c6b6a702e060a45c686a030950a0aa4.png](https://i-blog.csdnimg.cn/blog_migrate/42d9d774b63d35d3c7ae3c306890d582.png)
由于mongo无法直接进行二次内嵌数组的过滤
所以我们可以采取层级查询:
![4923dad7f2bd1c1bc2f343f4d32c3dd5.png](https://i-blog.csdnimg.cn/blog_migrate/57db83d941a31c9fb2bf82af6775d349.png)
![32891ae875af495efc5abf99c1ed33e4.png](https://i-blog.csdnimg.cn/blog_migrate/c181d4f459c0349fce23d990dff35310.png)
完整代码如下:
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" ]}
}
}
}
}
])
返回数据格式如下:
![024c4ea33f29340e6ed62999de343db6.png](https://i-blog.csdnimg.cn/blog_migrate/71a9c4e0f55b0d58caf190d2619433bd.png)
根据条件查询并返回指定数组或数组之上的字段
数据库如下:
![031fad098b6937853256b925293ea7a9.png](https://i-blog.csdnimg.cn/blog_migrate/6d9d08de8dd0c390e88d85b8ae7d7372.png)
查询语句如下:
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" },
])
返回结果:
![ee9218f566085513ea8bab170a9a6409.png](https://i-blog.csdnimg.cn/blog_migrate/f3b81698b91a8e7a7ee748e81c779671.png)
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
}}
])
查询结果如下:
![27381a2bff07c517f92106b5650bbd32.png](https://i-blog.csdnimg.cn/blog_migrate/3caf1a61a75e462cbb85e14ec109c9d1.png)
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基于时间段的聚合查询(内嵌数组符合条件查询)
参考数据库:
![402a302c5f866257ba7d0fc6a1dd586d.png](https://i-blog.csdnimg.cn/blog_migrate/e4b330164bb8ef2601c212461360a46b.png)
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
}}
])
结果:
![be1901dd02cd75dcae65fd7b13e82951.png](https://i-blog.csdnimg.cn/blog_migrate/08c899d8f183a5d2a614ecdec2719a48.png)
查询嵌套文档并只返回嵌套对象:
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}]) |
子文档数组去重模糊匹配查询
需求:对字段为所属用户的数组进行去重并模糊匹配返回数据
![453e2ca38b2ff233a445f9fc908c433a.png](https://i-blog.csdnimg.cn/blog_migrate/4eb2ee730cd2bfc9a9ffeb41cf5fa1bc.png)
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; |