126025, 8:07 AM Query options overview - OData | Microsoft Lean Query options overview Article + 12/08/2022 A query option is a set of query string parameters applied to a resource that can help control the amount of data being returned for the resource in the URL. A query option is basically requesting that a service perform a set of transformations such as filtering, sorting, etc. to its data before returning the results. A query option can be applied to every verb except DELETE operations. The query options part of an OData URL specifies three types of information: system query options, Custom query options, and Parameter aliases. System query options System query options are query string parameters that contro! the amount and order of the data returned for the resource identified by the URL. The names of all system query options are optionally prefixed with a dollar ($) character. Data Protocol V4.01 based services support case-insensitive system query option names specified with or without the $ prefix. Clients that want to work with 4.0 services must use lower case names and specify the $ prefix System query options are $filter, $select, $orderby, $count, Stop, Sskip and $expand Filter The Sfilter system query option allows clients to filter a collection of resources that are addressed by a request URL. The expression specified with filter is evaluated for each resource in the collection, and only items where the expression evaluates to true are included in the response, Resources for which the expression evaluates to false or to null, or which reference properties that are unavailable due to permissions, are omitted from the response. You can find details on filter specification in the OData spec filter options section. Examples: * All products with a Name equal to ‘Milk’: http: //host/service/Products?$filter=Nane eq "Milk" titps:lear microsoft comlen-uslodatalconcoptsiqueryoptions-overviow 8126025, 8:07 AM Query options overview - OData | Microsoft Lean * All products with a Name not equal to ‘Milk’: nttp://nost/service/Products? $filter=Name ne ‘Milk’ * All products with a Name greater than ‘Milk’: http: //host/service/Products? $filter-Name gt ‘Milk’ * All products with a Name greater than or equal to ‘Milk’: [Link] ge ‘Milk’ * All products with a Name less than ‘Milk’: http: //host/service/Products?$#ilter-Name ae Milk? * All products with a Name less than or equal to ‘Milk’: http: ‘Jhost/service/Products? © All products with the Name 'Milk’ that also have a Price less than 2.5 [Link] eq ‘Milk’ and Price 1t 2.55 * All products that either have the Name ‘Milk’ or have a Price less than 2.55: http: //host/service/Products?$filter=Name eq ‘Milk’ or Price 1t 2.55 * All products that do not have a Name that ends with ‘ilk http: //host/service/Products?$filter=not endswith(Name, 'ilk') * All products whose style value includes Yellow: [Link] $filter=style has [Link]"Yellow’ * All products whose name value is ‘Milk’ or ‘Cheese’: http: //host/service/Products? $filter-Name in (‘Milk', ‘Cheese’) Expand The Sexpand system query option specifies the related resources or media streams to be included in line with retrieved resources. Each expandltem is evaluated relative to the entity containing the navigation or stream property being expanded. Examples: * Expand a navigation property of an entity type: http: //host/service/Products? $expand=Category titps:ilear microsoft comlen-uslodatalconcoptsiqueryoptions-overviow 216s28ns, 807 AM ‘Quer options oveview - Data | Mirsof Leam * Expand a navigation property of a complex type: http: //host/service/Custoners? Sexpand=Addresses/Country Query options can be applied to an expanded navigation property by appending a semicolon-separated list of query options, enclosed in parentheses, to the navigation property name. Allowed system query options are filter, $select, Sorderby, $skip, Stop, Scount, $search, and $expand. * All categories and for each category all related products with a discontinued date equal to null http: //host/service/Categories?$expand=Products($filter=Discontinuedbate eq null) Select The $select system query option allows clients to request a specific set of properties for each entity or complex type. The $select query option is often used in conjunction with the Sexpand system query option, to define the extent of the resource graph to return (Sexpand) and then specify a subset of properties for each resource in the graph (select) Examples: * Rating and release date of all products : http: //host/service/Products? $select=-Rating, ReleaseDate Itis also possible to request all declared and dynamic structural properties using a star (*) * All structural properties of all products: nttp://host/service/Products?$select=* OrderBy The Sorderby system query option allows clients to request resources in a particular order. * Return all Products ordered by release date in ascending order, then by rating in descending order GET [Link] asc, Rating desc titps:lear microsoft comlen-uslodatalconcoptsiqueryoptions-overviow a6126025, 8:07 AM Query options overview - OData | Microsoft Lean Related entities may be ordered by specifying $orderby within the Sexpand clause. * Return all Categories, and their Products ordered according to release date and in descending order of rating GET [Link] $expand=Products (Sorderby=ReleaseDate asc, Rating desc) $count may be used within a $orderby expression to order the returned items according to the exact count of related entities or items within a collection-valued property. * Return all Categories ordered by the number of Products within each category GET http: //host/service/Categories?$orderby=Products/Scount Top and Skip The $top system query option requests the number of items in the queried collection to be included in the result. The $skip query option requests the number of items in the queried collection that are to be skipped and not included in the result. A client can request a particular page of items by combining $top and $skip. Examples: * Get top 10 items http: //host/service/Products?$top=10 © Skip first 10 items http: //host/service/Products?$skip=: Count The $count system query option allows clients to request a count of the matching resources included with the resources in the response. The $count query option has a Boolean value of true or false. Examples: * Return, along with the results, the total number of products in the collection titps:lear microsoft comlen-uslodatalconcoptsiqueryoptions-overviow 46126025, 8:07 AM Query options overview - OData | Microsoft Lean http: //host/service/Products?$count=true * The count of related entities can be requested by specifying the $count query option within the $expand clause. http: //host/service/Categories?$expand=Products($count=true) Search The $search system query option allows clients to request items within a collection matching a free-text search expression. The $search query option can be applied to a URL representing a collection of entity, complex, or primitive typed instances, to return all matching items within the collection. Applying the $search query option to the Sall resource requests all matching entities in the service. If both $search and Sfilter are applied to the same request, the results include only those items that match both criteria Example: * All products that are blue or green. It is up to the service to decide what makes a product blue or green. http: //host/service/Products?$search=blue OR green Custom query options Custom query options provide an extensible mechanism for service-specific information to be placed in a URL query string. Custom query options MUST NOT begin with a $ or @ character. Examples: * Service-specific custom query option debug-mode http: //host/service/Products?debug-mode=true Parameter aliases titps:ilear microsoft comlen-uslodatalconcoptsiqueryoptions-overviow126025, 8:07 AM Query options overview - OData | Microsoft Lean Parameter aliases can be used in place of literal values in entity keys, function parameters, or within a Sfilter or $orderby expression. Parameter aliases MUST start with an @ character. Examples: * Filter movies that have the word Super in its title: http: //host/service/Movies? $filter=contains(@word, Title) &@wor Super" * Filter movies that have the title 'Wizard of Oz': http: //host/service/Movies? $filter-Title eq @titlea@title="Wizard of 02 * JSON array of strings as parameter alias value — note that [,], and " need to be percent-encoded in real URLs, the clear-text representation used here is just for readability : hetp://host/service/Products/Model .withIngredients (Ingredients: @ Ni)? Ginger" , “Oranges” ] Conventions A request to a resource using Http verbs GET, PATCH or PUT follow these conventions: * Resource paths identifying a single entity, a complex type instance, a collection of entities, or a collection of complex type instances allow $compute, Sexpand and $select. * Resource paths identifying a collection allow Sfilter, $search, $count, $orderby, $skip, and $top. * Resource paths ending in /$count allow Sfilter and $search * Resource paths not ending in /Scount or /Sbatch allow $format. * Query options for a PosT operations may differ due to the type of object being returned in the response, If a POST returns a single entity vs collection of entities, it will impact the query options that are applicable to the request. Feedback Was this page helpful? titps:lear microsoft comlen-uslodatalconcoptsiqueryoptions-overviow 56