SharePoint Online Rest API - Filter by boolean (yes/no)

Question

I am trying to simply apply a Boolean filter to a Rest API call that is based on a Yes/No

It has been documented in many locations that this functionality is broken for many years. I am unable to find any firm indication that it is indeed broken from Microsoft.

Has anyone been able to successfully achieve this in SharePoint online. I understand it is simple enough to filter the local collection, but this just feels sub-optimal in so many ways.

Examples:-

• items?$filter=YesNoColumn eq 1 - returns nothing
• items?$filter=YesNoColumn ne 0 - return everything
• items?$filter=YesNoColumn gt 0 - return nothing

Answer 1

Assuming the column is a Yes/No column, and not a column with "Yes" and "No" text, the following works for me in SharePoint Online:

Returns false items:

_api/web/lists/getbytitle('TestList')/items?$select=Title,YesNoColumn&$filter=YesNoColumn eq 0

Returns true items:

_api/web/lists/getbytitle('TestList')/items?$select=Title,YesNoColumn&$filter=YesNoColumn eq 1

Neither return items with a blank value.

Answer 2

Using the following pattern works in our implementation:

&$filter=(Category eq 'Work Description')and (Archived eq false)

Answer 3

_api/web/lists/getbytitle('TestList')/items?$select=Title,YesNoColumn&$filter=YesNoColumn eq 1

This is the correct answer.

Answer 4

Mike Smith - MCT gave the correct answer, I'll just add this:

_api/web/lists/getbytitle('TestList')/items?$select=Title,YesNoColumn&$filter=YesNoColumn eq null 

you will get the blank data.

So, for all negative values as well as blank (or null) values you can use:

_api/web/lists/getbytitle('TestList')/items?$select=Title,YesNoColumn&$filter=YesNoColumn eq null or YesNoColumn eq 0

Answer 5

We have to use expand property in REST API:

/_api/web/lists/getbytitle('TestList')/items?$select=IsActive&$expand=isActive&$filter=IsActive eq 1