how to convert this mongodb aggregation command to java code

Question

how can i convert this mongodb aggregation command to java:
to give you a global vision of what i want; i will give more details; i have a collection called "Finance" and within this collection there are many documents that have different types; i want only those with type expenses:{type:"expenses"} so documents with expenses type have a field called bought_date; and it is in milliseconds, and also those documents have field called price. i want the total of price within each month! so i need to convert those milliseconds to date to be able to regroup it using $month!

db.Finance.aggregate([ { $match : { type : "Expenses" } } ,{
    "$project": {
        "bought_date": {
            "$add": [ new Date(0), "$bought_date" ]
        },
        "msisdn": 1
     } 
},

{ 
    "$group" : { 
        "_id" : {
            "bought_date" : "$bought_date",
            "msisdn":"$msisdn"
        }, 
        "msisdnCount" : { "$sum" : 1}
    }
}

]);

i tried this:

JsonObject first_match=new JsonObject()
    .put("type", "Expenses");

JsonObject project=new JsonObject()
    .put("bought_date", new JsonObject()
    .put("$add",new JsonArray()
    .add(new Date(0))
    .add("$bought_date")))
    .put("msisdn", 1);

JsonObject project_sec=new JsonObject()
    .put("month",new JsonObject().put("$month","$bought_date"))
    .put("msisdn", 1) 
    .put("bought_date", 1);

JsonObject group=new JsonObject()
    .put("_id","$month")
    .put("price",new JsonObject()
    .put("$sum","price"));

JsonArray pipeline=new JsonArray()
    .add(new JsonObject().put("$match", first_match))
    .add(new JsonObject().put("$project", project))
    .add(new JsonObject().put("$project", project_sec))
    .add(new JsonObject().put("$group", group));

JsonObject command = new JsonObject()
    .put("collection", "Finance")
    .put("pipeline",pipeline)
    .put("explain",false);

//using RunCommand

client.runCommand("aggregate",command, results -> { ...

but it looks like i can't add a date into JsonArray? : Illegal type in JsonObject: class java.util.Date

Solution in case someone was facing the same problem: my problem was with new Date(0) which wasn't a type that can be added to JsonObject, so i instead used this: new JsonObject().put("$date", "1970-01-01T00:00:00Z")

Pratik · Accepted Answer · 2018-07-31 05:36:54Z

1

You can use only one date in $add aggregate function. for adding new date ( ex. today date ) , find the timestamp of today date ( new Date().getTime() ) . and add in the list .

   BasicDBObject basicDBObject=new BasicDBObject();
    Date date1=new Date();
    List list=new ArrayList();
    list.add("$date_achat");
    list.add(date1.getTime());
    Document project = new Document("$project", new Document("date_achat",new Document("$add",list)).append("msisdn", 1));
    List<Document> agglist = new ArrayList<Document>();
    agglist.add(project);

------------------------solution in java---------------------------------

Here is the java Code :

        List list = new ArrayList();
        list.add(new Date(0));
        list.add("$bought_date");
        Document match = new Document("$match", new Document("type", "Expenses"));
        Document project = new Document("$project", new Document("bought_date", 1).append("msisdn", 1).append("date_formatted", new Document("$add", list)));
        Document group = new Document("$group", new Document("_id", new Document("$month", "$date_formatted")).append("msisdncount", new Document("$sum", "$msisdn")));

        List<Document> agglist = new ArrayList<Document>();
        agglist.add(match);
        agglist.add(project);
        agglist.add(group);

edited Jul 31, 2018 at 5:36

answered Jul 30, 2018 at 6:32

Pratik

672 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

knowledgeSeeker Over a year ago

to give you a global vision of what i want; i will give more details; i have a collection called "Finance" and within this collection there are many documents that have different types; i want only those with type expenses:{type:"expenses"} so documents with expenses type have a field called bought_date; and it is in milliseconds, and also have those documents have field called price. i want the total of price within each month! so i need to convert those milliseconds to date to be able to regroup it using $month!

Pratik Over a year ago

basically, you want to figure out the total price {$match:{"type":"expences"}} by aggregating the $bought_date using group by for a month. please correct me if I am wrong.

Pratik Over a year ago

You first add a field in document as month by doing {$month:"$bought_date"} and then grouping by $month . I would share the solution in java

Pratik Over a year ago

Updated the solution . Welcome :)

knowledgeSeeker Over a year ago

thank you really really a looot! it doesn't work for me since my problem was with new Date(0) wasn't a type that can be added to JsonObject, so i instead used this new JsonObject().put("$date", "1970-01-01T00:00:00Z")

|

Collectives™ on Stack Overflow

how to convert this mongodb aggregation command to java code

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related