Search API exclude null Birthday records in SharePoint Online

Question

I am building a birthday webpart. I am using search api to get the user details as below:

var birthday = 'RefinableDate00';

    fetch(this.props.SPUrl + "/_api/search/query?querytext=%27*%27&selectproperties=%27WorkEmail,LastName,FirstName,RefinableDate00,RefinableDate01,Date00,BirthdateOWSDATE%27&sourceid=%27B09A7990-05EA-4AF9-81EF-EDFAB16C4E31%27&rowlimit=500&sortlist=%27" + birthday + ":ascending%27",
      {
        method: 'GET',
        headers: {
          "Accept": "application/json; odata=verbose"
        }
      })
      .then((response) => {
        return response.json();
      })
      .then((data) => {

        if (data.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results.length > 0) {
          console.log(data.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results);
          var items = [];
          data.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results.map((item) => {
            if (item.Cells.results[5].Value) {
              items.push({
                "FirstName": item.Cells.results[4].Value ? item.Cells.results[4].Value : "",
                "LastName": item.Cells.results[3].Value ? item.Cells.results[3].Value : "",
                "WorkPhone": item.Cells.results[7].Value ? item.Cells.results[7].Value : "",
                "WorkEmail": item.Cells.results[2].Value,
                //"Department":item.Cells.results[5].Value?item.Cells.results[5].Value:"",                                                  
                "OfficeNumber": item.Cells.results[2].Value ? item.Cells.results[2].Value : "",
                "JobTitl": item.Cells.results[8].Value ? item.Cells.results[8].Value : "",
                "PictureURL": item.Cells.results[9].Value ? item.Cells.results[9].Value : "",
                "BirthDate": item.Cells.results[5].Value,
                "AnniversaryDate": item.Cells.results[6].Value
              });
            }
          });
          console.log(items);
          var today = new Date();
          var enddate = new Date(new Date().setDate(today.getDate() + 30));
          var filteredUsers = items.filter((item) => {
            if (item.BirthDate) {
              var birthDate = new Date(item.BirthDate);
              if (item.FirstName == "" || item.FirstName == "null" || item.FirstName == "undefined" || item.FirstName == undefined || item.FirstName == null) {
                return false; //hide result if firtstname is empty
              }
              var nextMonth = new Date((today.getMonth() + 2) + "/" + today.getDate() + "/" + today.getFullYear());
              //return (anniversaryDate >= today && anniversaryDate < nextMonth?item:null)
              //return ((birthDate.getDate() >= today.getDate() && birthDate.getMonth() >= today.getMonth()) && (birthDate.getMonth() < nextMonth.getMonth()));
              return (new Date(birthDate.getMonth() + 1 + "-" + birthDate.getDate() + "-" + today.getFullYear()).getTime() >= today.getTime()) && (new Date(birthDate.getMonth() + 1 + "-" + birthDate.getDate() + "-" + today.getFullYear()).getTime() <= enddate.getTime());

            }
          });

It works fine but the issue is it takes time to load details. Is there a way we can directly check null birthdays in api call, so I can have only those results that have birthdays and the load can be reduced?

I have checked below:

• check date range from 1900-01-01 to today
• check null in rest api

Update - I have checked the datatype of the crawled property and it is date time. Also I have added links above which I already tried which contains comparing date time queries. But it didn't work.

Answer 1

The Query API only supports the 'greater than' (>) and 'less than' (<) operators on certain data types. Luckily, datetime is one of the supported ones, so as long as your BirthDate managed search property is defined as a datetime, you can set your querytext to 'YourBirthDateSearchProperty>1900-01-01'. While the REST API does not support a true check for "empty" or "null", checking for a minimum value should achieve the same. So I think your fetch should look something like:

fetch(this.props.SPUrl + "/_api/search/query?querytext=%27RefinableDate00>1900-01-01%27&selectproperties=%27WorkEmail,LastName,FirstName,RefinableDate00,RefinableDate01,Date00,BirthdateOWSDATE%27&sourceid=%27B09A7990-05EA-4AF9-81EF-EDFAB16C4E31%27&rowlimit=500&sortlist=%27" + birthday + ":ascending%27",