如何使用java获得mongoDB每月汇总数据

Question

我的数据以db为单位。

"Accounts" : [ 
        {
            "Total_Credits" : 4000,
            "Total_Debits" : 0,
            "Date" : "25-05-2015"
        }, 
        {
            "Total_Credits" : 1000,
            "Total_Debits" : 0,
            "Date" : "26-05-2015"
        }, 
        {
            "Total_Credits" : 1000,
            "Total_Debits" : 0,
            "Date" : "10-07-2015"
        }]

我想提取每月的贷方和借方总额。我想用java写

Answer 1

使用聚合框架与以下聚合管道(Mongo shell实现):

db.ledger.aggregate([
    {
        "$unwind": "$Accounts"
    },
    {
        "$project": {
            "Total_Credits" : "$Accounts.Total_Credits",
            "Total_Debits" : "$Accounts.Total_Debits",
            "month_year" : {  "$substr": [ "$Accounts.Date", 3, -1 ] }
        }
    },
    {
        "$group": {
            "_id": "$month_year",
            "Total_Credits": { "$sum": "$Total_Credits" },
            "Total_Debits": { "$sum": "$Total_Debits" }
        }
    }
])

在上面的例子中，它输出到控制台:

/* 0 */
{
    "result" : [ 
        {
            "_id" : "07-2015",
            "Total_Credits" : 1000,
            "Total_Debits" : 0
        }, 
        {
            "_id" : "05-2015",
            "Total_Credits" : 5000,
            "Total_Debits" : 0
        }
    ],
    "ok" : 1
}

在Java中，可以这样实现:

import com.mongodb.AggregationOutput;
import com.mongodb.BasicDBObject;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBObject;
public class Aggregation {
    public static void main(String[] args) {
        DB db = MongoDb.getAccountsDb();
        DBCollection ledger = db.getCollection("ledger");
        //------------------------------------------------- aggregation framework
        DBObject unwind = new BasicDBObject("$unwind", "$Accounts");
        List<Object> substrList = Arrays.asList(new Object[]{"$Accounts.Date", 3, -1});
        DBObject monthProjection = new BasicDBObject("$substr", substrList);
        DBObject projectFields = new BasicDBObject("Total_Credits", "$Accounts.Total_Credits");
        projectFields.put("Total_Debits", "$Accounts.Total_Debits");
        projectFields.put("month_year", monthProjection);
        DBObject project = new BasicDBObject("$project", projectFields );
        DBObject groupFields = new BasicDBObject( "_id", "$month_year");
        groupFields.put("Total_Credits", new BasicDBObject( "$sum", "$Total_Credits"));
        groupFields.put("Total_Debits", new BasicDBObject( "$sum", "$Total_Debits"));
        DBObject group = new BasicDBObject("$group", groupFields);
        AggregationOutput output = ledger.aggregate( unwind, project, group );
        System.out.println("n" + output);
    }
}