Selecting, filtering, sorting and pagination in SharePoint REST API.

In this post, I will explain how we can use REST API to filter or query on SharePoint lists and returns data as we want. My main agenda of this article is to understand ‘how you can use multiple filters in SharePoint REST API’ while fetching items from SharePoint list. We will start from basic REST API and will take Selecting, filtering, sorting and pagination options one by one.

sharepoint-2013-rest-apis-filter-TechnicalDiiary

In old days working with SharePoint lists or library, many times we came across querying lists and apply filters by using CAML Query. We all know constructing long CAML query gets confusing and many lines of chunks as well. SharePoint 2013 has come with exciting new features; which is REST API. With REST API, it’s very easy to filter lists and gives to flexibility using few lines and sometimes just one line of code. You can test them on the browser itself before writing any piece of code.

//To get items from list you can use following <strong>URL
http://[SiteUrl]/_api/web/lists/getbytitle('ListName')/items</strong>

The above Url return all items with all possible fields or list columns. But what if list has more than 20-30 columns? It’s not good practice to get all fields. Get only specific fields which are required.

$select : Selecting item in SharePoint REST API

You can select the specific attributes to return for your items using the $select parameter. Below example show how to use it.

//Syntax:  .........$select=Field1, Field2, Field3

//Example......
http://[SiteUrl]/_api/web/lists/getbytitle('ListName')/items?$select=ID,Title,Employee,company

$orderby : Sorting item in SharePoint REST API

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=columnName asc” or “$orderby=colunmName desc“.

//Syntax: ....&$orderby=(Column Internal Name order)

//query for assending order by title.....
http://[SiteUrl]/_api/web/lists/getbytitle('ListName')/items?$select=ID,Title,Employee,company&$orderby= Title asc

//query for decending order by title.....
http://[SiteUrl]/_api/web/lists/getbytitle('ListName')/items?$select=ID,Title,Employee,company&$orderby= Title desc

 

$filter: Filtering item in SharePoint REST API

You can filter the sharePoint list by adding simple logical expression with Url.

Syntax: ………$filter=(Column Internal Name operator value).

//Filter by ID:.....
http://[SiteUrl]/_api/web/lists/getbytitle('webList')/items?$filter=ID eq 1

//Filter by Title....
http://[SiteUrl]/_api/web/lists/getbytitle('webList')/items?$filter=Website eq ‘technicalDiary'

//Title name starts with the letter P.....
http://[SiteUrl]/_api/web/lists/getbytitle(‘'webList')/items?$filter=startswith(Title,‘T’)

//Filter by Date......
http://[SiteUrl]/_api/web/lists/getbytitle('webList')/items?$filter=Start_x0020_Date le datetime'2016-08-20T09:59:32Z'

//Multiple Filters.....
http://[SiteUrl]/_api/web/lists/getbytitle('webList')/items?$filter=( Modified le datetime'2016-08-20T09:59:32Z') and (ID eq 1)

//Return all items from the 'weblist' modified in August....
http://[SiteUrl]/_api/web/lists/getbytitle(‘'webList')/items? $filter=month(Modified) eq 8

$expand: expend lookup fields item in SharePoint REST API

This is very useful when dealing with person or lookup fields. Using this we can get corresponding value person or lookup fields based on Id.

//REST API Query used for lookup fields........
http://[...]/_api/web/lists/getbytitle('cityList')/items?$select=ID,Title,Employee,company,city/Id&$expand=city/Id  

//REST API Query used for lookup fields........
http://[SiteUrl]/_api/web/lists/getbytitle('emplist')/items?$select=Employee/Title&$expand=Author/Id

$top: operator specifies how many results to return.

This operator is used to implement paging for results. The $top operator specifies how many results to return.

// this query will return top 5 employee of the list......
http://[SiteUrl]/_api/web/lists/getbytitle('Emplist')/items?$top 5

 

Likewise, you can add more filters in REST query according to your requirement. If you think I miss some important stuff, please add in comments. Happy SharePoint coding. 🙂

S Sharif

Founder of Technical Diary. Software engineer and passionate about technologies to learn and share. Crazy about best practices and new technology. I have worked in C#,ASP.NET MVC, HTML5, AngularJs SharePoint and SQL Server. I always try to keep myself updated and learn and implement new technologies. You can connect me on Twitter Facebook LinkedIn.

You may also like...

1 Response

  1. February 23, 2017

    […] ng-href is also replaced by [routerLink]  Selecting, filtering, sorting and pagination in SharePoint REST API. […]

Leave a Reply

Your email address will not be published. Required fields are marked *