Spring data mongo - Get sum of array of object

Question

I have the following document:

{
   pmv: {
        budgets: [
           {
              amount: 10
           },
           {   
              amount: 20
           }
         ]
     }
}

and I need to sum the amount field from every object in budgets. But it's also possible that the budget object doesn't exist so I need to check that.

How could I do this? I've seen many questions but with projections, I just need a integer number which in this case would be 30.

How can I do it?

Thanks.

EDIT 1 FOR PUNIT

This is the code I tried but its giving me and empty aray

AggregationOperation filter = match(Criteria.where("pmv.budgets").exists(true).not().size(0));
            AggregationOperation unwind = unwind("pmv.budgets");
            AggregationOperation sum = group().sum("budgets").as("amount");

            Aggregation aggregation = newAggregation(filter, unwind, sum);

            mongoTemplate.aggregate(aggregation,"Iniciativas",String.class);

            AggregationResults<String> aggregationa = mongoTemplate.aggregate(aggregation,"Iniciativas",String.class);

            List<String> results = aggregationa.getMappedResults();

Punit Tiwan · Accepted Answer · 2019-06-04 08:33:39Z

2

You can do this with aggregation pipeline

db.COLLECTION_NAME.aggregate([
  {"pmv.budgets":{$exists:true,$not:{$size:0}}},
  {$unwind:"$pmv.budgets"},
  {amount:{$sum:"$pmv.budgets"}}
]);

This pipeline contains three queries:

get document having non-null and non-empty budgets
$unwind basically open the array and create one document for each array element. e.g. if one document of budgets has 3 elements then it will create 3 document and fill budgets property from each of the array element. You can read more about it here
sum all the budgets property using $sum operator

You can read more about aggregation pipeline here

EDIT: as per comments, adding code for java as well.

AggregationOperation filter = match(Criteria.where("pmv.budgets").exists(true).not().size(0));
AggregationOperation unwind = unwind("pmv.budgets");
AggregationOperation sum = group().sum("pmv.budgets").as("amount");

Aggregation aggregation = newAggregation(filter, unwind, sum);

mongoTemplate.aggregate(aggregation,COLLECTION_NAME,Output.class);

You can do this in more inline way as well but I wrote it like this so that it will be easy to understand.

I hope this answer your question.

edited Jun 4, 2019 at 8:33

answered May 30, 2019 at 12:39

Punit Tiwan

1065 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Wrong Over a year ago

I know how it's in Mongo but idk how to translate it into java

Punit Tiwan Over a year ago

hi @Wrong, added java code as well. let me know if you need more help.

Wrong Over a year ago

I am really sorry @PunitTiwan, I forgot to mention that the budget object is inside another object. I've edited my questions with your code and what I tried but success.

Punit Tiwan Over a year ago

@Wrong I think you will have to use "pmv.budgets" instead of "budgets" in sum operator or give the projected name as "budgets" in unwind

Wrong Over a year ago

I had to put 'pmv' in the match criteria (it might not exists but budgets always exist if pmv exists), then $budgets in the unwind and then 'budgets' in the sum. I'll accept your answer but modify that forr future readers, please :)

|

Collectives™ on Stack Overflow

Spring data mongo - Get sum of array of object

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related