Get next birthday in MongoDB

Question

I have a MongoDB collection with events in time with the date. Something like this:

{
    "year"  : 1985,
    "month" : 4,
    "day"   : 16,
    "name"  : "J"
},
{
    "year"  : 1985,
    "month" : 9,
    "day"   : 16,
    "name"  : "E"
},
{
    "year"  : 1950,
    "month" : 11,
    "day"   : 11,
    "name"  : "M"
},
{
    "year"  : 1947,
    "month" : 5,
    "day"   : 6,
    "name"  : "D"
}

I want a MongoDB query that returns next birthday starting today. For instance, if today was September 25th, next birthday in my collection would be November 11th, corresponding to the entry with name equal to "M".

I would accept adding fields of type Date, removing my current year, month and day fields, or whatever in order to have an efficient way to achieve my goal.

I'm using MongoDB v3.4.

I assume you want to wrap right? So in your case if today was December 12, you'd want to return the entry by the name "J" because it's the next month? — Michael Platt, Sep 25 '17 at 14:57

score 0 · Answer 1 · edited Sep 25 '17 at 17:21

Ok so best I can tell you essentially need two queries to make this work. I'm not a Mongo expert but this will get you what you need for now/put you in the right direction for research. The two queries are as follows:

db.YOUR_COLLECTION.find({ $where: "this.year = input_year & this.month > input_month & this.day > input_day" }).sort({ month: 1, day: 1 }) 
// Gets you back all the entries that have a month greater than the input month.

db.YOUR_COLLECTION.find({ $where: "this.year > input_year }).sort({ year: 1, month: 1, day: 1 }) 
// Gets you back all the entries that start at next year and go forward.  This could probably be optimized a bit at some point.

Note that in the first case I'm ordering them on month first, then day in ascending order and the second orders by year as an extra parameter. I'm also returning all the rows. In practice, you may want to do something like a limit to one result. Either way, you'll need to run the first query to handle a case where the next date isn't wrapped into the next year. If that returns no results, then you'll want the second query that will start in the following year and search forward. If this doesn't fully answer your question let me know.

score 0 · Accepted Answer · answered Oct 07 '17 at 11:46

Finally I got an answer following this answer. It uses the Aggregation Pipeline.

First of all, I decided to use Date/ISODate type to store birthdays, instead of individual year/month/day fields. So my test collection would have these entries:

{
    "birthday" : ISODate("1985-04-16T10:00:00.000Z"),
    "name" : "J"
}

{
    "birthday" : ISODate("1985-09-16T11:00:00.000Z"),
    "name" : "E"
}

{
    "birthday" : ISODate("1950-11-11T11:00:00.000Z"),
    "name" : "M"
}

{
    "birthday" : ISODate("1947-05-06T10:00:00.000Z"),
    "name" : "D"
}

Then, I constructed a few structures to use in aggregation pipeline:

In pDayOfYear get sequential year of the year of the birthday and today, using $dayOfYear operator.
In pLeapYear subtract 1 to dayofYear field if birthday was in a leap year and calculate the difference between each birthday and today.
In pPast add 365 to past birthdays to have positiveDiff field.
In pSort sort results by positiveDiff field in descending order, so we can can have a list of next birthdays
In pFirst get only the first result.

Query would look like:

pDayOfYear = {
    "$project": {
        "birthday": 1,
        "todayDayOfYear": {"$dayOfYear": new ISODate()},
        "leap": {"$or": [
            {"$eq": [0, {"$mod": [{"$year": "$birthday"}, 400]}]},
            {"$and": [
                {"$eq": [0, {"$mod": [{"$year": "$birthday"}, 4]}]},
                {"$ne": [0, {"$mod": [{"$year": "$birthday"}, 100]}]}
            ]}
        ]},
        "dayOfYear": {"$dayOfYear": "$birthday"}
    }
}

pLeapYear = {
    "$project": {
        "birthday": 1,
        "todayDayOfYear": 1,
        "dayOfYear": {
            "$subtract": [
                "$dayOfYear",
                {
                    "$cond": [
                        {"$and":
                             ["$leap",
                              {"$gt": ["$dayOfYear", 59]}
                              ]},
                        1,
                        0]
                }
            ]},
         "diff": { "$subtract": [ "$dayOfYear", "$todayDayOfYear"] }
    }
}


pPast = {
    "$project": {
        "diff": 1,
        "birthday": 1,
        "positiveDiff": {
            "$cond": { 
                "if": { "$lt": ["$diff", 0 ]},
                    "then": { "$add": ["$diff", 365] },
                "else":  "$diff"
                },
            }
    }
}

pSort = {
        "$sort": {
            "positiveDiff": 1
        }
}


pFirst = {
    "$group": {
        "_id": "first_birthday",
        "first": {
            "$first": "$$ROOT"
        }
    }
}

db.getCollection('tests').aggregate(pDayOfYear, pLeapYear, pPast, pSort, pFirst);

So I would get next birthday ID and I could query the field by ID. One can get next birthday of an specific date changing todayDayOfYearfield by any date.

I'm open to any change, specially readability and efficiency improvements.

Get next birthday in MongoDB

2 Answers2