CODEWITHSUNDEEP
springmongodbmongodb-queryspring-dataaggregation-framework
Amir Kost
Amir Kost

Reputation: 2168

MongoDB group by query running from Spring MongoTemplate does not return sum

I have the following query which works fine when running from MongoDB shell:

db.feedback.aggregate([
    {
        $group: {
           _id: {
               year: { $year: '$receivedDate' }, 
               month: { $month: '$receivedDate' }
           }, 
           count: { $sum: 1 }
        }
    }
])

It returns dates (year, month) and their sum.

However, when I run the same query from Spring's MongoTemplate I get the date, and the sum is always null:

private static final String QUERY_STATS_BY_MONTH = "{
    aggregate: 'feedback', 
    pipeline: ([
        {
            $group:{
                _id: {
                    year: { $year: '$receivedDate' }, 
                    month: { $month: '$receivedDate' }
                }, 
                count: { $sum: 1 }
            }
        } 
    ])";
    ....


    CommandResult commandResult = mongoTemplate.executeCommand(QUERY_STATS_BY_DAY);
    BasicDBList dbList = (BasicDBList) commandResult.get(QUERY_RESULT);
    Iterator<Object> it = dbList.iterator();
    List<FeedbackDateAggregation> res = new ArrayList();
    while (it.hasNext()) {
        DBObject dbObject = (DBObject) it.next();
        res.add(mongoTemplate.getConverter().read(FeedbackDateAggregation.class, dbObject));
    }
    return res;

My domain classes are:

public class FeedbackDateAggregation {

    private StatsDate id;
    private Long count;

    public Long getCount() {
        return count;
    }

    public void setCount(Long count) {
        this.count = count;
    }

    public StatsDate getId() {
        return id;
    }

    public void setId(StatsDate id) {
        this.id = id;
    }
}

and: 

public class StatsDate {

    private int year;
    private int month;
    private int day;
    private int week;

    public int getYear() {
        return year;
    }

    public void setYear(int year) {
        this.year = year;
    }

    public int getMonth() {
        return month;
    }

    public void setMonth(int month) {
        this.month = month;
    }

    public int getDay() {
        return day;
    }

    public void setDay(int day) {
        this.day = day;
    }

    public int getWeek() {
        return week;
    }

    public void setWeek(int week) {
        this.week = week;
    }
}

The StatsDate are retrieved, but count is always null.

Upvotes: 1

Views: 2838

Answers (3)

Amir Kost
Amir Kost

Reputation: 2168

The problem was that I used an old version of Fongo (fake mongo for tests). Once I tried it on a real MongoDB, it worked like a charm. When I updated my Fongo version, it worked as well. I also had to change my domain objects. I have now a domain object that includes day, week, month, year, count.

Upvotes: 0

s7vr
s7vr

Reputation: 75924

This is the idiomatic way of doing aggregation queries using spring template. You don't need to access the low level CommandResult mongo api, only need for advance use.

Makes use of AggregationOperation as the operator $year and $month are not supported out of box in the spring group stage yet.

AggregationOperation group = new AggregationOperation() {
  @Override
  public DBObject toDBObject(AggregationOperationContext context) {
    return new BasicDBObject(
            "$group", new BasicDBObject(
              "_id", new BasicDBObject(
                "year", new BasicDBObject("$year", "$receivedDate")).append(
                "month", new BasicDBObject("$month", "$receivedDate"))).append(
            "count", new BasicDBObject("$sum", 1)));
   }
};

newAggregation is the static helper method which takes a list of pipeline stages.

Aggregation aggregation = newAggregation(group);

Finally, mongo template aggregate method to run the query.

List<FeedbackDateAggregation> res = mongoTemplate.aggregate(aggregation, "feedback", FeedbackDateAggregation.class).getMappedResults();

Generated Query:

{
    "aggregate": "__collection__",
    "pipeline": [{
        "$group": {
            "_id": {
                "year": {
                    "$year": "$receivedDate"
                },
                "month": {
                    "$month": "$receivedDate"
                }
            },
            "count": {
                "$sum": 1
            }
        }
    }]
}

Upvotes: 2

chridam
chridam

Reputation: 103355

You could try projecting the fields you want to use as the group by keys first by using the SpEL andExpression in the projection operation and then group by the new fields in the group operation:

Aggregation agg = newAggregation(
    project("id")
        .andExpression("month(receivedDate)").as("month")
        .andExpression("year(receivedDate)").as("year"),
    group("year", "month").count().as("count")
);

AggregationResults<FeedbackDateAggregation> results = mongoTemplate.aggregate(
    agg, 
    "feedback", 
    FeedbackDateAggregation.class
);
List<FeedbackDateAggregation> res = results.getMappedResults();

In the above a new aggregation via the newAggregation static factory method is created to which you pass a list of aggregation operations. These aggregate operations define the aggregation pipeline of your Aggregation.

In the first step you project the fields from the input collection by using the SpEL andExpression with the project operation.

In the second step you use the group operation to define a group for each "year" and "month"-value for which you aggregate the occurrence count via the count() aggregation operator and collect the result in a new field called "count".

Finally call the aggregate() Method on the MongoTemplate in order to let MongoDB perform the actual aggregation operation with the created Aggregation as an argument.

Upvotes: 1

Related Questions