Filtering support

The $filter 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.

The following filter keywords are supported:
Table 1. Logical operators
Name Keyword Description
Equals eq The eq operator returns true if the left operand is equal to the right operand, otherwise it returns false.
Examples:
http://host/serviceName/objects?$filter=stringField eq 'text'
http://host/serviceName/objects?$filter=booleanField eq true
http://host/serviceName/objects?$filter=numericalField eq 200
http://host/serviceName/objects?$filter=dateField eq 2015-11-25
http://host/serviceName/objects?$filter=dateTimeField eq 2015-11-25T20:15:03Z
http://host/serviceName/objects?$filter=timeField eq 11:12:13
http://host/serviceName/objects?$filter=durationField eq duration'P435DT15H0S'
Not Equals ne The ne operator returns true if the left operand is not equal to the right operand, otherwise it returns false.
Examples:
http://host/serviceName/objects?$filter=stringField ne 'text'
http://host/serviceName/objects?$filter=booleanField ne true
http://host/serviceName/objects?$filter=numericalField ne 200
http://host/serviceName/objects?$filter=dateField ne 2015-11-25
http://host/serviceName/objects?$filter=dateTimeField ne 2015-11-25T20:15:03Z
http://host/serviceName/objects?$filter=timeField ne 11:12:13
http://host/serviceName/objects?$filter=durationField ne duration'P435DT15H0S'
Greater Than gt The gt operator returns true if the left operand is greater than the right operand, otherwise it returns false.
Greater Than or Equal ge The ge operator returns true if the left operand is greater than or equal to the right operand, otherwise it returns false.
Less Than lt The lt operator returns true if the left operand is less than the right operand, otherwise it returns false.
Less Than or Equal le The le operator returns true if the left operand is less than or equal to the right operand, otherwise it returns false.
And and The and operator returns true if both the left and right operands evaluate to true, otherwise it returns false.
Example:
http://host/service/orders?$filter=address eq 'USA' and orderAmount gt 100000
Or or The or operator returns false if both the left and right operands both evaluate to false, otherwise it returns true.
Example:
http://host/service/orders?$filter=address eq 'USA' or orderAmount gt 5000
Not not The not operator returns true if the operand returns false, otherwise it returns false.
Example:
http://host/service/orders?$filter=not (indexof(address,'Bangalore') ne -1)

Table 2. Arithmetic Operators
Name Keyword Description
Addition add The add operator adds the left and right numeric operands.
Examples:
http://host/service/orders?$filter=fieldInteger add 15 eq 200
http://host/service/orders?$filter=fieldDecimal add 5.5 eq 25.0
http://host/service/orders?$filter=fieldDate add duration'P10D' eq 2018-10-28
http://host/service/orders?$filter=fieldDateTime add duration'P28DT03H15M20S' eq 2018-05-19
http://host/service/orders?$filter=fieldDuration add duration'P28DT03H15M20S' eq duration'P118DT03H15M20S'
Subtraction sub The sub operator subtracts the right numeric operand from the left numeric operand.
Examples:
http://host/service/orders?$filter=fieldInteger sub 15 eq 55
http://host/service/orders?$filter=fieldDecimal sub 10.5 eq 19.5
http://host/service/orders?$filter=fieldDate sub duration'P05D' eq 2018-10-13
http://host/service/orders?$filter=fieldDateTime sub duration'P05DT10H10M10S' eq 2015-11-20T10:05:35Z
http://host/service/orders?$filter=fieldDuration sub duration'P400DT2H05M10S' eq duration'P28DT03H15M20S'
http://host/service/orders?$filter=fieldDate sub 2018-10-08 eq duration'P10D'
http://host/service/orders?$filter=fieldDateTime sub 2015-11-10T13:00:00Z eq duration'P10DT07H15M45H'
Negation - The negation operator, represented by a minus (-) sign, changes the sign of its numeric or Duration operand.
Example:
http://host/service/orders?$filter=-rating eq -2
Multiplication mul The mul operator multiplies the left and right numeric operands.
Examples:
http://host/service/orders?$filter=fieldInteger mul 15 eq 75
http://host/service/orders?$filter=fieldDecimal mul 5.5 eq 57.75
http://host/service/orders?$filter=fieldDuration mul 3 eq duration'P90D'
Division div The div operator divides the left numeric operand by the right numeric operand.
Examples:
http://host/service/orders?$filter=fieldInteger div 15 eq 5
http://host/service/orders?$filter=fieldDecimal div 5.5 eq 10
http://host/service/orders?$filter=fieldDuration div 3 eq duration'P10D'
Modulo mod The mod operator returns the remainder when the left integral operand is divided by the right integral operand.
Examples:
http://host/service/orders?$filter=fieldInteger mod 5 eq 2
http://host/service/orders?$filter=fieldDecimal div 5.5 eq 1.2

