Sharepoint REST API filter empty LookUp field

Question

I'd like to know how I can filter ListItems in SharePoint rest API (2013) on empty lookup. On my list items, there is a lookup (single value, not required) and i'd like to get all items where lookup is empty (blank).

_api/web/lists/getbytitle('MyList')/items?&select=Id&$expand=MyLookUp/Id&$filter=??

Does someone has a clue ? Thx

Answer 1

Hi try giving null for value for lookup column value

_api/web/lists/getbytitle('MyList')/items?&select=Id&$expand=MyLookUp/Id&$filter=MyLookUp/Id eq null

Answer 2

REST API in SharePoint 2013 does Not support null values for filtering on list item queries.

However you can combine CAML query with REST API to get the required data.

Please refer below code : (Note : please change your query/URL):

 function _rest_Post(rquest) {
        return $.ajax({
            method: rquest.method,
            url: rquest.url,
            contentType: rquest.contentType,
            headers: rquest.header,
            data: JSON.stringify(rquest.body),
        });

    }

function GetByCaml(serviceParams) {
    var req = {
        method: 'POST',
        url: url + "/_api/web/lists/getByTitle('" + serviceParams.create.lName + "')/getitems",
        header: {
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "accept": "application/json;odata=verbose",
            "content-type": "application/json;odata=verbose"
        },
        body: serviceParams.create.body,
        contentType: "application/json;odata=verbose",

    };
    return _rest_Post(req);

}

function getData(){
    var queryViewXml = "<View><Query><Where><IsNull><FieldRef Name='Project_x0020_Manager'/></IsNull></Where></Query><ViewFields><FieldRef Name='Title'/></ViewFields></View>";
    var params = {};
    params.create= {};
    params.create.lName = "MyList";
    params.create.filter = "";
    params.create.body = {
        'query':{ 
            '__metadata': { 'type': 'SP.CamlQuery' },
            'ViewXml': queryViewXml
        }
    }
    return GetByCaml(params);
};


getData().then(function(data){
    //success handler
}, function(error){
    //failure handler
});

Answer 3

Try this

_spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + drinkTypeListName + "')/items?$select=Title,Drink/Title,Drink/Id&$expand=Drink&$filter=Drink/Title eq '" + selectedDrink + "'";

Ref: Cascading drop down in SharePoint using REST API.