$substr at java driver group query

[윤경환]

i want just get monthly group count.  (regDate format : yyyy-MM-dd HH:mm:ss) 

So, i generated this query

db.siteData.aggregate([

   {

      "$group":{"_id":{"date":{"$substr":["$regDate",0,7]} , "count":{"$sum:1"}}      

   },

   {

     "$sort": {"_id.date":1}

   }

])

result is

{

   "_id" : {"date":"2017-04"} , "count" : 5400

}

{

   "_id" : {"date":"2017-05"} , "count" : 1233

}

it's correct !! 

Now i converted this query to java mongotemplate like this~

Aggregation agg = Aggregation.newAggregation(

                                             Aggregation.group("substr(regDate,0,7)").count().as("count"),

                                             Aggregation.sort(Sort.Direction.DESC, "regDate")

                           );

But! it's not working. The result is wrong 

"_id":null , "count": 6633

substr is not working... is there any problem? or another way?

[Wan Bachtiar]

substr is not working… is there any problem?

Hi,

You can see from the second result of the aggregation count (i.e. 6633) that it’s the total number of the first aggregation result i.e. (5400 + 1233). The numbers are totalled (grouped) because they shared the same group id. As you may be aware, this is because string substr(regDate, 0, 7) is not being evaluated but treated as a string value for group id . See also $group operator.

One way to achieve what you’re after with spring-data MongoTemplate is to use ProjectOperation first, for example:

AggregationResults<Document> cur = mongoTemplate.aggregate(newAggregation(
                                                       project()
                                                          .andExpression("substr(date, 0, 7)")
                                                          .as("regDate"),
                                                    group("regDate").count().as("count"), 
                                                    sort(Sort.Direction.DESC, "regDate")), 
                                                 "collectionName", 
                                                 Document.class);

The above snippet is tested using spring-data-mongodb v1.10.1, MongoDB Java Driver v3.4.2 and MongoDB Server v3.4.x.

If you have further questions, please provide:

• MongoDB version
• spring-data-mongodb version
• MongoDB Java driver version

Regards,

Wan.

​