CODEWITHSUNDEEP
javamongodbaggregation-frameworkspring-data-mongodb
Sam
Sam

Reputation: 6890

Two Aggregate Totals in One Group

I wrote a query in MongoDB as follows:

db.getCollection('student').aggregate(
[
  {
      $match: { "student_age" : { "$ne" : 15 } }
  },
  {
      $group: 
      {
        _id: "$student_name", 
        count: {$sum: 1},
        sum1: {$sum: "$student_age"}
      }
  }     
])

In others words, I want to fetch the count of students that aren't 15 years old and the summary of their age. The query works fine and I get two data items.

In my application, I want to do the query by Spring Data. I wrote the following code:

Criteria where = Criteria.where("AGE").ne(15);
Aggregation aggregation = Aggregation.newAggregation(
                Aggregation.match(where),
                Aggregation.group().sum("student_age").as("totalAge"),
                count().as("countOfStudentNot15YearsOld"));

When this code is run, the output query will be:

"aggregate" : "MyDocument", "pipeline" : 
    [           { "$match"   { "AGE" : { "$ne" : 15 } } }, 
                { "$group" : { "_id" : null, "totalAge" : { "$sum" : "$student_age" } } }, 
                { "$count" : "countOfStudentNot15YearsOld" }], 
                  "cursor" : { "batchSize" : 2147483647 }

Unfortunately, the result is only countOfStudentNot15YearsOld item.

I want to fetch the result like my native query.

Upvotes: 3

Views: 1031

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151092

If your're asking to return the grouping for both "15" and "not 15" as a result then you're looking for the $cond operator which will allow a "branching" based on conditional evaluation.

From the "shell" content you would use it like this:

db.getCollection('student').aggregate([
  { "$group": {
    "_id": null,
    "countFiteen": { 
      "$sum": {
        "$cond": [{ "$eq": [ "$student_age", 15 ] }, 1, 0 ]
      }
    },
    "countNotFifteen": {
      "$sum": {
        "$cond": [{ "$ne": [ "$student_age", 15 ] }, 1, 0 ]
      }
    },
     "sumNotFifteen": {
      "$sum": {
        "$cond": [{ "$ne": [ "$student_age", 15 ] }, "$student_age", 0 ]
      }
    }
  }}
])

So you use the $cond to perform a logical test, in this case whether the "student_age" in the current document being considered is 15 or not, then you can return a numerical value in response which is 1 here for "counting" or the actual field value when that is what you want to send to the accumulator instead. In short it's a "ternary" operator or if/then/else condition ( which in fact can be shown in the more expressive form with keys ) you can use to test a condition and decide what to return.

For the spring mongodb implementation you use ConditionalOperators.Cond to construct the same BSON expressions:

import org.springframework.data.mongodb.core.aggregation.*;

ConditionalOperators.Cond isFifteen = ConditionalOperators.when(new Criteria("student_age").is(15))
   .then(1).otherwise(0);

ConditionalOperators.Cond notFifteen = ConditionalOperators.when(new Criteria("student_age").ne(15))
        .then(1).otherwise(0);

ConditionalOperators.Cond sumNotFifteen = ConditionalOperators.when(new Criteria("student_age").ne(15))
        .thenValueOf("student_age").otherwise(0);


GroupOperation groupStage =  Aggregation.group()
        .sum(isFifteen).as("countFifteen")
        .sum(notFifteen).as("countNotFifteen")
        .sum(sumNotFifteen).as("sumNotFifteen");

Aggregation aggregation = Aggregation.newAggregation(groupStage);

So basically you just extend off of that logic, using .then() for a "constant" value such as 1 for the "counts", and .thenValueOf() where you actually need the "value" of a field from the document, so basically equal to the "$student_age" as shown for the common shell notation.

Since ConditionalOperators.Cond shares the AggregationExpression interface, this can be used with .sum() in the form that accepts an AggregationExpression as opposed to a string. This is an improvement on past releases of spring mongo which would require you to perform a $project stage so there were actual document properties for the evaluated expression prior to performing a $group.

If all you want is to replicate the original query for spring mongodb, then your mistake was using the $count aggregation stage rather than appending to the group():

Criteria where = Criteria.where("AGE").ne(15);
Aggregation aggregation = Aggregation.newAggregation(
                Aggregation.match(where),
                Aggregation.group()
                    .sum("student_age").as("totalAge")
                    .count().as("countOfStudentNot15YearsOld")
);

Upvotes: 2

Related Questions