Table 3. Grouping Operator
Name Keyword Description
Grouping operator ( ) The Grouping operator (open and close parenthesis “( )”) controls the evaluation order of an expression. The Grouping operator returns the expression grouped inside the parenthesis.
Examples:
http://host/service/orders?$filter=(address eq 'USA') and (orderAmount gt 100000)
http://host/service/orders?$filter=indexof(address,'Bangalore') ge 0 and contains(customerid,'IBM')
http://host/service/orders?$filter=not (indexof(address,'Bangalore') ne -1)

Table 4. String functions
Name Keyword Description
Concatenate concat The concat function returns a string that appends the second input parameter string value to the first.
Examples:
http://host/service/orders?$filter=concat(stringField,', CA') eq 'Sanfrancisco, CA'
http://host/service/orders?$filter=concat(stringField1,stringField2) eq 'abcdxyz'
Contains contains The contains function returns true if the second parameter string value is a substring of the first parameter string value, otherwise it returns false.
Example:
http://host/service/orders?$filter=contains(stringField3,'tablet')
Ends with endswith The endswith function returns true if the first parameter string value ends with the second parameter string value, otherwise it returns false.
Examples:
http://host/service/orders?$filter=endswith(stringField3,'dia')
http://host/service/orders?$filter=endswith(stringField3,null)
Index of indexof The indexof function returns the zero-based character position of the first occurrence of the second parameter value in the first parameter value, or -1 if the second parameter value does not occur in the first parameter value.
Examples:
http://host/service/orders?$filter=indexof(stringField5,'man') eq 2
http://host/service/orders?$filter=indexof(stringField5,'m') eq 0
Length length The length function returns the number of characters in the parameter value.
Examples:
http://host/service/orders?$filter=length(stringField) eq 9
http://host/service/orders?$filter=length(stringField) eq 0
Starts with startswith The startswith function returns true if the first parameter string value starts with the second parameter string value, otherwise it returns false.
Examples:
http://host/service/orders?$filter=startswith(stringField3,'cust')
http://host/service/orders?$filter=startswith(stringField3,null)
Substring substring The two argument substring function returns a substring of the first parameter string value, starting at the Nth character and finishing at the last character (where N is the second parameter integer value). The three argument substring function returns a substring of the first parameter string value identified by selecting M characters starting at the Nth character (where N is the second parameter integer value and M is the third parameter integer value).
Examples:
http://host/service/orders?$filter=substring(stringField4,5) eq 'y'
http://host/service/orders?$filter=substring(stringField4,0) eq 'policy'
http://host/service/orders?$filter=substring(stringField4,0,2) eq 'te'
To lower tolower The tolower function returns the input parameter string value with all uppercase characters converted to lowercase according to Unicode rules.
Example:
http://host/service/orders?$filter=tolower(stringField) eq 'zebra'
To upper toupper The toupper function returns the input parameter string value with all lowercase characters converted to uppercase according to Unicode rules.
Example:
http://host/service/orders?$filter=toupper(stringField) eq 'FUND'
Trim trim The trim function returns the input parameter string value with all leading and trailing whitespace characters, according to Unicode rules, removed.
Example:
http://host/service/orders?$filter=trim(stringField) eq 'umbrella'

