SharePoint Rest API Select, Filter, Sort and Paging Example

In this SharePoint Rest API tutorial, we will discuss how to use Rest API select, filter, sort, and paging in the SharePoint list. In the same way, we can use SharePoint Rest API to 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.

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.

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. The 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 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 the below examples-

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 the 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

OData query operators supported in the SharePoint REST service

Supported	Not 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.

The syntax for this is $top Count. This returns top n records.
See the 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 the 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 rest api filter example

Let us see an example on SharePoint Rest API Filter.

SharePoint development using JavaScript for any time I require unique customization not possible using out of the box. One of the technical challenges you may come across in your travels is trying to perform a GET of list items and filtering where a column value. Seems pretty straightforward right?

Here we will discuss how to filter or query using Rest API in SharePoint 2013.

In the old days working with SharePoint lists or libraries, many times we came across querying lists and applying a filter on list items using CAML Query. We all know constructing long CAML query gets confusing and many lines of chunks as well.

But no worries REST is the lifesaver; with REST it’s very easy to filter lists and gives to flexibility using few lines or sometimes just one line of code.

In this post, I will be showing how you can use REST to filter or query on SharePoint lists and returns data as you want.

You’ll first start to craft your URI like the following:

https://<siteUrl>/_api/Web/Lists/GetByTitle('listname')/items?$select*&$filter= Title eq 'test'

$Select: Lets you select list columns you want to return
$filter: Condition based results.

Refer to the following images and code for clear understanding.

Solution: 1st Approach:

Open the browser and add the following URL in the address bar:

https://hostssite/apps/TestAppsite/_api/Web/Lists/GetByTitle('Divisions')/items?$select=*&$filter=Title eq 'test1'

2nd Approach: Using JQuey

Step 1: Navigate to your SharePoint 2013 site.

Step 2: From this page select the Site Actions | Edit Page.

Edit the page, go to the “Insert” tab in the Ribbon, and click the “Web Part” option. In the “Web Parts”dialogue, go to the “Media and Content” category, select the “Script Editor” Web Part, and click the “Add button”.

Step 3: Once the Web Part is inserted into the page, you will see an “EDIT SNIPPET” link; click it. You can insert the HTML and/or JavaScript as in the following code snippet:

function Divsion ()
{

var listName = "Divisions";
var itemId="";

var Ownurl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle(' Divisions')/items?$select=OwnerEmailID&$filter=Title eq 'test1′";
$.ajax({

url: Ownurl,
headers: { Accept: "application/json;odata=verbose" },
async:false,
success: function (data) {
var items = data.d.results;
if (items[0].OwnerEmailID != "") {
itemId = items[0].OwnerEmailID;
}

},eror: function (data) {
alert("An error occurred. Please try again.");
}
});
}

This is how to use the Rest API filter in SharePoint Online/2013/2016/2019.

SharePoint Rest API Filter and Sort list data using AngularJS

Let us see how to filter and sort in SharePoint List data using AngularJS / REST API in SharePoint 2013/2016/Online. I used the REST API to talk to SharePoint and get the data from the list.

Here we just see that we have first created an Angular Controller with the name “spCustomerController”. We have also injected $scope and $http service. The $http service will fetch the list data from the specific columns of the SharePoint list.

$scope is a glue between a Controller and a View. It acts as an execution context for Expression. Angular expressions are code snippets that are usually placed in bindings such as {{ expression }}. We’ll be looking at a way to sort and filter our tabular data.

This is a common feature that is always useful so let’s look at what we’ll be building and dive right into the code.

SharePoint 2013 Filter and sort of List data using AngularJS and REST API

We will implement on a Sample Application and try to get data from SharePoint list bind the table and applying sort and filter our tabular data.

Our application will allow us to:

• Show a table of data (ng-repeat)
• Sort by ascending or descending columns (orderBy)
• Filter by using a search field (filter)

