CODEWITHSUNDEEP
mongodb
Sambhav Sharma
Sambhav Sharma

Reputation: 5860

MongoDB aggregation over a range

I have documents of the following format:

[
  {
    date:"2014-07-07",
    value: 20
  },
  {
    date:"2014-07-08",
    value: 29
  },
  {
    date:"2014-07-09",
    value: 24
  },
  {
    date:"2014-07-10",
    value: 21
  }
]

I want to run an aggregation query that gives me results in date ranges. for example 

[
  { sum: 49 },
  { sum:45 },
]

So these are daily values, I need to know the sum of value field for last 7 days. and 7 days before these. for example sum from May 1 to May 6 and then sum from May 7 to May 14.

Can I use aggregation with multiple groups and range to get this result in a single mongodb query?

Upvotes: 1

Views: 1897

Answers (1)

Asya Kamsky
Asya Kamsky

Reputation: 42352

You can use aggregation to group by anything that can be computed from the source documents, as long as you know exactly what you want to do.

Based on your document content and sample output, I'm guessing that you are summing by two day intervals. Here is how you would write aggregation to output this on your sample data:

var range1={$and:[{"$gte":["$date","2014-07-07"]},{$lte:["$date","2014-07-08"]}]}
var range2={$and:[{"$gte":["$date","2014-07-09"]},{$lte:["$date","2014-07-10"]}]}
db.range.aggregate(
    {$project:{
         dateRange:{$cond:{if:range1, then:"dateRange1",else:{$cond:{if:range2, then:"dateRange2", else:"NotInRange"}}}},
         value:1}
    }, 
    {$group:{_id:"$dateRange", sum:{$sum:"$value"}}}
)
{ "_id" : "dateRange2", "sum" : 45 }
{ "_id" : "dateRange1", "sum" : 49 }

Substitute your dates for strings in range1 and range2 and optionally you can filter before you start to only operate on documents which are already in the full ranges you are aggregating over.

Upvotes: 1

Related Questions