Find out if anyone got a birthday in the next 30 days with mongo

Question

Let's say that we have a collection of users, each happy birthday in BSON date format.

How can we run a request to find out all users who received a birthday in the next 30 days?

Answer 1

The aggregation structure, of course, is the right approach - everything that requires JS on the server is a performance issue, while aggregations are performed on the server in native code.

While it is possible to convert a birthday to dates of upcoming birthdays, and then run a range request, I prefer to do it a little differently.

The only "prerequisite is to calculate the current day of the year." There are ways to do this in different languages , so you can do this at the application level before invoking aggregation by passing this number to it. I was going to name my todayDayOfYear , but I realized that you can let the aggregation infrastructure understand this based on today, so the only variable is today.

 var today=new Date(); 

I accept the document, including the name and birthday, I change the options accordingly

 var p1 = { "$project" : { "_id" : 0, "name" : 1, "birthday" : 1, "todayDayOfYear" : { "$dayOfYear" : today }, "dayOfYear" : { "$dayOfYear" : "$birthday"} } }; 

Now ask how many days from today to the next birthday:

 var p2 = { "$project" : { "name" : 1, "birthday" : 1, "daysTillBirthday" : { "$subtract" : [ { "$add" : [ "$dayOfYear", { "$cond" : [{"$lt":["$dayOfYear","$todayDayOfYear"]},365,0 ] } ] }, "$todayDayOfYear" ] } } }; 

Exclude everything except those that are within the required range:

 var m = { "$match" : { "daysTillBirthday" : { "$lt" : 31 } } }; 

Now run the aggregation with:

 db.collection.aggregate( p1, p2, m ); 

to return a list of names, birthdays, and days before a birthday for all lucky people whose birthday is within 30 days.

EDIT

@ Sean999 caught an interesting extreme case - people who were born in a leap year after February 28 will count them. The following is an aggregation that is correctly configured for this:

 var p1 = { "$project" : { "_id" : 0, "name" : 1, "birthday" : 1, "todayDayOfYear" : { "$dayOfYear" : ISODate("2014-03-09T12:30:51.515Z") }, "leap" : { "$or" : [ { "$eq" : [ 0, { "$mod" : [ { "$year" : "$birthday" }, 400 ] } ] }, { "$and" : [ { "$eq" : [ 0, { "$mod" : [ { "$year" : "$birthday" }, 4 ] } ] }, { "$ne" : [ 0, { "$mod" : [ { "$year" : "$birthday" }, 100 ] } ] } ] } ] }, "dayOfYear" : { "$dayOfYear" : "$birthday" } } }; var p1p = { "$project" : { "name" : 1, "birthday" : 1, "todayDayOfYear" : 1, "dayOfYear" : { "$subtract" : [ "$dayOfYear", { "$cond" : [ { "$and" : [ "$leap", { "$gt" : [ "$dayOfYear", 59 ] } ] }, 1, 0 ] } ] } } } 

p2 and m remain the same as above.

Test input:

 db.birthdays.find({},{name:1,birthday:1,_id:0}) { "name" : "Ally", "birthday" : ISODate("1975-06-12T00:00:00Z") } { "name" : "Ben", "birthday" : ISODate("1968-04-03T00:00:00Z") } { "name" : "Mark", "birthday" : ISODate("1949-12-23T00:00:00Z") } { "name" : "Paul", "birthday" : ISODate("2014-03-04T15:59:05.374Z") } { "name" : "Paul", "birthday" : ISODate("2011-02-07T00:00:00Z") } { "name" : "Sean", "birthday" : ISODate("2004-01-31T00:00:00Z") } { "name" : "Tim", "birthday" : ISODate("2008-02-28T00:00:00Z") } { "name" : "Sandy", "birthday" : ISODate("2005-01-31T00:00:00Z") } { "name" : "Toni", "birthday" : ISODate("2009-02-28T00:00:00Z") } { "name" : "Sam", "birthday" : ISODate("2005-03-31T00:00:00Z") } { "name" : "Max", "birthday" : ISODate("2004-03-31T00:00:00Z") } { "name" : "Jen", "birthday" : ISODate("1971-04-03T00:00:00Z") } { "name" : "Ellen", "birthday" : ISODate("1996-02-28T00:00:00Z") } { "name" : "Fanny", "birthday" : ISODate("1996-02-29T00:00:00Z") } { "name" : "Gene", "birthday" : ISODate("1996-03-01T00:00:00Z") } { "name" : "Edgar", "birthday" : ISODate("1997-02-28T00:00:00Z") } { "name" : "George", "birthday" : ISODate("1997-03-01T00:00:00Z") } 

