Sum int values of an array field

Question

I have a records collection with thousands of documents, like the following:

{ "_id" : ObjectId("5ee1e209e07f053f990cea8c"), "key" : "TAKwGc6Jr4i8Z487", "createdAt" : ISODate("2017-01-28T01:22:14.398Z"), "counts" : [ 150, 160 ], "value" : "Doc1" }
{ "_id" : ObjectId("5ee1e8dee07f053f990ceaa1"), "key" : "TAKwGc6Jr4i8Z487", "createdAt" : ISODate("2017-01-28T01:22:14.398Z"), "counts" : [ 170 ], "value" : "Doc2" }
...

I want to write a query to select them by date range, and by the total count of elements in the counts array field.

I haven't managed to find any resources on that. Should an aggregation be used, in this case?

Like $sum something?

{$and:[
  {createdAt:{$gte:ISODate('2016-01-30'),$lt:ISODate('2016-02-01')}},
  {<sum> ???}
]}

Viettel Solutions · Accepted Answer · 2021-12-18 18:47:13Z

1

You can add a field to calculate sum of counts using $addFields, then use $match to compare sum and finally remove that temporary field before return using $unset.
Here is the playground for your example.

edited Dec 18, 2021 at 18:47

answered Dec 18, 2021 at 18:42

Viettel Solutions

1,52715 silver badges23 bronze badges

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

2 Comments

Fabio B. Over a year ago

Please, how can I add the range date filter to your query? Also, can I use $gt and $lte instead of $match ?

Viettel Solutions Over a year ago

I've modified the playground link and added filter by date range for createdAt. You can use $gt and $lte in $match.

Collectives™ on Stack Overflow

Sum int values of an array field

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related