ShortPoint REST API: Selecting, Filtering, Sorting Results in a SharePoint List
When setting up the REST API connection for your SharePoint intranet, it might happen that you will need to perform some modifications on the data retrieved by REST API, for example, selecting or filtering specific items.
In this solution article, we will shed light on the most common parameters that you can use for such operations while forming the REST API URL to get data: $select, $orderby, $filter and $expand.
For the purposes of this article, we will take the following REST API URL as an example, and modify it according to the certain operations explained:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items
The above REST API request returns all items with all possible fields from a SharePoint list called "Employees", located in the SharePoint site.
Make sure to update the {siteUrl} and the list title values to match your needs.
Selecting items
By forming the REST API URL to get the items (for example, URL), it will get all the fields available in that SharePoint list. However, it might happen that there are too many columns with data, but you only need to get some of them. In such cases, you can select specific fields to return for your result items using the $select parameter.
The syntax for the $select parameter is as follows: $select=Field1, Field2, Field3.
Provided that you only need to return the ID, Title and Employee values from the list, your REST API URL might look like this:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=ID,Title,Employee
Sorting items
If you are getting a list of items as a REST API response, you might want to sort them on a specific field name. For this operation, you will need to use the $orderby system filter parameter, specifying if you want to get the sorting in ascending (asc) or descending (desc) order.
The syntax for the $orderby parameter is as follows: $orderby=(ColumnInternalName order).
For example, if you want to sort the results on the Employee field, your REST API URL might look similar to this:
Ascending order:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=ID,Title,Employee&$orderby= Employee asc
Descending order:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=ID,Title,Employee&$orderby= Employee desc
Filtering items
If you want to get only the results that match some specific condition(s), you may use the $filter parameter.
The syntax for the $filter parameter is as follows: $filter=(ColumnInternalName operator value).
Operators
Below you may find the operators supported in the SharePoint REST service, as well as those you cannot use:
Here are some examples of using $filter parameter:
Filtering by Title:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter= Employee eq ‘parth'
Filtering by ID:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter= ID eq 2
Filtering by Date:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter=Start_x0020_Date le datetime'2016-03-26T09:59:32Z'
Title name starts with the letter P:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter=startswith(Title,‘P’)
Return all items from the "Employees" modified in May:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter=month(Modified) eq 5
You can also use multiple filters using the and operator to combine them, and wrapping each filter condition in a parenthesis:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$filter=(Modified le datetime'2016-03-26T09:59:32Z') and (ID eq 2)
Expanding for a person or lookup fields
When dealing with a person or lookup fields, only the ID will be returned. Using the $expand parameter, you can get the corresponding values based on the ID.
The syntax for $expand operator is as follows: $expand= Field/Id
Here are some examples:
Lookup field: a City column is a lookup to Title column in the "Employees" list, and we would like to get it in the results:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=ID,Title,Employee,company,city/Id&$expand= city/Id
People field: the "Employees" list contains a custom field Author, and returns the AuthorId in response. To get the title of the Author, we need to expand the Author field:
{siteUrl}/_api/web/lists/getbytitle('Employees')/items?$select=Author/Title&$expand=Author/Id
That's it. Now it's time for you to get into REST-ing and deal with the data results filtering and sorting like a true professional!