These are three common functions in any application and Angular lets us implement these features in a very simple way. Let’s set up our sample application’s HTML and Angular parts and then look at how we can sort and filter.

Use the following procedure to create a sample:

<h1> Filtering and sorting in SharePoint List using AngularJS-REST-API !!</h1>
<div ng-app="SharePointAngApp" class="row">
<div ng-controller="spCustomerController" class="span10">
<div>

Sort by:

<select ng-model="sortExpression">
<option value="Title">Title</option>
<option value="Employee">Employee</option>
<option value="Company">Company</option>
</select>
</div>
<br />

Search By Any:

<input type="text" ng-model="search.$" />
<br />
<br />
<table class="table table-condensed table-hover">
<tr>
<th>Title</th>
<th>Employee</th>
<th>Company</th>
</tr>
<tr ng-repeat="customer in customers | orderBy:mySortFunction | filter:search">
<td>{{customer.Title}}</td>
<td>{{customer.Employee}}</td>
<td>{{customer.Company}}</td>
</tr>
</table>
</div>
</div>

Step 1: Navigate to your SharePoint 2013 site.

Step 2: From this page select the Site Actions | Edit Page.

Edit the page, go to the “Insert” tab in the Ribbon and click the “Web Part” option. In the “Web Parts” picker area, go to the “Media and Content” category, select the “Script Editor” Web Part and press the “Add button”.

Step 3: Once the Web Part is inserted into the page, you will see an “EDIT SNIPPET” link; click it. You can insert the HTML and/or JavaScript as in the following:

<style>
th {
background-color: #778899;
color: white;
}
</style>

<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.0.1/angular.min.js"></script>
<script src="http://code.jquery.com/ui/1.10.3/jquery-ui.min.js"></script>
<script>
var myAngApp = angular.module(‘SharePointAngApp’, []);
myAngApp.controller(‘spCustomerController’, function ($scope, $http) {

$http({
method: ‘GET’,
url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getByTitle(‘InfoList’)/items?$select=Title,Employee,Company",
headers: { "Accept": "application/json;odata=verbose" }
}).success(function (data, status, headers, config) {

$scope.customers = data.d.results;
$scope.mySortFunction = function(customer) {//Sorting Iteam
if(isNaN(customer[$scope.sortExpression]))
return customer[$scope.sortExpression];
return parseInt(customer[$scope.sortExpression]);
}
}).error(function (data, status, headers, config) {
});
});
</script>

<h1> Filtering and sorting in SharePoint List using AngularJS-REST-API !!</h1>
<br /><br />
<div ng-app="SharePointAngApp" class="row">
<div ng-controller="spCustomerController" class="span10">
<div>

Sort by:
<select ng-model="sortExpression">
<option value="Title">Title</option>
<option value="Employee">Employee</option>
<option value="Company">Company</option>
</select>
</div>
<br />

Search By Any:

<input type="text" ng-model="search.$" />
<br />
<br />
<table class="table table-condensed table-hover">
<tr>
<th>Title</th>
<th>Employee</th>
<th>Company</th>
</tr>
<tr ng-repeat="customer in customers | orderBy:mySortFunction | filter:search">
<td>{{customer.Title}}</td>
<td>{{customer.Employee}}</td>
<td>{{customer.Company}}</td>
</tr>
</table>
</div>
</div>

Filtering the Table:

<tr ng-repeat="customer in customers | filter:search">

You will need to bind whatever input you are using with ng-model=”search.$”. Now as we type into that input box, you should see that table changes.

Sorting the Table:

<tr ng-repeat="customer in customers | orderBy:mySortFunction">

That’s all we need to change the sort order of our ng-repeat. If you refresh your page, you’ll see that your list is sorted by Employee in normal order.

Get list data using AngularJS REST API in SharePoint 2013

Let us see, how to get the data from SharePoint List using AngularJs and Rest API. I used REST API to talk to SharePoint and get the data from the list. I am not going to discuss much the REST services as many folks have already done great work on explaining REST API service.

