SharePoint 2013 Using REST API Selecting filtering sorting and pagination in SharePoint list

sharepoint 2013 sorting filtering using rest api 2.png
InfoPath alternatives for form designing SharePoint

 I have explained Previous Article 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 basic REST url and will take Selecting, filtering, sorting and pagination options one by one.

Read some SharePoint branding articles below:

Main agenda of this Article is to understand how you can use take Selecting, filtering, sorting and pagination options one by one



sharepoint 2013 sorting filtering using rest api
sharepoint 2013 sorting filtering using rest api

Scenarios:
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:-

Selecting and sorting items:
$select : This  /administration/_api/web/lists/getbytitle(‘infolist’)/items url returns 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 field which are 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 2013 rest api filter
sharepoint 2013 rest api filter

‘$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 2013 rest api filter contains
sharepoint 2013 rest api filter contains

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

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

– 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 filter pagination
sharepoint rest api filter pagination

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 date time falls inside a recurrent date time pattern


– 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 person or lookup fields where only Id is returned. Using this we can get corresponding value based on Id.

See below example:
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 custom field: Author, it will return ‘AuthorId’ in response.
What is the proper way to deal with people field? You need to use ‘$expand’ parameter to expand the field.

Following REST URL gives your idea how to use $expand.
/_api/web/lists/getbytitle(‘infolist’)/items?$select=Author/Title&$expand=Author/Id

Similar SharePoint 2013 Tutorials


About Sagar Pardeshi

I am Developer working on Microsoft Technologies for the past 6+years. I am very much passionate about programming and my core skills are SharePoint, ASP.NET & C#,Jquery,Javascript,REST. I am running this blog to share my experience & learning with the community I am an MCP, MCTS .NET & Sharepoint 2010, MCPD Sharepoint 2010, and MCSD HTML 5,Sharepoint 2013 Core Solutions. I am currently working on Sharepoint 2010, MOSS 2007, Sharepoint 2013,Sharepoint 2013 App Dev, C#, ASP.NET, and SQL Server 2008.

View all posts by Sagar Pardeshi →

Leave a Reply