Output:

 db.birthdays.aggregate( p1, p1p, p2, {$sort:{daysTillBirthday:1}}); { "name" : "Sam", "birthday" : ISODate("2005-03-31T00:00:00Z"), "daysTillBirthday" : 22 } { "name" : "Max", "birthday" : ISODate("2004-03-31T00:00:00Z"), "daysTillBirthday" : 22 } { "name" : "Ben", "birthday" : ISODate("1968-04-03T00:00:00Z"), "daysTillBirthday" : 25 } { "name" : "Jen", "birthday" : ISODate("1971-04-03T00:00:00Z"), "daysTillBirthday" : 25 } { "name" : "Ally", "birthday" : ISODate("1975-06-12T00:00:00Z"), "daysTillBirthday" : 95 } { "name" : "Mark", "birthday" : ISODate("1949-12-23T00:00:00Z"), "daysTillBirthday" : 289 } { "name" : "Sean", "birthday" : ISODate("2004-01-31T00:00:00Z"), "daysTillBirthday" : 328 } { "name" : "Sandy", "birthday" : ISODate("2005-01-31T00:00:00Z"), "daysTillBirthday" : 328 } { "name" : "Paul", "birthday" : ISODate("2011-02-07T00:00:00Z"), "daysTillBirthday" : 335 } { "name" : "Tim", "birthday" : ISODate("2008-02-28T00:00:00Z"), "daysTillBirthday" : 356 } { "name" : "Toni", "birthday" : ISODate("2009-02-28T00:00:00Z"), "daysTillBirthday" : 356 } { "name" : "Ellen", "birthday" : ISODate("1996-02-28T00:00:00Z"), "daysTillBirthday" : 356 } { "name" : "Fanny", "birthday" : ISODate("1996-02-29T00:00:00Z"), "daysTillBirthday" : 356 } { "name" : "Edgar", "birthday" : ISODate("1997-02-28T00:00:00Z"), "daysTillBirthday" : 356 } { "name" : "Gene", "birthday" : ISODate("1996-03-01T00:00:00Z"), "daysTillBirthday" : 357 } { "name" : "George", "birthday" : ISODate("1997-03-01T00:00:00Z"), "daysTillBirthday" : 357 } { "name" : "Paul", "birthday" : ISODate("2014-03-04T15:59:05.374Z"), "daysTillBirthday" : 360 } 

You can see that people with the same birthday have the same number of days before the birthday, whether they were born in a leap year or not. You can now complete the step for the cut slice.

EDIT

Starting with version 3.5.11, there are several date manipulation expressions in the aggregation pipeline that greatly simplify writing. In particular, the expression $ dateFromParts allows you to build a date from different parts, allowing this aggregation:

 var today = new Date(); var a1 = {$addFields:{ today:{$dateFromParts:{year:{$year:today},month:{$month:today},day:{$dayOfMonth:today}}}, birthdayThisYear:{$dateFromParts:{year:{$year:today}, month:{$month:"$birthday"}, day:{$dayOfMonth:"$birthday"}}}, birthdayNextYear:{$dateFromParts:{year:{$add:[1,{$year:today}]}, month:{$month:"$birthday"}, day:{$dayOfMonth:"$birthday"}}} }}; var a2 = {$addFields:{ nextBirthday:{$cond:[ {$gte:[ "$birthdayThisYear", "$today"]}, "$birthdayThisYear", "$birthdayNextYear"]} }}; var p1 = {$project:{ name:1, birthday:1, daysTillNextBirthday:{$divide:[ {$subtract:["$nextBirthday", "$today"]}, 24*60*60*1000 /* milliseconds in a day */ ]}, _id:0 }}; var s1 = {$sort:{daysTillNextBirthday:1}}; db.birthdays.aggregate([ a1, a2, p1, s1 ]); 

You can set "today" to any date (leap year or not) and see that the calculation is now always correct and much easier.

Answer 2

With the obvious that birth dates are the date of birth in the past, but we want to look in the future? Yes, a good trap .

But we can do with some projection into aggregation for one solution method.

First, configure the necessary variables a little:

 var start_time = new Date(), end_time = new Date(); end_time.setDate(end_time.getDate() + 30 ); var monthRange = [ start_time.getMonth() + 1, end_time.getMonth() + 1 ]; var start_string = start_time.getFullYear().toString() + ("0" + (start_time.getMonth()+1)).slice(-2) + ("0" + (start_time.getDate()-1)).slice(-2); var end_string = end_time.getFullYear().toString() + ("0" + (end_time.getMonth()+1)).slice(-2) + ("0" + (end_time.getDate()-1)).slice(-2); var start_year = start_time.getFullYear(); var end_year = end_time.getFullYear(); 

