Filter SharePoint list by multichoice column for exact match

Question

If I run a query

.../_api/web/lists(<GUID>)/items?$filter=Field eq 'Option1'

then it returns all items containing Option1 in multi-choice Field including:

• [Option1],
• [Option1, Option2],
• [Option1, Option3, Option4]

What query should I run to get all items which have only Option1 in multi-choice Field: [Option1]?

Answer 1

The short answer is to construct the query so that it excludes all the other possible values. So lets say you only have 4 options, you could do

/_api/web/lists(<GUID>)/items?$filter=Field eq 'Option1' and Field ne 'Option2' and Field ne 'Option3' and Field ne 'Option4'

That will certainly get the exact results you want, but clearly if your choice field has a lot of choices, that might be a pain to write.

If you retrieve the field from the list first you could programmatically generate the query by doing something like this:

// maybe you got this from a UI control or something
const filterSelection = 'Option1';

let negativeFilters = [];

field.Choices.forEach(choice => {
    if (choice !== filterSelection) {
        negativeFilters.push(`Field ne '${choice}'`);
    }
});

const url = `/_api/web/lists(<GUID>)/items?$filter=Field eq '${filterSelection} and ${negativeFilters.join(' and ')}'`;

But honestly, the first thing that came to my mind was to abandon the idea of getting the exact results you want by constructing the perfect oData query, and just know that you are going to get those other results and filter them out afterwards:

// make this query
/_api/web/lists(<GUID>)/items?$filter=Field eq 'Option1'


// then when you get the results

const realResults = response.value.filter(item => {
    return item.Field.length === 1;
});