Mongo aggregation framework: group users by age

Question

I have a user base stored in mongo. Users may record their date of birth. I need to run a report aggregating users by age.

I now have a pipeline that groups users by year of birth. However, that is not precise enough because most people are not born on January 1st; so even if they are born in, say, 1970, they may well not be 43 yet.

db.Users.aggregate([
    { $match : { "DateOfBirth" : { $exists : true} } },
    { $project : {"YearOfBirth" : {$year : "$DateOfBirth"} } }, 
    { $group : { _id : "$YearOfBirth", Total : { $sum : 1} } },
    { $sort : { "Total" : -1 } }
])

Do you know if it's possible to perform some kind of arithmetic within the aggregation framework to exactly calculate the age of a user? Or is this possible with MapReduce only?

Answer 1

It seems like the whole thing is possible with the new Mongo 2.4 version just released, supporting additional Date operations (namely the "$subtract").

Here's how I did it:

db.Users.aggregate([
    { $match : { "DateOfBirth" : { $exists : true} } },
    { $project : {"ageInMillis" : {$subtract : [new Date(), "$DateOfBirth"] } } }, 
    { $project : {"age" : {$divide : ["$ageInMillis", 31558464000] }}},
    // take the floor of the previous number:
    { $project : {"age" : {$subtract : ["$age", {$mod : ["$age",1]}]}}},
    { $group : { _id : "$age", Total : { $sum : 1} } },
    { $sort : { "Total" : -1 } }
])

Answer 2

There are not enough dateTime operators and math operators to project out the date. But you might be able to create age ranges by composing a dynamic query:

Define your date ranges as cut-off dates as

dt18 = today - 18 dt25 = today - 25 ... dt65 = today - 65

Then do nested conditionals, where you progressively use the cut off dates as age group markers, like so:

db.folks.save({ "_id" : 1, "bd" : ISODate("2000-02-03T00:00:00Z") });
db.folks.save({ "_id" : 2, "bd" : ISODate("2010-06-07T00:00:00Z") });
db.folks.save({ "_id" : 3, "bd" : ISODate("1990-10-20T00:00:00Z") });
db.folks.save({ "_id" : 4, "bd" : ISODate("1964-09-23T00:00:00Z") });


db.folks.aggregate(
    {
    $project: {
        ageGroup: {
            $cond: [{
                $gt: ["$bd",
                ISODate("1995-03-19")]
            },
            "age0_18",
            {
                $cond: [{
                    $gt: ["$bd",
                    ISODate("1988-03-19")]
                },
                "age18_25",
                "age25_plus"]
            }]
        }
    }
},
{
    $group: {
        _id: "$ageGroup",
        count: {
            $sum: 1
        }
    }
})