SharePoint Rest API Select, Filter, Sort and Paging Example

This SharePoint Rest API tutorial, we will discuss how to use Rest API select, filter, sort and paging in the SharePoint list. The same way we can use SharePoint Rest API select, filter, sort and paging in SharePoint Online or SharePoint 2013/2016.

I have explained how to work with SharePoint list items, basically performing CRUD operations, using the combination of REST API and jQuery Ajax. You can check out CRUD operations in SharePoint 2013 list item using rest api.

The REST URI ends with any OData query operators to specify selecting, sorting, or filtering. Let’s see other parameters and options which can be used with REST services. We will start from the basic REST URL and will take Selecting, filtering, sorting and pagination options one by one.

sharepoint rest api filter

The main agenda of this tutorial is to understand how you can use take Selecting, filtering, sorting and pagination options one by one using SharePoint 2013 list.

There can be scenarios where you want to perform additional operations on data returned by REST API like Selecting, filtering, sorting and pagination fields only, etc. For such operations while forming Url to Get data, we have to add some parameters as described below:-

Rest API Select and sort list item example

$select: /administration/_api/web/lists/getbytitle('infolist')/items

The above URL returns all items with all possible fields or list columns.

But what if the list has more than 20-30 columns? It’s not good practice to get all the fields. Get only a specific field that is required. You can select the specific attributes to return for your items using the $select parameter. Below example shows how to use it

Syntax for this is $select=Field1, Field2, Field3
/_api/web/lists/getbytitle('infolist')/items?$select=ID,Title,Employee,company
sharepoint rest api sort

SharePoint rest api order by

‘$orderby’: If your response is a list of items, each with a number of field names, you can sort the list on a given field name using the $orderby, $orderby.asc or $orderby.desc system filter parameter.

The first two specify sorting in ascending order and the third one descending order. Simple, you can use ‘$orderby’ parameter and provide the field name. REST service will return sorted list items in response.

Syntax: for this is $orderby=(Column Internal Name order)

See below examples-

sharepoint rest api pagination

SharePoint rest api sort ascending

Below is the Rest API syntax for sharepoint rest api sort ascending.

Ascending Order:  /_api/web/lists/getbytitle('infolist')/items?$select=ID,Title,Employee,company&$orderby= Employee asc

SharePoint rest api sort descending

Below is the Rest API syntax for sharepoint rest api sort descending.

Descending Order:  /_api/web/lists/getbytitle('infolist')/items?$select=ID,Title,Employee,company&$orderby= Employee desc

SharePoint rest api filter

Filtering items: You can filter your list to contain only items which match a simple logical expression using the $filter parameter.

Syntax: for this is $filter=(Column Internal Name operator value).

See below examples-

Filter by Title /_api/web/lists/getbytitle(‘infolist’)/items?$filter= Employee eq ‘parth’
Filter by ID:  /_api/web/lists/getbytitle(‘infolist’)/items?$filter=ID eq 2
Filter by Date  /_api/web/lists/getbytitle(‘infolist’)/items?$filter=Start_x0020_Date le datetime’2016-03-26T09:59:32Z’
Multiple Filters  /_api/web/lists/getbytitle(‘infolist’)/items?$filter=( Modified le datetime’2016-03-26T09:59:32Z’) and (ID eq 2)
Title name starts with the letter P/_api/web/lists/getbytitle(‘’infolist’)/items?$filter=startswith(Title,‘P’)
Return all items from the ‘infolist’list modified in May/_api/web/lists/getbytitle(‘’infolist’)/items? $filter=month(Modified) eq 5
sharepoint rest api paging

OData query operators supported in the SharePoint REST service

SupportedNot supported
Numeric comparisons
Lt
Le
Gt
Ge
Eq
Ne
Arithmetic operators (Add, Sub, Mul, Div, Mod)
Basic math functions (round, floor, ceiling)
String comparisons
startsWith
substringof
Eq
Ne
endsWith
replace
substring
tolower
toupper
trim
concat
Date and time functions
day()
month()
year()
hour()
minute()
second()
DateTimeRangesOverlap operator
Querying as to whether a datetime falls inside a recurrent datetime pattern

SharePoint rest api paging

Paging items: The $top operators are used to implement paging for results. The $top operator specifies how many results to return.

Syntax for this is $top Count. This returns top n records.
See below example:

/_api/web/lists/getbytitle('infolist')/items?$top 5

Note: The $skip operator does not work in SharePoint 2013 on list items.it works only on Lists.

See below example

/_api/web/lists? Orderby Title desc&$skip

$expand: This is very useful when dealing with a person or lookup fields where the only Id is returned. Using this we can get corresponding value based on Id.

See below example:

SharePoint rest api filter lookup column

Lookup Field: Say there is City column in County list which is a lookup to Title column in Info List.

/_api/web/lists/getbytitle('infolist')/items?$select=ID,Title,Employee,company,city/Id&$expand= city/Id

People Field: let’s say list have the custom field: Author, it will return ‘AuthorId’ in response.
What is the proper way to deal with people fields? You need to use the ‘$expand’ parameter to expand the field.

Following the REST URL gives your idea of how to use $expand.

/_api/web/lists/getbytitle('infolist')/items?$select=Author/Title&$expand=Author/Id

SharePoint 2013 crud operations using rest api

This SharePoint Rest API tutorial, we will discuss how to do CRUD operations in SharePoint 2013 list item using rest api.

