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.

Note: If you would like to become more familiar with the ShortPoint REST API, please check our solution article: Connection Type: REST API.

TABLE OF CONTENTS

• Selecting items
• Sorting items
• Filtering items
• Expanding for a person or lookup fields

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

Hint: If you have difficulties with finding the correct internal name of a specific column, check this third-party article for some help: List of internal names for SharePoint fields.

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 person or lookup fields

When dealing with person or lookup fields, only the ID will be returned. Using the $expand parameter, you can get the corresponding values based on the ID. 

Important: You need to make sure that the columns you would like to get, should be indicated in the $select query first (for example, $select=Author/Title&$expand=Author/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

Note: If you need to expand more than one field, you can do that using a comma separator between the fields (e.g., $expand=Author/Id,file).

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!

Related articles:

• Connection Type: REST API
• Map Results Function for Your Custom REST APIs
• Modify URL Parameters Function for your REST API connection
• Modify the URL Path Function for Your REST API Connection