Using Agreegate to get the data of all wards that their birthday will be in the next 7 days

Hey friends, Please i need help, I have a table called ward wit wards details such as name, date of birth and so on. i want to get the data of all the wards that their birthday will be in the next 7days using aggregate. Kindly help. i am stocked

To explain better, i want to get all wards data using where the date_of_birth column will come up in the next 7days.

Thanks

Hello @Gbade_Francis, Welcome to MongoDB Community Forum,

You can use conditional operators with the and condition, here you have to input your current date for greater than and equal to condition and current date +7 days date in less than condition,

"date_of_birth": { 
  "$gte": <current date>, 
  "$lt": <current date + 7 days> 
}

Thanks for the response. but current date?? current date should be like recent date now? it did not work with the birthday date.

let getbirthday= await model.aggregate([

      [
        {
          '$match': {
            'isDeleted': false,
            "dateOfBirth": {
              "$gte": "$dateOfBirth",
              "$lt": "$dateOfBirth"+7
            }


          }
        }, {
          '$project': {
            '_id': '$_id',
            'dateOfBirth': {
              '$dateToString': {
                'format': '%Y-%m-%d',
                'date': '$dateOfBirth'
              }
            },
            'firstName': '$firstName',
            'middleName': '$middleName',
            'lastName': '$lastName',
            'sex': '$sex'
          }
        }
      ]
    ])

Please help me to check.

This is the data from my ward table:

[

{

    "sex": "female",

    "dateJoined": "2022-02-13T18:58:37.083Z",

    "roleName": null,

    "isDeleted": false,

    "isRestricted": false,

    "levelHistory": [],

    "_id": "6209599e9ee1d60016315ce0",

    "admissionNumber": "Dia001",

    "firstName": "kehinde",

    "middleName": "",

    "lastName": "fatokun",

    "dateOfBirth": "2006-02-23T19:18:12.000Z",

    "guardianContact": "08034356783",

    "currentLevel": "620956889ee1d60016315bfc",

    "schoolId": "620956889ee1d60016315bf8",

    "__v": 0

},

{

    "sex": "male",

    "dateJoined": "2022-02-13T18:58:37.083Z",

    "roleName": null,

    "isDeleted": false,

    "isRestricted": false,

    "levelHistory": [],

    "_id": "620959f09ee1d60016315ced",

    "admissionNumber": "Dia002",

    "firstName": "funsho ",

    "middleName": "",

    "lastName": "adeoye",

    "dateOfBirth": "2014-02-20T19:19:30.000Z",

    "guardianContact": "08035678893",

    "currentLevel": "620956889ee1d60016315bfa",

    "schoolId": "620956889ee1d60016315bf8",

    "__v": 0

},

{

    "sex": "female",

    "dateJoined": "2022-02-13T18:58:37.083Z",

    "roleName": null,

    "isDeleted": false,

    "isRestricted": false,

    "levelHistory": [],

    "_id": "62095b199ee1d60016315d0c",

    "admissionNumber": "dia003",

    "firstName": "tolani",

    "middleName": "",

    "lastName": "makinde",

    "dateOfBirth": "2015-02-11T19:24:34.000Z",

    "guardianContact": "08033567898",

    "currentLevel": "620956889ee1d60016315bfa",

    "schoolId": "620956889ee1d60016315bf8",

    "__v": 0

},

{

    "sex": "male",

    "dateJoined": "2022-02-13T18:58:37.083Z",

    "roleName": null,

    "isDeleted": true,

    "isRestricted": false,

    "levelHistory": [],

    "_id": "62095ba19ee1d60016315d25",

    "admissionNumber": "Dia004",

    "firstName": "kayode ",

    "middleName": "",

    "lastName": "adeolu",

    "dateOfBirth": "2010-02-18T19:26:53.000Z",

    "guardianContact": "08033679866",

    "currentLevel": "620956889ee1d60016315bfa",

    "schoolId": "620956889ee1d60016315bf8",

    "__v": 0

},

{

    "sex": "female",

    "dateJoined": "2022-02-13T18:58:37.083Z",

    "roleName": null,

    "isDeleted": false,

    "isRestricted": false,

    "levelHistory": [],

    "_id": "62095bec9ee1d60016315d31",

    "admissionNumber": "dia005",

    "firstName": "tina",

    "middleName": "",

    "lastName": "phillips",

    "dateOfBirth": "2010-02-18T19:28:04.000Z",

    "guardianContact": "08035678764",

    "currentLevel": "620956889ee1d60016315bfa",

    "schoolId": "620956889ee1d60016315bf8",

    "__v": 0

},

{

    "sex": "female",

    "dateJoined": "2022-02-13T18:58:37.083Z",

    "roleName": null,

    "isDeleted": false,

    "isRestricted": false,

    "levelHistory": [],

    "_id": "62095cda9ee1d60016315d46",

    "admissionNumber": "dia006",

    "firstName": "kehinde",

    "middleName": "",

    "lastName": "faye",

    "dateOfBirth": "2006-02-16T00:00:00.000Z",

    "guardianContact": "08143469839",

    "currentLevel": "620956889ee1d60016315bfa",

    "schoolId": "620956889ee1d60016315bf8",

    "__v": 0

},

{

    "sex": "male",

    "dateJoined": "2022-02-14T07:13:58.502Z",

    "roleName": null,

    "isDeleted": false,

    "isRestricted": true,

    "levelHistory": [],

    "_id": "620a02689e6dfb00162eaa98",

    "admissionNumber": "DI001",

    "firstName": "gold",

    "middleName": "",

    "lastName": "silver",

    "dateOfBirth": "2022-02-01T07:18:45.000Z",

    "guardianContact": "07037617125",

    "currentLevel": "620956889ee1d60016315bfb",

    "schoolId": "620956889ee1d60016315bf8",

    "__v": 0,

    "imageUrl": "http://res.cloudinary.com/asm-web/image/upload/v1646499727/zhes72svpkdwylmzl638.png"

},

]

