These examples use various OData services:
To run the CAP based V4 OData service in this repository, use cds run. The default port 4004 is used.
Products that are discontinued
Products?$filter=Discontinued eq true
Basic, single filter on collection with logical operator eq
Products that are discontinued but there is still stock
Products?$filter=Discontinued eq true and UnitsInStock gt 0
Combination of filters with logical operators eq, gt and and
Products in Category 2 that are expensive (25.00 or more)
Categories/2/Products?$filter=UnitPrice ge 25.00
Filter on collection via navigation with logical operator ge
All people with female gender
People/$filter=Gender has Microsoft.OData.SampleService.Models.TripPin.PersonGender'Female'
Referencing an enumeration value, using logical operator has
Categories with fewer than 10 products
Categories?$filter=Products/$count lt 10
Using the raw value of the number of items in a collection (via navigation)
Products with high availability (more than 100 units of stock and backorders combined)
Products?$filter=UnitsInStock add UnitsOnOrder gt 100
Combining two numeric properties with add
Products with a high stock value (over 3,500.00)
Products?$filter=UnitsInStock mul UnitPrice gt 3500.00
Combining two numeric properties with mul
Suppliers with a '555' phone code, and a homepage
Suppliers?$filter=contains(Phone,'555') and HomePage ne 'NULL'
Using the contains function (note arg order and no spaces)
Products with short names
Products?$filter=length(ProductName) lt 5
Combining the canonical function length's output with the logical operator lt
Airports where the IATA and ICAO codes diverge
Airports?$select=Name,IcaoCode,IataCode&$filter=not contains(IcaoCode,IataCode)
Using the contains function with the logical operator not, with both parameters passed to contains being properties (see also IATA vs ICAO)
Orders shipped on the first of the month
Summary_of_Sales_by_Years?$count=true&$filter=day(ShippedDate) eq 1
Using day, one of many date and time functions, plus $count as a system query option, to show the number of orders
Total order value for 1996
Summary_of_Sales_by_Years?$apply=filter(year(ShippedDate) eq 1996)/aggregate(Subtotal with sum as Total)
Using year with a bonus digression on aggregation via $apply
Products with pennies in the unit price
Products?$filter=round(UnitPrice) ne UnitPrice
Rounding the UnitPrice value with round and comparing it to what it was with the logical operator ne
Just for info - discontinued products and their categories, by category
Products?$filter=Discontinued eq true&$select=ProductName&$expand=Category($select=CategoryName,CategoryID)&$orderby=Category/CategoryID
Categories that have high stock products
Categories?$expand=Products&$filter=Products/any(x:x/UnitsInStock gt 100)
You can filter on the expanded collection (but this example is probably not what you want)
Categories with no discontinued products
Categories?$expand=Products&$filter=Products/all(x:x/Discontinued eq false)
Using the all lambda operator (as opposed to any)
Categories with at least some stock for every product
Categories?$expand=Products($select=ProductName)&$filter=Products/all(x:x/UnitsInStock gt 0)
Another example using all, and restricting the expanded collection data to just the product name
Categories and their discontinued products
Categories?$expand=Products($filter=Discontinued eq true)
A $filter query option can be applied to the expanded navigation property
Categories and the names of their discontinued products
Categories?$expand=Products($filter=Discontinued eq true;$select=ProductName)
Multiple system query options can be applied, separated by semicolons
A better list of categories and their discontinued products
Categories?$expand=Products($filter=Discontinued eq true)&$filter=Products/any(x:x/Discontinued eq true)
Combining a $filter on the expanded navigation property with a $filter using the any lambda operator
Email addresses in the 'example' domain for a person
TripPinServiceRW/People('russellwhyte')/Emails?$filter=contains($it,'example')
Using the $it literal to refer back to the collection in the resource path (here Emails is just an array of string values)