Java – calculated group by field in mongodb

For this example in the mongodb document, how to write a query using mongotemplate?

db.sales.aggregate(
   [
      {
        $group : {
           _id : { month: { $month: "$date" },day: { $dayOfMonth: "$date" },year: { $year: "$date" } },totalPrice: { $sum: { $multiply: [ "$price","$quantity" ] } },averageQuantity: { $avg: "$quantity" },count: { $sum: 1 }
        }
      }
   ]
)

Or, in general, how do I group by calculated fields?

Solution

You can use "project" to do such things first, but for me, it is a bit counter intuitive and needs a $project stage:

Aggregation agg = newAggregation(
        project("quantity")
            .andExpression("dayOfMonth(date)").as("day")
            .andExpression("month(date)").as("month")
            .andExpression("year(date)").as("year")
            .andExpression("price * quantity").as("totalAmount"),group(fields().and("day").and("month").and("year"))
            .avg("quantity").as("averavgeQuantity")
            .sum("totalAmount").as("totalAmount")
            .count().as("count")
    );

As I said, counter intuitively, you should be able to announce all this at the $group stage, but the helper doesn't seem to work that way Serialization is a bit interesting (wrapping date operator parameters in arrays), but it seems to work However, these are two pipeline stages, not one

What's the problem? By phasing, the "project" section forces all documents in the pipeline to obtain the calculated fields, which means that it passes through all stages before moving to the group stage

By running the query in two forms, you can clearly see the difference in processing time In a separate project phase, my hardware takes three times longer to execute a query that calculates all fields during a group operation

So now it seems that the only correct way is to create the pipeline object by yourself:

ApplicationContext ctx =
            new AnnotationConfigApplicationContext(SpringMongoConfig.class);
    MongoOperations mongoOperation = (MongoOperations) ctx.getBean("mongoTemplate");

    BasicDBList pipeline = new BasicDBList();
    String[] multiplier = { "$price","$quantity" };

    pipeline.add(
        new BasicDBObject("$group",new BasicDBObject("_id",new BasicDBObject("month",new BasicDBObject("$month","$date"))
                    .append("day",new BasicDBObject("$dayOfMonth","$date"))
                    .append("year",new BasicDBObject("$year","$date"))
            )
            .append("totalPrice",new BasicDBObject(
                "$sum",new BasicDBObject(
                    "$multiply",multiplier
                )
            ))
            .append("averageQuantity",new BasicDBObject("$avg","$quantity"))
            .append("count",new BasicDBObject("$sum",1))
        )
    );

    BasicDBObject aggregation = new BasicDBObject("aggregate","collection")
        .append("pipeline",pipeline);

    System.out.println(aggregation);

    CommandResult commandResult = mongoOperation.executeCommand(aggregation);

Or if all this seems simplified, you can use the JSON source and parse it at any time But of course, it must be a valid JSON:

String json = "[" +
        "{ \"$group\": { "+
            "\"_id\": { " +
                "\"month\": { \"$month\": \"$date\" }," +
                "\"day\": { \"$dayOfMonth\":\"$date\" }," +
                "\"year\": { \"$year\": \"$date\" } " +
            "}," +
            "\"totalPrice\": { \"$sum\": { \"$multiply\": [ \"$price\",\"$quantity\" ] } }," +
            "\"averageQuantity\": { \"$avg\": \"$quantity\" }," +
            "\"count\": { \"$sum\": 1 } " +
        "}}" +
    "]";

    BasicDBList pipeline = (BasicDBList)com.mongodb.util.JSON.parse(json);