Thanks

Means a specific date period, right?

so current date means now date, for ex in JS,

let fromDate = new Date();
let toDate = new Date();
toDate.setDate(toDate.getDate() + 7);
toDate = new Date(toDate);

The query would be,

{
  "$match": {
    "dateOfBirth": {
      "$gte": fromDate,
      "$lt": toDate
    }
  }
}

Boos, You are the best. It actually work, but i think there is something needed to be done again.

because i run it, remember we have date of birth which is
2009-10-13T00:00:00.000+00:00, Meaning that we have
year i was born is 2009
Month is 10
and day is 13

Now, the system should calculate in the sense that if it is 2009, 10, 13, how will the system understand that the birthday is comming up on 2022-10-13. because not untill i alter the birthday and change it to todays date before i can get the data. if not, its given empty array.
I hope you understand boss

    let fromDate = new Date();
    console.log(fromDate)
    let toDate = new Date();
    console.log(toDate)
    toDate.setDate(toDate.getDate() + 7);
    toDate = new Date(toDate);
    console.log(toDate)
    let getbirthday= await model.aggregate([
      [
        {
          '$match': {
            'isDeleted': false,
            "dateOfBirth": {
              "$gte": fromDate,
              "$lt": toDate
            }
          }

]

then i got this
[ ]
GET /api/v1/birthday/get-wardBirthday - - ms - -
2022-07-07T11:16:34.972Z
2022-07-07T11:16:34.973Z
2022-07-14T11:16:34.973Z
[ ]

instead of it to pull the wards birthday that belongs to that range.

remember my date of birth i inserted last year can be 1988-03-13
and i want the system to remember my birthday this year. and it should remind me 7days before my birthday… (This is exactly what it should work boss).

Thanks so much

Okay, I misunderstood,

Might be there are other solutions as well but, I know the way is $expr expression with aggregation operators, by using $month and $dayOfMonth operators,

let fromDate = new Date();
let toDate = new Date();
toDate.setDate(toDate.getDate() + 7);
toDate = new Date(toDate);

let getbirthday= await model.aggregate([
  {
    "$match": {
      "$expr": {
        "$and": [
          {
            "$gte": [
              { "$dayOfMonth": "$dateOfBirth" },
              { "$dayOfMonth": fromDate }
            ]
          },
          {
            "$gte": [
              { "$month": "$dateOfBirth" },
              { "$month": fromDate }
            ]
          },
          {
            "$lte": [
              { "$dayOfMonth": "$dateOfBirth" },
              { "$dayOfMonth": toDate }
            ]
          },
          {
            "$lte": [
              { "$month": "$dateOfBirth" },
              { "$month": toDate }
            ]
          }
        ]
      }
    }
  }
]);

You can simplify more this by passing the month number and date number instead of the whole date,

let fromMonth = 7;
let toMonth = 7;
let fromDay = 7;
let toDay = 14;

let getbirthday= await model.aggregate([
  {
    "$match": {
      "$expr": {
        "$and": [
          {
            "$gte": [
              { "$dayOfMonth": "$dateOfBirth" },
              fromDay
            ]
          },
          {
            "$gte": [
              { "$month": "$dateOfBirth" },
              fromMonth
            ]
          },
          {
            "$lte": [
              { "$dayOfMonth": "$dateOfBirth" },
              toDay
            ]
          },
          {
            "$lte": [
              { "$month": "$dateOfBirth" },
              toMonth
            ]
          }
        ]
      }
    }
  }
]);

Wow!!!. I am grateful. it work exactly the way i want it. Thanks so much and thanks so much. CHeers.
Please i will like to be your friend, you can send me your whatsap number on +2348132185887 or in my email. gbade.francis@gmail.com

I am not too sure that this will work for boundary cases.

Assume you want birthdays from June 30th to July 6th.

So your fromDay=30, fromMonth=6, toDay=6, toMonth=7.

There is no way you could find a day that $gte:fromDay and $lte:toDay. The aggregation works because fromMonth == toMonth.

You need something a little bit more complex.

Something like:

if fromMonth == toMonth
  return monthOfBirth == fromMonth && fromDay <= dayOfBirth && dayOfBirth <= toDay
else if monthOfBirth == fromMonth
  return fromDay <= dayOfBirth
else if monthOfBirth == toMonth
  return dayOfBirth <= toDay

Which would look like:

/* set stage to set monthOfBirth and dayOfBirth using $month and $dayOfMonth */
"$or" : [
  { "$and" : [
     { "$eq" : [ fromMonth , toMonth ] } ,
     { "$eq" : [ fromMonth , "$monthOfBirth" ] } ,
     { "$lte" : [ fromDay , "$dayOfBirth" ] } ,
     { "$lte" : [ "$dayOfBirth" , toDay ] } ,
  ] } ,
  { "$and" : [
     { "$eq" : [ fromMonth , "$monthOfBirth" ] } ,
     { "$lte" : [ fromDay , "$dayOfBirth" ] }
  ] } ,
  { "$and" : [
     { "$eq" : [ toMonth , "$monthOfBirth" ] } ,
     { "$lte" : [ "$dayOfBirth" , toDay ] } ,
  ] }
]

I am still unsure if the above handle the edge cases where fromMonth=12 and toMonth=1

1 Like

You are right,

There is another solution, I am not sure about the performance but, what if we fix the year and match the condition, and reconstruct the date by $dateFromParts operator,

let fixedYear = 2000;

let fromDate = new Date();
fromDate.setFullYear(fixedYear);
fromDate = new Date(fromDate);

let toDate = new Date();
toDate.setFullYear(fixedYear);
toDate.setDate(toDate.getDate() + 7);
toDate = new Date(toDate);

let getbirthday= await model.aggregate([
  {
    "$match": {
      "$expr": {
        "$and": [
          {
            "$gte": [
              {
                "$dateFromParts": {
                  "year": fixedYear,
                  "month": { "$month": "$dateOfBirth" },
                  "day": { "$dayOfMonth": "$dateOfBirth" }
                }
              },
              fromDate
            ]
          },
          {
            "$lt": [
              {
                "$dateFromParts": {
                  "year": fixedYear,
                  "month": { "$month": "$dateOfBirth" },
                  "day": { "$dayOfMonth": "$dateOfBirth" }
                }
              },
              toDate
            ]
          }
        ]
      }
    }
  }
])

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.