SharePoint 2013 has greatly expanded the REST services available to developers. With this, we have much more SharePoint functionality exposed via JSOM and Web Services. The goal of this article is to provide how to perform basic create, read, update, and delete (CRUD) operations on lists and list items with the SharePoint REST services.

SharePoint 2013 crud operations using rest api

The following table contains typical REST endpoint URL examples to get you started working with SharePoint data. Prepend http://server/site/_api/ to the URL fragments shown in the table to construct a fully qualified REST URL.

Below is a list of the basic commands used to get List Items from a SharePoint List through the SharePoint 2013 REST Services.

URL endpointDescriptionSupported HTTP Method
/_api/Web/Lists/ getbytitle(‘listname’)Getting a list details by its title and updating it as well. Ifanyone changes your list title, your code will break.GET, POST
/_api/Web/Lists(guid’guid id of your list’)Same as above but changing list title will not affect the code.GET, POST
/_api/Web/Lists/getbytitle(‘ listname ‘)/FieldsRetrieving all fields associated with a list and add new fieldsGET, POST
/_api/Web/Lists/getbytitle(‘listname’)/
Fields/getbytitle(‘fieldname’)
Getting details of a field, modifying and deleting it.GET, PUT, PATCH, MERGE, DELETE
/_api/Web/Lists/getbytitle(‘listname’)/ItemsRetrieving all items in a list and adding new itemsGET, POST
/_api/web/lists/getbytitle(‘listname’)
/GetItemById(itemId)
This endpoint can be used to get, update and delete a single item.GET, PUT, PATCH, MERGE, DELETE
/_api/lists/ getbytitle (‘’listname’)/items?$orderby=TitleOrder Your ResultsGET, POST
/_api/lists/ getbytitle (‘’listname’)/items?$select=Title,IdRetrieve Selected Column Data valueGET, POST
/_api/web/lists/getbytitle(‘listname’)/Items/
?$select=Title,FieldName/Id&$expand= FieldName /Id
Retrieving the lookup valueGET, POST

Now, I will demo all the operations on list items, including retrieve, create, update and delete on list items.

sharepoint 2013 crud operations using rest api

Retrieve list items using Rest API in SharePoint 2013

Now, we will see SharePoint crud operations using rest api, how to retrieve list items using Rest API in SharePoint 2013/2016.

sharepoint crud operations using rest api
function retriveListItem()
{
    $.ajax
    ({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('companyInfo')/items?$select=Company,Industry",
        type: type,
        data: data,
        headers:
        {
            "Accept": "application/json;odata=verbose",
            "Content-Type": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "IF-MATCH": "*",
            "X-HTTP-Method": null
        },
        cache: false,
        success: function(data)
        {
            $("#ResultDiv").empty();
            for (var i = 0; i < data.d.results.length; i++)
            {
                var item = data.d.results[i];
                $("#ResultDiv").append(item.Company + "\t" + item.Industry + "<br/>");
            }
        },
        error: function(data)
        {
            $("#ResultDiv").empty().text(data.responseJSON.error);
        }
    });
}

Create list item using Rest API in SharePoint 2013

Now, we will see how to create a list item using Rest API in SharePoint 2013/2016.

sharepoint 2013 crud operations using rest api

Here is the main code in detail:

function AddListItem()
{
    var industryVal = $("#Industry").val();
    var Company = $("#Company").val();
    $.ajax
        ({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('companyInfo')/items",
        type: "POST",
        data: JSON.stringify
        ({
            __metadata:
            {
                type: "SP.Data.TestListItem"
            },
            Company: Company,
            Industry: industryVal
        }),
        headers:
        {
            "Accept": "application/json;odata=verbose",
            "Content-Type": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "X-HTTP-Method": "POST"
        },
        success: function(data, status, xhr)
        {
            retriveListItem();
        },
        error: function(xhr, status, error)
        {
            $("#ResultDiv").empty().text(data.responseJSON.error);
        }
    });
}

Update list item using Rest API in SharePoint 2013

Now, we will see how we can update the list item using Rest API in SharePoint 2013/2016.

crud operations using rest api in sharepoint 2013

Here is the main code in detail:

function updateListItem()
{
    var industryVal = $("#Industry").val();
    $.ajax
    ({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('companyInfo')/items(7)", // list item ID
        type: "POST",
        data: JSON.stringify
        ({
            __metadata:
            {
                type: "SP.Data.TestListItem"
            },
            Industry: industryVal
        }),
        headers:
        {
            "Accept": "application/json;odata=verbose",
            "Content-Type": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "IF-MATCH": "*",
            "X-HTTP-Method": "MERGE"
        },
        success: function(data, status, xhr)
        {
            retriveListItem();
        },
        error: function(xhr, status, error)
        {
            $("#ResultDiv").empty().text(data.responseJSON.error);
        }
    }); 

Delete list item using Rest API in SharePoint 2013

Now, we will see how to delete the list item using Rest API in SharePoint 2013/2016.

crud operations in sharepoint 2013 using rest api

Here is the main code in detail:

function deleteListItem()
{
    $.ajax
    ({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('companyInfo')/items(7)",
        type: "POST",
        headers:
        {
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "IF-MATCH": "*",
            "X-HTTP-Method": "DELETE"
        },
        success: function(data, status, xhr)
        {
            retriveListItem();
        },
        error: function(xhr, status, error)
        {
            $("#ResultDiv").empty().text(data.responseJSON.error);
        }
    });
}

You may like following SharePoint Rest API tutorials:

This SharePoint Rest API tutorial we learned how to use Rest API select, filter, sort and paging in a SharePoint list

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

>