MongoDB中的按组计算的字段

For this example from the MongoDB documentation, how do I write the query using MongoTemplate?

对于MongoDB文档中的这个示例，如何使用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 a calculated field?

一般来说，我怎么用一个计算过的场来分组?

2 个解决方案

#1

You can actually do something like this with "project" first, but to me it's a little counter-intuitive to require a $project stage before hand:

实际上，你可以先用“项目”做类似的事情，但对我来说，事先要求一个$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")
    );

Like I said, counter-intuitive as you should just be able to declare all of this under $group stage, but the helpers don't seem to work this way. The serialization comes out a bit funny ( wraps the date operator arguments with arrays ) but it does seem to work. But still, this is two pipeline stages rather than one.

就像我说的，反直觉，你应该能够在$group stage下声明所有这些，但是助手们似乎不是这样工作的。序列化结果有点滑稽(用数组封装日期操作符参数)，但它确实有效。但是，这仍然是两个流水线阶段而不是一个阶段。

What is the problem with this? Well by separating the stages the stages the "project" portion forces the processing of all of the documents in the pipeline in order to get the calculated fields, that means it passes through everything before moving on to the group stage.

这有什么问题?通过将各个阶段分开，“项目”部分强制处理管道中的所有文档，以获得计算的字段，这意味着它在进入组阶段之前要遍历所有内容。

The difference in processing time can be clearly seen by running the queries in both forms. With a separate project stage, on my hardware takes three times longer to execute than the query where all fields are calculated during the "group" operation.

通过以两种形式运行查询，可以清楚地看到处理时间的差异。在一个单独的项目阶段，在我的硬件上执行的时间比在“组”操作期间计算所有字段的查询要长三倍。

So it seems the only present way to construct this properly is by building the pipeline object 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 of that seems to terse to you, then you can always work with the JSON source and parse that. But of course, it has to be valid JSON:

或者，如果所有这些对您来说都很简单，那么您总是可以使用JSON源并对其进行解析。当然，它必须是有效的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);

#2

Another alternative is to use a custom aggregation operation class defined like so:

另一种选择是使用自定义聚合操作类，定义如下:

public class CustomAggregationOperation implements AggregationOperation {
    private DBObject operation;

    public CustomAggregationOperation (DBObject operation) {
        this.operation = operation;
    }

    @Override
    public DBObject toDBObject(AggregationOperationContext context) {
        return context.getMappedObject(operation);
    }
}

Then implement it in the pipeline like so:

然后在流水线上实现如下:

Aggregation aggregation = newAggregation(
    new CustomAggregationOperation(
        new BasicDBObject(
            "$group",
            new BasicDBObject("_id",
                new BasicDBObject("day", new BasicDBObject("$dayOfMonth", "$date" ))
                .append("month", new BasicDBObject("$month", "$date"))
                .append("year", new BasicDBObject("$year", "$date"))
            )
            .append("totalPrice", new BasicDBObject(
                "$sum", new BasicDBObject(
                    "$multiply", Arrays.asList("$price","$quantity")
                )
            ))
            .append("averageQuantity", new BasicDBObject("$avg", "$quantity"))
            .append("count",new BasicDBObject("$sum",1))
        )
    )
)

So it's basically just an interface that is consistent with that used by the existing pipeline helpers, but instead of using other helper methods it directly takes a DBObject definition to return in pipeline construction.

因此，它基本上只是一个与现有管道助手使用的接口相一致的接口，但是它不使用其他助手方法，而是直接使用一个DBObject定义在管道构建中返回。

#1