Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Use OData query operations in SharePoint REST requests

Use OData query operations in SharePoint REST requests

SharePoint Add-ins

Learn how to use a wide range of OData query string operators to select, filter, and order the data you request from the SharePoint REST service.

Last modified: March 09, 2015

Applies to: SharePoint Add-ins | SharePoint Foundation 2013 | SharePoint Server 2013

Note Note

The name "apps for SharePoint" is changing to "SharePoint Add-ins". During the transition, the documentation and the UI of some SharePoint products and Visual Studio tools might still use the term "apps for SharePoint". For details, see New name for apps for Office and SharePoint.

In this article
Select fields to return
Select items to return
Query for single value lookup fields
Query for users
Query for multi-value lookup fields and users
Sort returned items
Page through returned items
OData query operators supported in the SharePoint REST service
Additional resources

Before you start

The SharePoint REST service supports a wide range of OData query string operators that enable you to select, filter, and order the data you request.

Tip Tip

The SharePoint Online REST service supports combining multiple requests into a single call to the service by using the OData $batch query option. For details and links to code samples, see Make batch requests with the REST APIs. This option is not yet supported for on-premise SharePoint.

Use the $select query option to specify which fields to return for a given list, list item, or other SharePoint object represented by an entity set. You can use $select=* to return all available fields.

Note Note

In general, if you do not specify the $select query option, the REST service returns all available fields by default. However, in a few cases, some SharePoint objects include properties that are very resource intensive to retrieve; to optimize REST service performance, these properties are not included in the default query, and must be explicitly requested.

For example, the SPWeb.EffectiveBasePermissions property is not returned by default, and must be explicitly requested using the $select query option.

In addition, you can specify that the request returns projected fields from other lists and the values of lookups. To do this, specify the field name in both the $select and $expand query options. For example:

http://server/site/_api/web/lists('guid')/items?$select=Title,Products/Name&$expand=Products/Name

Bulk expansion and selection of related items is not supported.

Use the $filter query option to select which items to return. OData query operators supported in the SharePoint REST service lists the filter query comparison options and functions you can use with the SharePoint REST service.

Single value lookup fields are represented by two separate fields in the SharePoint REST service: one field representing the actual field value, and another representing the field name. You can perform queries against the lookup field value as you would any other field of that data type. For example, if the lookup field value is a string, you can use string comparison options in your query.

In the SharePoint REST service, users are represented by the user's friendly (display) name, and not their alias or domain\alias combination. Therefore, you must construct user queries against users' friendly names.

Note Note

Membership-based user queries are not supported.

Usage of the Current operator to do queries using the ID of the current user is not supported.

Because multi-value lookup fields are returned as a string of multiple values, there is no way to query for them (for example, the equivalent of an Includes element or NotIncludes element is not supported).

Use the $orderby query option to specify how to sort the items in your query return set. To sort by multiple fields, specify a comma-separated list of fields. You can also specify whether to sort the items in ascending or descending order by appending the asc or desc keyword to your query.

Use the $top and $skiptoken query options to select a subset of the items that would otherwise be returned by your query.

Note Note

The $skip query option does not work with queries for SharePoint list items.

The $top option enables you to select the first n items of the return set for return. For example, the following URI requests that only the first ten items in the prospective return set actually be returned:

http://server/site/_api/web/lists('<guid>')/items$top=10

The $skiptoken option enables you to skip over items until the specified item is reached and return the rest.

Note Note

When using these query options, take into account that paging in OData is ordinal. For example, suppose you are implementing a next page button to display SharePoint list items. You use the REST service to enable the button to return items 1 through 20 when clicked, then items 21 through 40, and so on. However, suppose another user deletes items 4 and 18 between clicks of the next button. In such a case, the ordinal positioning of the remaining items is reset, and displaying items 21 through 40 actually skips over two items.

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

The figure below shows the supported OData query options.

Supported OData query options

SharePoint REST service query option syntax
Show:
© 2015 Microsoft