ShortPoint REST API: Selecting, Filtering, Sorting Results in a SharePoint List

Modified on: Mon, 6 Dec, 2021 at 3:03 PM

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

    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:

    Operator
    Meaning
    Not supported operators
    Numeric comparisons
    Lt
    Less thanArithmetic operators (Add, Sub, Mul, Div, Mod)
    LeLess than or equal
    GtGreater than
    GeGreater than or equalBasic math functions (round, floor, ceiling)
    EqEqual
    NeNot equal
    String comparisons
    startsWithFilters all the records that starts with certain characters in the specific columnendsWith
    replace
    substringofFilters all the records that contain with certain characters in the specific columnsubstring
    tolower
    EqEqualtoupper
    NeNot equaltrim
    concat
    Date and time functions
    day()

    Converts a date string to a day of the month

    DateTimeRangesOverlap operator
    month()Converts a date string to a month
    year()

    Converts a date string to a year

    hour()

    Converts a date string to an hour

    Querying whether a date time falls inside a recurrent date time pattern
    minute()

    Converts a date string to a minute

    second()

    Converts a date string to a second

    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.

    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!

    To search more helpful articles,
    Visit our Support Site

    Start a trial

    Ignite your vision. Install ShortPoint directly on your site, or play in sandbox mode. No credit card required.

    Get started today

    World’s best intranet sites are designed using ShortPoint

    Thousands of companies using ShortPoint everyday to design, brand and build award winning intranet sites.

    Get started Learn more