Group query result by month in mongoTemplate

I have a history collection that returns json as follow:

    "id": "60f53b546b089f10e96c0206",
    "workspace": null,
    "remote_url": "",
    "status": "SUCCESS",
    "branch": "origin/master",
    "description": "",
    "date": "2021-07-19 10:44:04",
    "project_id": "60ef4cccc3d2d312c81eab44",
    "build_id": "362",
    "type": "back",
    "backup_path": "/root/backups_api/backup_362"

I want to group count of success and failure status by month using mongotemplate in Springboot API.

Hello @youssef_boudaya, you can try this code:

MongoOperations mongoOps = new MongoTemplate(MongoClients.create(), "testDB");

Aggregation agg = newAggregation(
    Aggregation.project("date", "status")
        .and(DateOperators.Month.monthOf("date")).as("month"),"month", "status")
        .push(new Document("status", "$_id.status").append("count", "$count"))

AggregationResults<Document> aggResults = mongoOps.aggregate(agg, "testColl", Document.class);

i tried this

private final MongoOperations mongoOperations;
Aggregation agg = Aggregation.newAggregation(
			Aggregation.project("date", "status").and(DateOperators.Month.monthOf("date"))
					.as("month"),"month", "status").count().as("count"),"_id.month")
					.push(new Document("status", "$_id.status").append("count", "$count"))

	return mongoOperations.aggregate(agg, "history", Document.class);

i get “can’t convert from BSON type string to Date”

Run this query in the mongo shell or Compass and tell what you see for the dateType field:

  { $project: { _id: 0, date: 1, dateType: { $type: "$date" } } } 

i get this:

    { "date" : "2021-07-14 10:33:39", "dateType" : "string" }

The problem is when i tried to store “date” as mongo date object “ISO (…)” i get problems with that.
Is there a way to properly store “date” ad mongo data object and have it as “Date date;” in spring boot instead of “String date;” ?

@youssef_boudaya, it is not recommended to store date data as a string data type. There are advantages in storing date as _date_type. Some of them are: faster comparisons in matching, searching and sorting, conversion to other date fields like month/year, etc., and also date type is likely to take up less storage.

Now we know that the "date": "2021-07-19 10:44:04" is a string type. I will fix the code to convert it to date type and then extract the month and work with it. This will fix the error: “can’t convert from BSON type string to Date”

If i store “date” as mongo date object i should just modify “date” type in model to be Date instead of String ?

Yes, I believe so. This can be mapped to java.util.Date or java.time.LocalDateTime. Please do verify how it works with other functions in your application.

Change the above code to the following; this converts the string date filed to date type and extracts the month from it. This will fix the error.

Aggregation.project("date", "status")

i wanted to do the correct approach as you recommended so i did:
in database:

date:new Date();

In Spring boot (History model class):

import java.util.Date;
private Date date;

and i return result as

return queryResult.getMappedResults();

verything works fine on the webservice of displaying all histories.
Now when i access my websrvice by postman where i want to group status by month i got:

        "status_counts": [
                "status": "FAILURE",
                "count": 1
                "status": "SUCCESS",
                "count": 1
        "month": 8

This how i should set data in angular for my chart:

 labels: ['January', 'February', 'March', 'April', 'May', 'June', 'July'],
    datasets: [
            label: 'My First dataset',
            backgroundColor: '#42A5F5',
            data: [65, 59, 80, 81, 56, 55, 40]
            label: 'My Second dataset',
            backgroundColor: '#FFA726',
            data: [28, 48, 40, 19, 86, 27, 90]

Is it possible to furthermore format response to be easily consumed in angular ?

I don’t know how it could be done.

how can i change the result of the aggregation to show results like this:

 "failure_count": failure_count_number,
 "success_count": success_count_number,

You can use projection to alter the way the output is returned.

Aggregation agg = Aggregation.newAggregation(
	Aggregation.project("date", "status")
		.and(DateOperators.Month.monthOf("date")).as("month"),"month", "status")
		.push(new Document("k", "$_id.status").append("v", "$count"))

Thanks a lot worked like a charm.One last thing , is it possible to show months as " january,february…" ?

I believe it is possible, but it is fairly complex operation. You need to create an array of the month strings, and map them to the month number in the result.

no problem i will test with if condition in typescript.Thank you so much for your help

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.