{"id":4563,"date":"2019-02-06T17:38:45","date_gmt":"2019-02-06T22:38:45","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=4563"},"modified":"2019-02-06T17:53:21","modified_gmt":"2019-02-06T22:53:21","slug":"sharepoint-rest-api-does-not-allow-unindexed-queries","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=4563","title":{"rendered":"SharePoint Rest API Does Not Allow Unindexed Queries"},"content":{"rendered":"

I’ve been developing code templates for CRUD operations (that’s a real acronym — Create, Read, Update, Delete) against SharePoint — we need to use SharePoint lists to replace database tables. Retrieving information worked fine until I tried to filter the data through the REST call. SharePoint throws a generic error about exceeding some admin-set limit. (1) I know the limit, I can see the limit. The limit is 5,000, and I know my filtered result set is 121 records. WAY lower than 5,000. Oh, and (2) I can run the query without the filter — I’m paging it! — and read all 29,887 records so what does the limit have to do with anything? Reasoning with an HTTP response … well, doesn’t work. No matter how unassailable my argument is, the API call still returned:<\/p>\n

{\"error\":\r\n    {\"code\":\"-2147024860, Microsoft.SharePoint.SPQueryThrottledException\",\r\n     \"message\":{\"lang\":\"en-US\",\r\n       \"value\":\"The attempted operation is prohibited because it exceeds the list \r\n                view threshold enforced by the administrator.\"}}}<\/pre>\n
It is, it turns out, a poorly worded error. I started thinking about the query limits on my LDAP servers — we have hard limits to operations and also require most people perform queries against indexed attributes. It’s computationally expensive to search through unindexed attributes (and the Right Thing To Do, generally speaking, is add an index for something that is a frequent query target). I wondered if there was an analogous “no unindexed queries” setting in SharePoint. Quick enough to test — add an index on the column(s) you use in the filter. In the site content listing, click the sideways hamburger menu by the list name. Select “Settings”<\/p>\n
\"\"<\/a><\/p>\n
Scroll down to Index Columns and click the hyperlink.<\/p>\n
\"\"<\/a><\/p>\n
Click ‘Create a new index’<\/p>\n
\"\"<\/a><\/p>\n
Wait for the index process to complete, then<\/em> try the filtered request again … I’ve got data! Evidently SharePoint ODATA filter queries to the REST API need to be performed against indexed columns. I’m sure Microsoft has that documented somewhere<\/em> but quite a bit of Googling didn’t get me anywhere … so I’m posting this in case anyone else encounters the same error.<\/p>\n","protected":false},"excerpt":{"rendered":"
I’ve been developing code templates for CRUD operations (that’s a real acronym — Create, Read, Update, Delete) against SharePoint — we need to use SharePoint lists to replace database tables. Retrieving information worked fine until I tried to filter the data through the REST call. SharePoint throws a generic error about exceeding some admin-set limit. …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[33,677],"tags":[749,415,747,748],"class_list":["post-4563","post","type-post","status-publish","format-standard","hentry","category-coding","category-office-365","tag-odata","tag-sharepoint","tag-sharepoint-online","tag-spo"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4563","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4563"}],"version-history":[{"count":3,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4563\/revisions"}],"predecessor-version":[{"id":4570,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4563\/revisions\/4570"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4563"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4563"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4563"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}