FAQ
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+-+Expression+Reference> 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!

--
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/-/L-QI7OI5k6kJ.
To post to this group, send email to mongodb-user@googlegroups.com.
To unsubscribe from this group, send email to mongodb-user+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

Search Discussions

  • Songhe yang at Feb 11, 2012 at 12:47 pm
    How about the performance of it ?
    On Feb 11, 6:57 am, Randy Merrill 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
    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!
    --
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com.
    To unsubscribe from this group, send email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
  • Randy Merrill at Feb 11, 2012 at 4:11 pm
    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 mongodb-user@googlegroups.com.
    To unsubscribe from this group, send email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
  • Sam Millman at Feb 11, 2012 at 4:15 pm
    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 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 mongodb-user@googlegroups.com.
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit this group at
    http://groups.google.com/group/mongodb-user?hl=en.
    --
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com.
    To unsubscribe from this group, send email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
  • Randy Merrill at Feb 11, 2012 at 4:32 pm
    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.

    --
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com.
    To unsubscribe from this group, send email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
  • Randy Merrill at Feb 13, 2012 at 10:14 pm
    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
    }
    }
    ]
    });

    --
    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/-/M7KBvpI2VaUJ.
    To post to this group, send email to mongodb-user@googlegroups.com.
    To unsubscribe from this group, send email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
  • Johno Scott at Apr 24, 2013 at 3:50 am
    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"
    ]
    }, ...
    On Tuesday, 14 February 2012 09:14:22 UTC+11, Randy Merrill wrote:

    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
    }
    }
    ]
    });
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Asya Kamsky at Apr 24, 2013 at 4:03 am
    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"
    ]
    }, ...
    On Tuesday, 14 February 2012 09:14:22 UTC+11, Randy Merrill wrote:

    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
    }
    }
    ]
    });
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupmongodb-user @
categoriesmongodb
postedFeb 10, '12 at 10:57p
activeApr 24, '13 at 4:03a
posts8
users5
websitemongodb.org
irc#mongodb

People

Translate

site design / logo © 2022 Grokbase