SharePoint REST API Limitation

Introduction

This blog aims to discuss about the limitations of SharePoint REST API. I have discussed these issues several times in different forums. Now, I am just putting them together as a blog, so that you can see the overall picture.

Nested query is not supported

Two level $expand or nested query is not supported in SharePoint REST API. For example, you are querying on A list and A has a lookup column from B list. B has a lookup column from C list. Hence, if you are querying on A list, you can only $expand all the columns of B.

/_api/web/lists/getbytitle('A')?$select={columns for B}&$expand=B

You cannot $expand any column from C. The reason is there is no direct relation between A and C.

Workaround #1

At first, get the item Ids of B list, make another REST call to B list and $expand the columns of C.

/_api/web/lists/getbytitle('B')/items({ItemId})?$select={select column from c}&$expand=C

You can use $batch request to get the multiple items at a time but $batch request has no support in SharePoint 2013.

 

$filter does not work with the calculated columns.

Let’s say, we have a list called SpList and there is a calculated column, named SpCalculated. Now, we need a $filter on SpCalculated column.

/_api/web/lists/getbytitle('SpList')/Items?$filter=SpCalculated eq 'xxx'

The query,mentioned above will not work. It will throw the following exception.

{
    "status": "Bad Request",
    "error": {
        "error": {
            "code": "-1, Microsoft.SharePoint.SPException",
            "message": {
                "lang": "en-US",
                "value": "The field 'SpCalculated' of type 'Calculated' cannot be used in the query filter expression."
            }
        }
    }
}

Workaround #2

This kind of problem can be solved, using CAML query. For it, we will have to make a POST request in the end-point, mentioned below.

/_api/Web/Lists/getbytitle(SpList')/GetItems

The request body is mentioned below.

var query = "<View>\
<Query>\
<Where>\
<Eq>\
<FieldRef Name='SpCalculated' />\
<Value Type='Text'>xxx</Value>\
</Eq>\
</Where>\
</Query>\
</View>";
var data = {
    query: {
        __metadata: {
            'type': 'SP.CamlQuery'
        },
        ViewXml: query
    }
};

$filter does not work with Multi Valued column

$filter also does not work with the multi valued columns like multiple choices and multi valued user type columns.

Workaround #3

It’s similar to my previous workaround. We will have to make a POST request and request body will be a CAML query. See workaround #2.

Term Store Management is not supported

There is no end-point to manage Term Store in REST API.

Workaround #4

Using JSOM or CSOM, we can manage Term Store. Find JSOM API reference in MSDN.

Maximum URL length GET request

In GET request, maximum URL length is 260 characters. In SharePoint On Premise, we can modify this value from web.config file but in SharePoint online, there is no way.

Workaround #5

In most cases, I have noticed that URL length crosses the 260 characters because of $filter and $select query. Thus, we can solve this issue, using POST request and CAML query. See workaround #2.

Date Time functions are not supported

As per OData protocol, DateTime type column should support day(), month(), year(), hour(), minute() and second() function but /_api/web endpoint does not support it.

Workaround #6

The old end-point /_vti_bin/listdata.svc supports these functions. This end-point was first introduced in SharePoint 2010. It still works in later SharePoint versions as well. An example is mentioned below.

var filterByMonth = "/_vti_bin/listdata.svc/SpTutorial?$filter=month(SpDateTime) eq 6";

$batch request support

Batch request only works with SharePoint online and SharePoint 2016 (On Premise). In SharePoint 2013, it does not work.

Workaround #7

In some cases, we can solve this issue, using JSOM. My idea is that we can use clientContext.load() as many as we need and finally we can call clientContext.executeQueryAsync(). As a result, the single request will be made to the Server.

Conclusion

The issues, mentioned above are faced in different times and I  tried to solve them in this way. If you are familiar with any limitations, please leave a comment. It will help me to update this article.