Aggregation Framework to Group by Day

[Randy Merrill]

I was just testing out the new aggregation framework that is coming in 2.1.0 and thought that I would share how to group by the date.

Example Document: 

{

"OriginationDate" : ISODate("2006-12-12T08:00:00Z"),

"AmountBorrowed" : 2250.40

}

Originally I had just grouped by the OriginationDate, but that was grouping by the full datetime:

db.runCommand({

aggregate: 'Loan',

pipeline: [

{

$group: {

 _id: "$OriginationDate",

TotalBorrowed: { $sum: "$AmountBorrowed" }

}

}

]

});

Resulting in:

{

"_id" : ISODate("2006-06-23T07:00:00Z"),

"AmountBorrowed" : 174552

}

I used the $year, $month, and $dayOfMonth expressions to make the _id be unique by day:

db.runCommand({

aggregate: 'Loan',

pipeline: [

{

$group: {

 _id: {

OriginationYear: { $year: "$OriginationDate" },

OriginationMonth: { $month: "$OriginationDate" },

OriginationDay: { $dayOfMonth: "$OriginationDate" }

},

TotalBorrowed: { $sum: "$AmountBorrowed" }

}

}

]

});

Using the expressions I get 'an order-preserving concatenated key' that is unique by day, not by the full datetime:

{

"_id" : {

"OriginationYear" : 2009,

"OriginationMonth" : 12,

"OriginationDay" : 31

},

"AmountBorrowed" : 408503

}

The aggregation framework is looking to be pretty awesome!

[songhe yang]

How about the performance of it ?

On Feb 11, 6:57 am, Randy Merrill <zoram...@gmail.com> wrote:
> I was just testing out the new aggregation framework that is coming in
> 2.1.0 and thought that I would share how to group by the date.
>
> Example Document:
> {
> "OriginationDate" : ISODate("2006-12-12T08:00:00Z"),
> "AmountBorrowed" : 2250.40
>
> }
>
> Originally I had just grouped by the OriginationDate, but that was grouping
> by the full datetime:
>
> db.runCommand({
> aggregate: 'Loan',
> pipeline: [
>  {
>  $group: {
>    _id: "$OriginationDate",
>   TotalBorrowed: { $sum: "$AmountBorrowed" }
>  }
>  }
> ]
>
> });
>
> Resulting in:
>
> {
> "_id" : ISODate("2006-06-23T07:00:00Z"),
> "AmountBorrowed" : 174552
>
> }
>

> I used the $year, $month, and $dayOfMonth expressions<http://www.mongodb.org/display/DOCS/Aggregation+Framework+-+Expressio...> to

[Randy Merrill]

I ran the query on a collection with 48698 documents and it took 355 ms.

[Sam Millman]

That is considerably slow but as Scott says the aggregation framework still has a lot needing doing in terms of performance and whats out atm is really designed to be a taster.

On 11 February 2012 16:10, Randy Merrill <zora...@gmail.com> wrote:

I ran the query on a collection with 48698 documents and it took 355 ms.

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.

To view this discussion on the web visit https://groups.google.com/d/msg/mongodb-user/-/vDCM1TJJ8DUJ.

To post to this group, send email to mongod...@googlegroups.com.
To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

[Randy Merrill]

It is slower than 'desired', but for my use case right now it works great. Until it is actually released I am not going to worry too much about the performance testing.

I am sure it will get faster with the release version or in the future releases.

[Randy Merrill]

As a followup to this, I also found a way to put the date back together after you group them. It isn't very elegant or fast, but it works:

db.runCommand({

aggregate: 'Loan',

pipeline: [

{

$group: {

 _id: {

OriginationYear: { $year: "$OriginationDate" },

OriginationMonth: { $month: "$OriginationDate" },

OriginationDay: { $dayOfMonth: "$OriginationDate" }

},

TotalBorrowed: { $sum: "$AmountBorrowed" }

}

}, {

$project: {

OriginationDate: {

$add: [

"$_id.OriginationYear",

"-",

"$_id.OriginationMonth",

"-",

"$_id.OriginationDay"

]

},

TotalBorrowed: 1,

_id: 0

}

}

]

});

[Asya Kamsky]

Not sure why you are replying to a thread that's over a year old rather than asking a new question, but either way, there are lots of ways to deal with dates in 2.4 aggregation framework.

Take a look at this blog post, it probably will help:

http://www.kamsky.org/1/post/2013/03/stupid-date-tricks-with-aggregation-framework.html

Asya

On Tuesday, April 23, 2013 10:31:20 PM UTC-4, Johno Scott wrote:

The $add expression in the final $group doesnt work in MongoDB 2.4 because $add only accepts numbers.

I tried $concat (new in 2.4) but it only accepts an array of strings ! - arrgh!!!!

So how to you compose this string which is a mixture of numbers and strings ?

$project: {

OriginationDate: {

$add: [

"$_id.OriginationYear",

"-",

"$_id.OriginationMonth",

"-",

"$_id.OriginationDay"

]

}, ...