Table 5. Date and Time Functions
Name Keyword Description
Date date The date function returns the date part of the DateTimeOffset parameter value, evaluated in the time zone of the DateTimeOffset parameter value.
Example:
http://host/service/orders?$filter=date(dateTimeField) eq 2017-05-15
Day day The day function returns the day component Date or DateTimeOffset parameter value, evaluated in the time zone of the DateTimeOffset parameter value.
Examples:
http://host/service/orders?$filter=day(dateField) eq 14
http://host/service/orders?$filter=day(dateTimeField) eq 25
Fractional seconds fractionalseconds The fractionalseconds function returns the fractional seconds component of the DateTimeOffset or TimeOfDay parameter value as a non-negative decimal value less than 1.
Examples:
http://host/service/orders?$filter=fractionalseconds(dateTimeField) ne 0
http://host/service/orders?$filter=fractionalseconds(timeField) ne 0
Hour hour The hour function returns the hour component of the DateTimeOffset or TimeOfDay parameter value, evaluated in the time zone of the DateTimeOffset parameter value.
Examples:
http://host/service/orders?$filter=hour(dateTimeField) eq 10
http://host/service/orders?$filter=hour(timeField) eq 15
Maximum datetime maxdatetime The maxdatetime function returns the latest possible point in time as a DateTimeOffset value.
Example:
http://host/service/orders?$filter=dateTimeField lt maxdatetime()
Minimum datetime mindatetime The mindatetime function returns the earliest possible point in time as a DateTimeOffset value.
Example:
http://host/service/orders?$filter=fieldDateTime gt mindatetime()
Minute minute The minute function returns the minute component of the DateTimeOffset or TimeOfDay parameter value, evaluated in the time zone of the DateTimeOffset parameter value.
Examples:
http://host/service/orders?$filter=minute(dateTimeField) eq 15
http://host/service/orders?$filter=minute(timeField) eq 30
Month month The month function returns the month component of the Date or DateTimeOffset parameter value, evaluated in the time zone of the DateTimeOffset parameter value.
Examples:
http://host/service/orders?$filter=month(dateTimeField) eq 5
http://host/service/orders?$filter=month(dateField) eq 11
Now now The now function returns the current point in time (date and time with time zone) as a DateTimeOffset value.
Example:
http://host/service/orders?$filter=dateTimeField gt now()
Second second The second function returns the second component (without the fractional part) of the DateTimeOffset or TimeOfDay parameter value.
Examples:
http://host/service/orders?$filter=second(dateTimeField) eq 59
http://host/service/orders?$filter=second(timeField) eq 5
Time time The time function returns the time part of the DateTimeOffset parameter value, evaluated in the time zone of the DateTimeOffset parameter value.
Example:
http://host/service/orders?$filter=time(dateTimeField) eq 18:15:45
Total offset minutes totaloffsetminutes The totaloffsetminutes function returns the signed number of minutes in the time zone offset part of the DateTimeOffset parameter value, evaluated in the time zone of the DateTimeOffset parameter value.
Example:
http://host/service/orders?$filter=totaloffsetminutes(dateTimeField) eq 330
Total seconds totalseconds The totalseconds function returns the duration of the value in total seconds, including fractional seconds.
Example:
http://host/service/orders?$filter=totalseconds(durationField) eq 8430
Year year The year function returns the year component of the Date or DateTimeOffset parameter value, evaluated in the time zone of the DateTimeOffset parameter value.
Examples:
http://host/service/orders?$filter=year(dateTimeField) eq 2017
http://host/service/orders?$filter=year(dateField) eq 2005

Table 6. Arithmetic functions
Name Keyword Description
Ceiling ceiling The ceiling function rounds the input numeric parameter up to the nearest numeric value with no decimal component.
Examples:
http://host/service/orders?$filter=ceiling(doubleField) eq 200
http://host/service/orders?$filter=ceiling(decimalField) eq 24
Floor floor The floor function rounds the input numeric parameter down to the nearest numeric value with no decimal component.
Examples:
http://host/service/orders?$filter=floor(doubleField) eq 200
http://host/service/orders?$filter=floor(decimalField) eq 24
Round round The round function rounds the input numeric parameter to the nearest numeric value with no decimal component. The mid-point between two integers is rounded away from zero, i.e. 0.5 is rounded to 1 and -0.5 is rounded to -1.
Examples:
http://host/service/orders?$filter=round(doubleField) eq 10
http://host/service/orders?$filter=round(decimalField) eq 24



Feedback | Notices


Timestamp icon Last updated: Monday, 24 July 2017


http://pic.dhe.ibm.com/infocenter/wci/v7r0m0/topic/com.ibm.wci.doc/OData_filtering_support.html