举例:查询表user_info的所有机构id, 机构对应人数,用户的最大年龄
mysql语法:
SELECT orgId, count(1) as orgId_total, max(age) as age_max
FROM user_info
GROUP BY orgId HAVING age_max>=40
ORDER BY orgId_total DESC
LIMIT 10
mongodb语法:--shell命令
db.user_info.aggregate([
{$group: { _id: "$orgId", orgId_total: { $sum: 1 }, age_max:{$max:"$age"}} },
{$match:{"age_max":{$gte:40}}},
{$sort:{"orgId_total":-1}},
{$limit: 10}
])
附 java代码 操作:
//mongodb操作数据库对象
private MongoDatabase mongoDatabase;
public MongoDBHepler(String connectionUrl){
try {
MongoClientURI mongoClientURI = new MongoClientURI(connectionUrl);
MongoClient mongoClient = new MongoClient(mongoClientURI);
this.mongoDatabase = mongoClient.getDatabase(dbName);//连接到数据库
logger.debug("连接成功");
} catch (Exception e) {
throw new RuntimeException("fail to init mongodb !");
}
}
public List<Map<String, Object>> group(){
MongoCollection<Document> collection = this.mongoDatabase.getCollection("user_info");
List<BasicDBObject> aggreList = new ArrayList<>();
// {$group: { _id: "$orgId", orgId_total: { $sum: 1 }, age_max:{$max:"$age"}} }
DBObject groupFields = new BasicDBObject("_id", "$orgId");
groupFields.put("orgId_total", new BasicDBObject("$sum", 1));
groupFields.put("age_max", new BasicDBObject("$max", "$age"));
BasicDBObject group = new BasicDBObject("$group", groupFields);
aggreList.add(group);
//{$match:{"age_max":{$gte:40}}}
BasicDBObject match_filter = new BasicDBObject("age_max", new BasicDBObject("$gte", 40));
BasicDBObject match = new BasicDBObject("$match", match_filter);
aggreList.add(match);
//{$sort:{"orgId_total":-1}}
BasicDBObject sort_filter = new BasicDBObject("orgId_total", -1);;
BasicDBObject sort = new BasicDBObject("$sort", sort_filter);
aggreList.add(sort);
//{$limit: 10}
BasicDBObject limit = new BasicDBObject("$limit", 10);
aggreList.add(limit);
List<Map<String, Object>> reslutList = new ArrayList<>();
AggregateIterable<Document> aggregateIterable = collection.aggregate(aggreList);
for (Document doc : aggregateIterable) {
String orgId = doc.getString("_id");
Integer orgId_total = doc.getInteger("orgId_total");
String age_max = doc.getString("age_max");
Map<String, Object> row = new HashMap<>();
row.put("orgId", orgId);
row.put("orgId_total", orgId_total);
row.put("age_max", age_max);
logger.info("groupCount row={} ", JSON.toJSONString(row));
reslutList.add(row);
}
return reslutList;
}
### 执行打印结果:###
--- groupCount row={"orgId_total":831,"orgId":"BJ004","age_max":85}
--- groupCount row={"orgId_total":622,"orgId":"H001008","age_max":89}
--- groupCount row={"orgId_total":515,"orgId":"H001003","age_max":96}
--- groupCount row={"orgId_total":479,"orgId":"H06121129","age_max":92}
--- groupCount row={"orgId_total":450,"orgId":"H01101001","age_max":90}
其它文章参考:
mongodb高级聚合查询 - http://www.cnblogs.com/zhoujie/p/mongo1.html
mongodb操作文档-https://docs.mongodb.com/manual/reference/collation/