Spring Data Aggregate Query

Question

Team,

I am new to Spring data for mongodb. I am trying to learn the Spring data code for aggregation query. but most of the tutorial shown only the simple examples. Could you please help me build the spring data code for the given complex aggregate example.

SCHEMA:
{
    "s"  : "CB",
    "c"  : "REQ_RCV",
    "e"  : "cta_sms_click",
    "st" : "i",
    "b"  : "UB",
    "a"  : "account-1",
    "u"  : "b1_h1_d1_m1_user_2",
    "c#" : "b1_h1_d1_m1_cr-2",
    "@"  : ISODate("2016-10-01T06:03:00.000Z"),
    "@h" : "16100106",
    "@d" : "161001",
    "@m" : "1610"
}


QUERY:
db.COLLECTION_NAME.aggregate([                       
        {$match:{"st":"i","@":{$gte : new ISODate("2015-10-01T06:00:00Z"), $lte : new ISODate("2017-10-02T10:00:00Z")}, "c":"REQ_RCV"}}, 
        {$group:{_id:{"b":"$b", "HOURLY":"$@h"}, count:{$sum:1}}}, 
        {$project : {_id:0, "BUCKET":"$_id.b", "TIME":"$_id.HOURLY", count:1}},
        {$sort:{"BUCKET":1, "TIME":1}}                     
    ]);

Complexities:

1. $match has muliple criterias
2. $project has to access the inner field of group under _id
3. The result can not be mapped to a class since it varies based on $project field changes. Ultimately i would like to map it to java.util.HashMap, so that i can put any fields inside $project. Is that possible?

---

Initial answer from Veeram for reference:

---

Aggregation agg = newAggregation(match(where("st").is("i").and("@").gte(start_date).lte(end_date).and("c").is("REQ_RCV")),
                    group(fields("b").and("HOURLY", "$@h")).count().as("count"),
                    project(fields("count").and("BUCKET","$_id.b").and("TIME", "$_id.HOURLY")),
                    sort(ASC, "BUCKET", "TIME"));

Answer 1

So there is probably a more spring-data way of doing this but what im doing is wiring the MongoTemplate and using that to the Aggreagation query. My Example us much simpler than what you want to do but maybe it can help :

    @Service
     public class AClass implements CategoryStructureService {

            @Autowired
            private MongoTemplate mongoTemplate ;

    ......
    private int method(CategoryStructureKey csKey) {

            Aggregation agg = Aggregation.newAggregation(Aggregation.match(Criteria
                    .where("_id").is(csKey)), Aggregation.unwind("fields"),
                    Aggregation.project("fields").andExclude("_id"), Aggregation
                            .sort(Direction.DESC, "fields.index"), Aggregation
                            .limit(1));

AggregationResults<MultiValuedFieldContainer> field = mongoTemplate
                .aggregate(agg, CategoryStructure.class,
                        MultiValuedFieldContainer.class);
        ....

        }

I did this with an old spring-boot project, having this as a dependency

<dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-mongodb</artifactId>
            <version>1.4.2.RELEASE</version>
        </dependency>

So it should work.

You can find a better example of MongoTemplate here https://www.mkyong.com/mongodb/spring-data-mongodb-aggregation-grouping-example/

And some other Agrregation examples https://github.com/spring-projects/spring-data-mongodb/blob/master/spring-data-mongodb/src/test/java/org/springframework/data/mongodb/core/aggregation/AggregationTests.java

Hope this helps

Answer 2

You can try something like below. Replace with your date values and collection name.

import static org.springframework.data.domain.Sort.Direction.ASC;
import static org.springframework.data.mongodb.core.aggregation.Aggregation.*;
import static org.springframework.data.mongodb.core.query.Criteria.where;

Aggregation agg = newAggregation(match(where("st").is("i").and("@").gte(new Date()).lte(new Date()).and("c").is("REQ_RCV")),
            group(fields("b").and("HOURLY", "$@h")).count().as("count"),
            sort(ASC, Aggregation.previousOperation()));

List<BasicDBObject> dbObjects = mongoOperations.aggregate(agg, "collection_name", BasicDBObject.class).getMappedResults();