Filter null value for SharePoint column using REST

Question

I am trying to fetch the data based on my "EmpData" list. I am able to get the exact rows for the below Rest API query till I don't include "Null" filtration in my below rest

var oDataUrl = siteUrl + "/_api/web/lists/GetByTitle('EmpData')/items?$top=50000&$Select=ColumnA,ColumnB,ColumnC,ColumnD,ID&$filter=((Country eq 'USA')or(EMPScore eq '500')or(Department eq 'HR')or(TechSkill eq 'CRM')or(State eq 'LA'))and(years eq '27')

But I don't get any result when I add the Column "Result ** as (Result eq 'null') to filter null values** with my below Rest API

var oDataUrl = siteUrl + "/_api/web/lists/GetByTitle('EmpData')/items?$top=6000&$Select=ColumnA,ColumnB,ColumnC,ColumnD,ID&$filter=((Country eq 'USA')or(EMPScore eq '500')or(Department eq 'HR')or(TechSkill eq 'CRM')or(State eq 'LA'))and(years eq '27')and(Result eq 'null')"

Can any one please do let me what's wrong in above API and what is the exact way of approach to fetch null values fro the column Result (Result eq 'null') using Rest

Answer 1

I've read this article you may want to try what he did

Checked null with dateTime in REST API SharePoint

$.ajax({
type: "POST", 
headers: { 
        "accept": "application/json;odata=verbose",
        "content-type": "application/json;odata=verbose",
        "X-RequestDigest": $("#__REQUESTDIGEST").val()
    }, 
url: 'https://kaucher.sharepoint.com/_api/web/Lists/GetByTitle(\'Test List\')/GetItems(query=@v1)?@v1={"ViewXml":"<View><Query><Where><IsNull><FieldRef Name=\'DateCompleted\' /></IsNull></Where></Query></View>"}', 
success: function(data){console.log(data); },
failure: function(data){console.log(data); } 

});

Try using CAML filtering instead

Answer 2

The following code will bring back items if the Result has a space (empty). If nothing is stored in the Result then it is null, and ODATA for SharePoint REST does not support the filtering based on null.

Try below: Result eq ''

var oDataUrl = siteUrl + "/_api/web/lists/GetByTitle('EmpData')/items?$top=6000&$Select=ColumnA,ColumnB,ColumnC,ColumnD,ID&$filter=((Country eq 'USA')or(EMPScore eq '500')or(Department eq 'HR')or(TechSkill eq 'CRM')or(State eq 'LA'))and(years eq '27')and(Result eq '')"

Use Caml Query on Javascript Object model as a work around.

Answer 3

As of today (december 2021), in SharePoint Online, we can do:

"/_api/web/lists/GetByTitle('EmpData')/items?$top=10&$Select=ColumnA,ID&$filter=Result eq null"

null must be in lower case.