java mongodb分组查询及与mysql语法比较

最新推荐文章于 2024-05-10 16:55:03 发布

原创最新推荐文章于 2024-05-10 16:55:03 发布 · 1.1k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mongodb #mysql #java

java 同时被 2 个专栏收录

15 篇文章

订阅专栏

mongo

1 篇文章

订阅专栏

本文详细介绍了如何使用MongoDB的聚合框架进行高级查询，包括如何计算每个机构的用户总数及最大年龄，并通过过滤条件筛选出最大年龄不低于40岁的机构，最后按机构用户数降序排列并限制返回前10条记录。同时提供了Java代码实现，便于读者理解和实践。

举例：查询表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/