Looking for Java code for Mongo aggregation query

Question

Can somebody guide me on the aggregation query in Java for the following Mongo query. I am trying to sum up the distance covered every day by the vehicle. There are some duplicate records (which I cannot eliminate) so I have to use group by to filter them out.

db.collection1.aggregate({ $match: { "vehicleId": "ABCDEFGH", $and: [{ "timestamp": { $gt: ISODate("2022-08-24T00:00:00.000+0000") } }, { "timestamp": { $lt: ISODate("2022-08-25T00:00:00.000+0000") } }, { "distanceMiles": { "$gt": 0 } }] } }, { $group: {"_id": {vehicleId: "$vehicleId", "distanceMiles" : "$distanceMiles" } } }, { $group: { _id: null, distance: { $sum: "$_id.distanceMiles" } } })

If possible can you also suggest some references? I am stuck at the last group by involving $_id part.

The Java code that I have except the last group by is:

Criteria criteria = new Criteria();
criteria.andOperator(Criteria.where("timestamp").gte(start).lte(end),
        Criteria.where("vehicleId").in(vehicleIdList));

Aggregation aggregation = Aggregation.newAggregation(Aggregation.match(criteria),
        Aggregation.sort(Direction.DESC, "timestamp"),
        Aggregation.project("distanceMiles", "vehicleId", "timestamp").and("timestamp")
                .dateAsFormattedString("%Y-%m-%d").as("yearMonthDay"),
        Aggregation.group("vehicleId", "yearMonthDay").first("vehicleId").as("vehicleId").
        first("timestamp").as("lastReported").sum("distanceMiles").as("distanceMiles"));

Note. there is a slight difference between the raw mongo query and the query in Java on the date param.

score 0 · Answer 1 · answered Oct 26 '22 at 15:14

Generally if you are looking for advice on how to directly convert an aggregation pipeline into Java code (not necessarily using the builders), check out this answer.

I'm not really clear on what component you're currently stuck on though. Is it just the direct translation between the aggregation pipeline and the Java code? Is the aggregation pipeline not giving correct results? You haven't mentioned some information such as driver version that would help us advise further if needed.

A few other general things come to mind that might be worth mentioning:

The sample .aggregate() snippet you provided does not have the square brackets ([ and ]) wrapping the pipeline which would be needed in the shell.
When referencing existing field names, you probably need to prefix them with $ in the Java code similar to how you do in the shell.
You should be able to access the values nested inside of the _id field after the first $group stage using dot notation (eg "$_id.distanceMiles") as you are in the sample aggregation.

Depending on which specific driver you are using, documentation such as this may be helpful with respect to working with the builders.

Looking for Java code for Mongo aggregation query

1 Answers1