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

深碍√TFBOYSˉ_ 2022-03-18 10:14 369阅读 0赞

举例：查询表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][http_www.cnblogs.com_zhoujie_p_mongo1.html]

mongodb操作文档－[https://docs.mongodb.com/manual/reference/collation/][https_docs.mongodb.com_manual_reference_collation]

[http_www.cnblogs.com_zhoujie_p_mongo1.html]: http://www.cnblogs.com/zhoujie/p/mongo1.html
[https_docs.mongodb.com_manual_reference_collation]: https://docs.mongodb.com/manual/reference/collation/