CODEWITHSUNDEEP
springmongodbspring-data-mongodb
Pankaj
Pankaj

Reputation: 1271

spring mongodb aggregation compare two field and get sum of one column

I have a order collection and orderLastStatusChangeDatetime, estimatedDeliveryDatetime and orderPrice are filed name of orders collection. I have to get sum of orderPrice where orderLastStatusChangeDatetime less or equal to estimatedDeliveryDatetime . I have used the below query to get the total record ...

Criteria criteria = new Criteria() {
        @Override
        public DBObject getCriteriaObject() {
            DBObject obj = new BasicDBObject();
            obj.put("$where", "this.orderLastStatusChangeDatetime <= this.estimatedDeliveryDatetime");
            return obj;
        }
    };

    Query query = new Query();


    query.addCriteria(criteria);


    totalOrder = (int) mongoTemplate.count(query,ORDERS_COLLECTION_NAME);

But I have to get the sum of order price. I have use the same criteria in aggregation match . But it give error "Command failed with error 16395: 'exception: $where is not allowed inside of a $match aggregation expression'"

Upvotes: 0

Views: 3031

Answers (2)

Horatiu
Horatiu

Reputation: 385

You can find lots of example how to use it here: https://www.javatips.net/api/spring-data-mongodb-master/spring-data-mongodb/src/test/java/org/springframework/data/mongodb/core/aggregation/ProjectionOperationUnitTests.java

Upvotes: 0

s7vr
s7vr

Reputation: 75924

You can use the below aggregation pipeline. Creates a cmp field in $project stage to hold the result for orderLastStatusChangeDatetime <= estimatedDeliveryDatetime followed by $match with cmp equal to true and $group with $sum the order price.

import static org.springframework.data.mongodb.core.aggregation.Aggregation.*;
import static org.springframework.data.mongodb.core.query.Criteria.where;

Aggregation aggregation = newAggregation(project("orderPrice").andExpression("orderLastStatusChangeDatetime <= estimatedDeliveryDatetime").as("cmp"), match(Criteria.where("cmp").is(true)), group().sum("orderPrice").as("total"));

BasicDBObject results = mongoOperations.aggregate(aggregation, ORDERS_COLLECTION_NAME, BasicDBObject.class).getUniqueMappedResult();
int totalOrder = results.getInt("total");

Update: Use AggregationExpression in 1.8.5 RELEASE

Aggregation agg = newAggregation(
    project("orderPrice").and(new AggregationExpression() {
                @Override
                public DBObject toDbObject(AggregationOperationContext context) {
                    return new BasicDBObject("$lte", Arrays.<Object>asList("$orderLastStatusChangeDatetime", "$estimatedDeliveryDatetime"));
                    }
    }).as("cmp"),
    match(Criteria.where("cmp").is(true)),
    group().sum("orderPrice").as("total")
);

Upvotes: 2

Related Questions