In this script we just saw, we have first created an Angular Controller with the name ‘spCustomerController’. We have also injected $scope and $http service. The $http service will fetch the list data from the specific columns of SharePoint list.

$scope is a glue between the Controller and a View. It acts as an execution context for Expressions. Angular expressions are code snippets that are usually placed in bindings such as {{ expression }}.

<h1>WelCome To Angular JS Sharepoint 2013 REST API !!</h1>
<div ng-app="SharePointAngApp" class="row">
<div ng-controller="spCustomerController" class="span10">
<table class="table table-condensed table-hover">
<tr>
<th>Title</th>
<th>Employee</th>
<th>Company</th>
</tr>
<tr ng-repeat="customer in customers">
<td>{{customer.Title}}</td>
<td>{{customer.Employee}}</td>
<td>{{customer.Company}}</td>
</tr>
</table>
</div>
</div>

In the view, you can use the ng-repeat to iterate through the customers and display customer information.

Use the following procedure to create a sample.

Step 1: Navigate to your SharePoint 2013 site.

Step 2: From this page select Site Actions | Edit Page:

Edit the page, go to the “Insert” tab in the Ribbon, and click the “Web Part” option. In the “Web Parts” picker area, go to the “Media and Content” category, select the “Script Editor” Web Part, and press the “Add button”.

Step 3: Once the Web Part is inserted into the page, you will see an “EDIT SNIPPET” link; click it. You can insert the HTML and/or JavaScript as in the following:

<style>
table, td, th {
border: 1px solid green;
}
th {
background-color: green;
color: white;
}
</style>

<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.0.1/angular.min.js"></script>
<script src="http://code.jquery.com/ui/1.10.3/jquery-ui.min.js"></script>
<script>

var myAngApp = angular.module('SharePointAngApp', []);
myAngApp.controller('spCustomerController', function ($scope, $http) {
$http({
method: 'GET',
url: _spPageContextInfo.webAbsoluteUrl + “/_api/web/lists/getByTitle('InfoList')/items?$select=Title,Employee,Company",
headers: { "Accept": "application/json;odata=verbose" }
}).success(function (data, status, headers, config) {
$scope.customers = data.d.results;
}).error(function (data, status, headers, config) {
});
});
</script>

<h1> Angular JS SharePoint 2013 REST API !!</h1>
<div ng-app="SharePointAngApp" class="row">
<div ng-controller="spCustomerController" class="span10">
<table class="table table-condensed table-hover">
<tr>
<th>Title</th>
<th>Employee</th>
<th>Company</th>
</tr>
<tr ng-repeat="customer in customers">
<td>{{customer.Title}}</td>
<td>{{customer.Employee}}</td>
<td>{{customer.Company}}</td>
</tr>
</table>
</div>
</div>

Finally result in show below:

This is how to retrieve list data using AngularJS and Rest API in SharePoint 2013/2016 or SharePoint Online.

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 endpoint	Description	Supported 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 ‘)/Fields	Retrieving all fields associated with a list and add new fields	GET, 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’)/Items	Retrieving all items in a list and adding new items	GET, 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=Title	Order Your Results	GET, POST
/_api/lists/ getbytitle (‘’listname’)/items?$select=Title,Id	Retrieve Selected Column Data value	GET, POST
/_api/web/lists/getbytitle(‘listname’)/Items/ ?$select=Title,FieldName/Id&$expand= FieldName /Id	Retrieving the lookup value	GET, POST

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

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.

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.

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.

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.

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);
        }
    });
}

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

You may like the following tutorials:

• SharePoint list operations using rest API
• SharePoint rest api create folder
• Display SharePoint list data in jQuery data table using Rest API
• SharePoint Rest API Microsoft.SharePoint.Client.InvalidClientQueryException The expression is not valid. Bad Request
• get documents from document library in SharePoint using Rest API