java - optimize query- aggregration framework mongodb

Question

A student can give any no of tests. I need to get the marks of last 5 tests(or dataLimit ) given by each student in a class in given time.

Limit can not be used before grouping as any student can give the test any no of times.

The group operation is giving memory restriction error. "Exceeded memory limit for $group, but didn't allow external sort" Currently i have allowed the disk space usage as workaround, but how can i optimize this query ?

    {
        "_id" : ObjectId("5fa98dfedc0e820001326944"),
        "school" : "ABC",
        "scholbranch" : "XYZ",
        "class" : "7",
        "section" : "C",
        "studentname" : "Student 1",
        "testmarks" : "90",
        "dateTime" : ISODate("2020-11-09T18:44:14.000Z")
    }
    
        Query query = new Query();
        List<Criteria> docCriterias = new ArrayList<Criteria>();
        docCriterias.add(Criteria.where("school").is(school));
        docCriterias.add(Criteria.where("scholbranch").is(scholbranch));
        docCriterias.add(Criteria.where("class").is(class));
        docCriterias.add(Criteria.where("section").is(section));
        docCriterias.add(Criteria.where("dateTime").gte(date1));
        docCriterias.add(Criteria.where("dateTime").lt(date2));
        Criteria criteria = new Criteria();
        criteria.andOperator(docCriterias.toArray(new Criteria[docCriterias.size()]));
        int datalimit = noOfData;
        
        MatchOperation matchStage = Aggregation.match(criteria);

        SortOperation sortByDateDesc = Aggregation.sort(new Sort(Direction.DESC, "dateTime"));

        GroupOperation groupBy = Aggregation.group("$studentname").push(new BasicDBObject("studentname", "$studentname")
                .append("testmarks", "$testmarks").append("dateTime", "$dateTime")).as("val");

        ProjectionOperation projectToMatchModel = Aggregation.project().and("$_id").as("student").and("val")
                .slice(datalimit).as("mostRecentVal");

        Aggregation aggregation = Aggregation.newAggregation(matchStage, sortByDateDesc, groupBy,
                projectToMatchModel);

question from:https://stackoverflow.com/questions/65885744/optimize-query-aggregration-framework-mongodb

Answer 1

Waitting for answers