azuresharepointmicrosoft-graph-apiodata

Filter by filename contains in SharePoint Graph API


I've been searching endlessly for a way to query SharePoint list items via the Microsoft Graph API while filtering by filename. Currently, I use startswith(fields/FileLeafRef, 'query'), but I would also like to filter using endswith() or contains(). However, these don't seem to work for my queries.

I make requests like the following:

[
    {
        "id": "123",
        "method": "GET",
        "url": "/sites/{site}/lists/{list1}/items?$top=10
                &expand=fields(select=Dokumenttyp,Author,Editor,Customer_ID,Project_ID,LinkFilename)
                &$select=id,name,webUrl,createdDateTime,lastModifiedDateTime,fields
                &$filter=
                    fields/ContentType ne 'Folder' and
                    fields/Customer_ID eq '162373' and
                    startswith(fields/FileLeafRef, 'query')",
        "headers": {
            "Prefer": "HonorNonIndexedQueriesWarningMayFailRandomly"
        }
    },
    {
        "id": "456",
        "method": "GET",
        "url": "/sites/{site}/lists/{list2}/items?$top=10
                &expand=fields(select=Dokumenttyp,Author,Editor,Customer_ID,Project_ID,LinkFilename)
                &$select=id,name,webUrl,createdDateTime,lastModifiedDateTime,fields
                &$filter=
                    fields/ContentType ne 'Folder' and
                    fields/Customer_ID eq '162373' and
                    startswith(fields/FileLeafRef, 'query')",
        "headers": {
            "Prefer": "HonorNonIndexedQueriesWarningMayFailRandomly"
        }
    },
    {
        "id": "789",
        "method": "GET",
        "url": "/sites/{site}/lists/{list3}/items?$top=10
                &expand=fields(select=Dokumenttyp,Author,Editor,Customer_ID,Project_ID,LinkFilename)
                &$select=id,name,webUrl,createdDateTime,lastModifiedDateTime,fields
                &$filter=
                    fields/ContentType ne 'Folder' and
                    fields/Customer_ID eq '162373' and
                    startswith(fields/FileLeafRef, 'query')",
        "headers": {
            "Prefer": "HonorNonIndexedQueriesWarningMayFailRandomly"
        }
    }
]

The startswith() function works fine, but I wouldn't want my users to only be able to search for filenames by search prefixing. contains() and endswith() don't seem to be valid for this endpoint. These functions are part of OData's advanced query capabilities, but it's unclear whether they should work for querying SharePoint list items.

If startswith() is indexed, why wouldn't contains() or endswith() be indexed as well? Is there a way to manually create an index to support contains() or endswith()? Using the /search endpoint isn't ideal since it's significantly slower, and we need to filter by Project_ID, Customer_ID, and other metadata.

Thank you for your time!


Solution

  • why wouldn't contains() or endswith() be indexed as well?

    NOTE: contains() and endswith() are NOT indexed because they require scanning every row, making them slow. SharePoint does not support reverse or full-text indexing in Graph API, which would be needed for contains() and endswith(),

    Recommended way is to use startswith() for fast Graph API queries For more details Refer this QnA.

    startswith() is indexed because SharePoint stores data in sorted and indexed structure, allowing quick lookups without scanning the entire dataset, which makes more efficient compared to contains() and endswith() .

    Generated the access token and used the same $filter query with startswith():

    GET https://graph.microsoft.com/v1.0/sites/<site-id>/lists/<list-id>/items?$filter=startswith(fields/FileLeafRef, 'query')
    

    enter image description here

    Reference:

    Microsoft QnA Blog on startswith