Microsoft Graph API - Unable to filter with endswith

Question

To filter Microsoft Graph API responses, the docs suggest to use the filter query option.

Here I've used startswith and endswith string functions on the '/me/messages' endpoint.

query = {
    '$filter': "
      startswith(from/emailAddress/address, 'abcd')
      or endswith(from/emailAddress/address, 'gmail.com')"
  }

Only using startswith returns the expected response. On adding the endswith function to the filter query, the response received is an error.

{
  "error": {
    "code": "ErrorInvalidUrlQueryFilter",
    "message": "The query filter contains one or more invalid nodes.",
    "innerError": {
      "request-id": "0d12e1f6-6105-4826-9656-8613f8c167ed",
      "date": "2019-03-14T11:05:56"
    }
  }
}

Finale

It seems as at today, this doesn't work and has been an open issue for over a year. See the issue here: github.com/microsoftgraph/microsoft-graph-docs/issues/4331 – Gustatory

Hey @friek108, yup that issue was raised by myself a couple years ago :) – Finale

Hi Tejas, endswith is now working on mail and userPrincipalName. See my answer at #64983820 for a Graph Explorer and Graph.Net SDK example. – Alaska

Answer 1

To use $filter with endsWith you need to:

1. Add $count=true to the query parameters
2. Add ConsistencyLevel: eventual request header

Example : 'https://graph.microsoft.com/v1.0/users?$count=true&ConsistencyLevel=eventual&$filter=endswith(mail,'@hotmail.com')'

**It works for mail and userPrincipalName but not for displayName

Tarrasa

Answer 2

Microsoft Graph has a tricky set of API endpoints. "endswith" works only with the "mail" field, not others. "startswith" works with almost all the fields. "contains" doesn't work at all.

Alternatively, you can use $search, which might some extra features

Receive

Answer 3

Copied this exact same query (with the ConsistencyLevel either in the query params or headers params and wasn't able to make it work. The app that I used to request authorisation token have Users.ReadWrite.All permission AND the User.Read.All.

In Postman :

GET https://graph.microsoft.com/v1.0/users?$count=true&ConsistencyLevel=eventual&$filter=endswith(mail,'@hotmail.com')

{
    "error": {
        "code": "Request_UnsupportedQuery",
        "message": "Unsupported property filter clause operator 'SuffixMatch'.",
        "innerError": {
            "date": "2022-07-27T13:22:38",
            "request-id": "3f509514-4e77-4eb1-8557-0fe102d502f5",
            "client-request-id": "3f509514-4e77-4eb1-8557-0fe102d502f5"
        }
    }
}

----- EDIT ------

---

Just found my answer here : https://learn.microsoft.com/en-us/graph/query-parameters

Use of $count is not supported in Azure AD B2C tenants.

Goodrum