Then run this through aggregate :

 db.users.aggregate([ {"$project": { "name": 1, "birthdate": 1, "matchYear": {"$concat":[ // Substituting the year into the current year {"$substr":[{"$cond":[ {"$eq": [{"$month": "$birthdate"}, monthRange[0]]}, start_year, // Being careful to see if we moved into the next year {"$cond":[ {"$lt": monthRange}, start_year, end_year ]} ]},0,4]}, {"$cond":[ {"$lt":[10, {"$month": "$birthdate"}]}, {"$substr":[{"$month": "$birthdate"},0,2]}, {"$concat":["0",{"$substr":[{"$month": "$birthdate"},0,2]}]} ]}, {"$cond":[ {"$lt":[10, {"$dayOfMonth": "$birthdate"}]}, {"$substr":[{"$dayOfMonth": "$birthdate"},0,2]}, {"$concat":["0",{"$substr":[{"$dayOfMonth": "$birthdate"},0,2]}]} ]} ]} }}, // Small optimize for the match stage {"sort": { "matchYear": 1}}, // And match on the range now that it lexical {"$match": { "matchYear": {"$gte": start_string, "$lte": end_string } }} 

])

I suppose the same applies to mapReduce if your mind works better. But the results will only lead to true or false no matter how you shook it. But you probably just need a sump, and the syntax will be a little clearer:

 var mapFunction = function () { var mDate = new Date( this.birthdate.valueOf() ); if ( mDate.getMonth() + 1 < monthRange[0] ) { mDate.setFullYear(start_year); } else if ( monthRange[0] < monthRange[1] ) { mDate.setFullYear(start_year); } else { mDate.setFullYear(end_year); } var matched = (mDate >= start_time && mDate <= end_time); var result = { name: this.name, birthdate: this.birthdate, matchDate: mDate, matched: matched }; emit( this._id, result ); }; 

Then you pass this to mapReduce by typing all the variables that were defined earlier:

 db.users.mapReduce( mapFunction, function(){}, // reducer is not called { out: { inline: 1 }, scope: { start_year: start_year, end_year: end_year, start_time: start_time, end_time: end_time, monthRange: monthRange } } 

)

But really, at least save the “Month of Birth” in a real field as part of your user record. Because then you can narrow the matches and not process your entire collection. Just add the extra $ match at the beginning of the pipeline:

 {"$match": "birthMonth": {"$in": monthRange }} 

With the field present in the document, which will save disk in the future.

Final note

Another form that should work just throws raw JavaScript into the search. This can be done as a shortcut where you do not provide any additional query conditions. But for confusing documentation is under $ where is the operator and, in fact, is the same as passing in JavaScript to $ where .

However, any attempt at this would simply not lead to a result. Hence other methods. Not sure if there was a good reason or if it was a mistake.

In any case, all tests, except the rollover test last year, were made on these documents. One result should not appear where the start date was from "2014-03-03".

 { "name" : "bill", "birthdate" : ISODate("1973-03-22T00:00:00Z") } { "name" : "fred", "birthdate" : ISODate("1974-04-17T00:00:00Z") } { "name" : "mary", "birthdate" : ISODate("1961-04-01T00:00:00Z") } { "name" : "wilma", "birthdate" : ISODate("1971-03-17T00:00:00Z") } 

Answer 3

The solution would be to pass the find operation function to Mongo. See inline comments:

 // call find db.users.find(function () { // convert BSON to Date object var bDate = new Date(this.birthday * 1000) // get the present moment , minDate = new Date() // add 30 days from this moment (days, hours, minutes, seconds, ms) , maxDate = new Date(minDate.getTime() + 30 * 24 * 60 * 60 * 1000); // modify the year of the birthday Date object bDate.setFullYear(minDate.getFullYear()); // return a boolean value return (bDate > minDate && bDate < maxDate); }); 

Answer 4

I think the most elegant and usually the most effective solution is to use an aggregation structure . To get birthdays, we need to drop all date information except $ month and $ DayOfMonth . We create new compound fields using these values, turning on them, and we leave!

This javascript can be executed from the mongo console and works with a collection called users with a birthday field. It returns a list of user IDs, grouped by day.

 var next30days = []; var today = Date.now(); var oneday = (1000*60*60*24); var in30days = Date.now() + (oneday*30); // make an array of all the month/day combos for the next 30 days for (var i=today;i<in30days;i=i+oneday) { var thisday = new Date(i); next30days.push({ "m": thisday.getMonth()+1, "d": thisday.getDate() }); } var agg = db.users.aggregate([ { '$project': { "m": {"$month": "$birthday"}, "d": {"$dayOfMonth": "$birthday"} } }, { "$match": { "$or": next30days } }, { "$group": { "_id": { "month": "$m", "day": "$d", }, "userids": {"$push":"$_id"} } } ]); printjson(agg);