CODEWITHSUNDEEP
springmongodbspring-bootaggregate
Adam Meadows
Adam Meadows

Reputation: 73

Spring Mongo aggragation average per hour

I've been scratching my head how to aggragate data in a way where I can use it further down the application.

What im trying to do is get the raw

{
    "_id" : ObjectId("61e0898effba6778d05827e0"),
    "customId" : {
        "_id" : UUID("315fa023-f6a4-4d34-865c-dbe661e46cd1")
    },
    "viewers" : 1,
    "auditTime" : ISODate("2022-01-13T20:20:30.880Z"),
}
* lots

Into

{
    "customId" : {
        "_id" : UUID("315fa023-f6a4-4d34-865c-dbe661e46cd1")
    },
    "averageViewers" : 5,
    "timePeriod" : ISODate("2022-01-13T20:10:00.00Z"),
}

I've got the matching down for time periods

        TypedAggregation<HistoricEntity> agg =
            Aggregation.newAggregation(HistoricEntity.class,
                                       Aggregation.match(Criteria.where("customId").is(channelId)),
                                       Aggregation.match(Criteria.where("auditTime").gte(start).andOperator(Criteria.where("auditTime").lte(end))),
                                       Aggregation.group("auditTime").avg("viewers").as("viewers")
            );

        AggregationResults<Map> aggregate = mongoTemplate.aggregate(agg, Map.class);

I think the idea solution would be to use the mongo atlas aggragations which i think will be easy once this is fixed.

But im a loss. Any tips or tricks will be super appreciated

Thanks Adam

Upvotes: 1

Views: 357

Answers (3)

Mr_Thorynque
Mr_Thorynque

Reputation: 2002

Some improvement user DateTrunc no need to project a second time:

ProjectionOperation convertAuditTimeToHourOp = Aggregation.project("channelId", "viewers", "auditTime")
                    .and(DateOperators.dateOf("auditTime").truncate("auditHour"))
                    .as("auditHour");
        
GroupOperation averageViewers = Aggregation.group("customId", "auditHour")
        .avg("viewers").as("averageViewers")
        .first("auditHour").as("auditHour")
        .first("customId").as("customId");

Upvotes: 0

Adam Meadows
Adam Meadows

Reputation: 73

@indybee

This worked with a little fiddling.

I changed count to average to get the average per hour (sorry dodgy question)

And used the DateOperator to pull Year Month day hour out of it

     ProjectionOperation convertAuditTimeToHourOp = Aggregation.project("channelId", "viewers", "auditTime")
            .and(DateOperators.dateOf("auditTime").toString("%Y-%m-%d-%H"))
            .as("auditHour");

        GroupOperation averageViewers = Aggregation.group("customId", "auditHour").avg("viewers").as("averageViewers");

    ProjectionOperation convertTimePeriodToDate = Aggregation.project("customId", "averageViewers", "auditHour")
            .and(DateOperators.DateFromString.fromStringOf("auditHour"))
            .as("timePeriod");

 ....

Thank you!

Upvotes: 1

indybee
indybee

Reputation: 1736

ProjectionOperation convertAuditTimeToHourOp = Aggregation.project("customId", "viewers", "auditTime")
                .and(StringOperators.Substr.valueOf("auditTime").substring(0, 13))
                .as("auditHour");
                
GroupOperation countOp = Aggregation.group("customId", "auditHour").count().as("averageViewers");
        
ProjectionOperation convertAuditHourToDateOp = Aggregation.project("customId", "averageViewers", "auditHour")
                .and(StringOperators.Concat.valueOf("auditHour").concat(":00:00.000Z")).as("timePeriodString");
        
ProjectionOperation convertTimePeriodToDate = Aggregation.project("customId", "averageViewers", "timePeriodString")
                .and(DateFromString.fromStringOf("timePeriodString"))
                .as("timePeriod");

AggregationResults<Document> aggregate = mongoTemplate.aggregate(
                Aggregation.newAggregation(convertAuditTimeToHourOp, countOp, convertAuditHourToDateOp, convertTimePeriodToDate),
                HistoricEntity.class, 
                Document.class
);

List<Document> mappedResults = aggregate.getMappedResults();

Upvotes: 1

Related Questions