www.espertech.comDocumentation
Esper arithmetic and logical operator precedence follows Java standard arithmetic and logical operator precedence.
The below table outlines the arithmetic operators available.
Table 9.1. Syntax and Results of Arithmetic Operators
| Operator | Description |
|---|---|
| +, - |
As unary operators they denote a positive or negative expression. As binary operators they add or subtract. |
| *, / |
Multiplication and division are binary operators. |
| % |
Modulo binary operator. |
The below table outlines the logical and comparison operators available.
Table 9.2. Syntax and Results of Logical and Comparison Operators
| Operator | Description |
|---|---|
| NOT |
Returns true if the following condition is false, returns false if it is true. |
| OR |
Returns true if either component condition is true, returns false if both are false. |
| AND |
Returns true if both component conditions are true, returns false if either is false. |
| =, !=, <, > <=, >=, is, is not |
Comparison. |
The null value is a special value, see http://en.wikipedia.org/wiki/Null_(SQL) (source:Wikipedia) for more information.
Thereby the following expressions all return null:
2 != null
null = null
2 != null or 1 = 2
2 != null and 2 = 2
Use the is and is not operators for comparing values that can be null.
The following expressions all return true:
2 is not null
null is not 2
null is null
2 is 2
The engine allows is and is not with any expression, not only in connection with the null constant.
The below table outlines the concatenation operators available.
Table 9.3. Syntax and Results of Concatenation Operators
| Operator | Description |
|---|---|
| || |
Concatenates character strings |
The below table outlines the binary operators available.
Table 9.4. Syntax and Results of Binary Operators
| Operator | Description |
|---|---|
| & |
Bitwise AND if both operands are numbers; conditional AND if both operands are boolean. |
| | |
Bitwise OR if both operands are numbers; conditional OR if both operands are boolean. |
| ^ |
Bitwise exclusive OR (XOR). |
The { and } curly braces are array definition operators following the Java array initialization syntax. Arrays can be useful to pass to user-defined functions or to select array data in a select clause.
Array definitions consist of zero or more expressions within curly braces. Any type of expression is allowed within array definitions including constants, arithmetic expressions or event properties. This is the syntax of an array definition:
{ [expression [,expression...]] }
Consider the next statement that returns an event property named actions. The engine populates the actions property as an array of java.lang.String values with a length of 2 elements. The first element of the array contains the observation property value and the second element the command property value of RFIDEvent events.
select {observation, command} as actions from RFIDEvent
The engine determines the array type based on the types returned by the expressions in the array definiton. For example, if all expressions in the array definition return integer values then the type of the array is java.lang.Integer[]. If the types returned by all expressions are compatible number types, such as integer and double values, the engine coerces the array element values and returns a suitable type, java.lang.Double[] in this example.
The type of the array returned is Object[] if the types of expressions cannot be coerced or return object values. Null values can also be used in an array definition.
Arrays can come in handy for use as parameters to user-defined functions:
select * from RFIDEvent where Filter.myFilter(zone, {1,2,3})
You can use the dot operator to invoke a method on the result of an expression. The dot operator uses the dot (.) or period character.
The dot-operator is relevant with enumeration methods: Enumeration methods perform tasks such as transformation, filtering, aggregation, sequence-matching, sorting and others on subquery results, named windows, tables, event properties or inputs that are or can be projected to a collection of events, scalar values or objects. See Chapter 11, EPL Reference: Enumeration Methods
Further the dot-operator is relevant to date-time methods. Date-time methods work on date-time values to add or subtract time periods, set or round calendar fields or query fields, among other tasks. See Chapter 12, EPL Reference: Date-Time Methods.
The dot-operator is also relevant to spatial methods and the use of spatial indexes. See Chapter 19, EPL Reference: Spatial Methods and Indexes.
This section only describes the dot-operator in relation to property instance methods, the special get and size indexed-property methods and duck typing.
The synopsis for the dot operator is as follows
expression.method([parameter [,...]])[.method(...)][...]
The expression to evaluate by the dot operator is in parenthesis. After the dot character follows the method name and method parameters in parenthesis.
You may use the dot operator when your expression returns an object that you want to invoke a method on. The dot operator allows duck typing and convenient array and collection access methods.
This example statement invokes the getZones method of the RFID event class by referring to the stream name assigned in the from-clause:
select rfid.getZones() from RFIDEvent as rfid
The size() method can be used to return the array length or collection size. Use the get method to return the value at a given index for an array or collection.
The next statement selects array size and returns the last array element:
select arrayproperty.size() as arraySize, arrayproperty.get((arrayproperty).size - 1) as lastInArray from ProductEvent
Duck typing is when the engine checks at runtime for the existence of a method regardless of object class inheritance hierarchies. This can be useful, for example, when a dynamic property returns an object which may or may not provide a method to return the desired value.
Duck typing is disabled in the default configuration to consistently enforce strong typing. Please enable duck typing via engine expression settings as described in Section 15.4.26, “Engine Settings Related to Expression Evaluation”.
The statement below selects a dynamic property by name productDesc and invokes the getCounter() method if that method exists on the property value, or returns the null value if the method does not exist for the dynamic property value of if the dynamic property value itself is null:
select (productDesc?).getCounter() as arraySize from ProductEvent
The in keyword determines if a given value matches any value in a list. The syntax of the keyword is:
test_expression [not] in (expression [,expression...] )
The test_expression is any valid expression. The keyword is followed by a list of expressions to test for a match. The optional not keyword specifies that the result of the predicate be negated.
The result of an in expression is of type Boolean. If the value of test_expression is equal to any expression from the comma-separated list, the result value is true. Otherwise, the result value is false.
The next example shows how the in keyword can be applied to select certain command types of RFID events:
select * from RFIDEvent where command in ('OBSERVATION', 'SIGNAL')The statement is equivalent to:
select * from RFIDEvent where command = 'OBSERVATION' or command = 'SIGNAL'
Expression may also return an array, a java.util.Collection or a java.util.Map. Thus event properties that are lists, sets or maps may provide values to compare against test_expression.
All expressions must be of the same type or a compatible type to test_expression. The in keyword may coerce number values to compatible types. If expression returns an array, then the component type of the array must be compatible, unless the component type of the array is Object.
If expression returns an array of component type Object, the operation compares each element of the array, applying equals semantics.
If expression returns a Collection, the operation determines if the collection contains the value returned by test_expression, applying contains semantics.
If expression returns a Map, the operation determines if the map contains the key value returned by test_expression, applying containsKey semantics.
Constants, arrays, Collection and Map expressions or event properties can be used combined.
For example, and assuming a property named 'mySpecialCmdList' exists that contains a list of command strings:
select * from RFIDEvent where command in ( 'OBSERVATION', 'SIGNAL', mySpecialCmdList)
When using prepared statements and substitution parameters with the in keyword, make sure to retain the parenthesis. Substitution values may also be arrays, Collection and Map values:
test_expression [not] in (? [,?...] )Note that if there are no successes and at least one right-hand row yields null for the operator's result, the result of the any construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values.
The in keyword can be used to specify ranges, including open, half-closed, half-open and inverted ranges.
Ranges come in the following 4 varieties. The round () or square [] bracket indicate whether an endpoint is included or excluded. The low point and the high-point of the range are separated by the colon : character.
Open ranges that contain neither endpoint (low:high)
Closed ranges that contain both endpoints [low:high]. The equivalent 'between' keyword also defines a closed range.
Half-open ranges that contain the low endpoint but not the high endpoint [low:high)
Half-closed ranges that contain the high endpoint but not the low endpoint (low:high]
The following statement two statements are equivalent: Both statements select orders where the price is in the range of zero and 10000 (endpoints inclusive):
select * from OrderEvent where price in [0:10000]
select * from OrderEvent where price between 0 and 10000
The next statement selects order events where the price is greater then 100 and less-or-equal to 2000:
select * from OrderEvent where price in (100:2000]
Use the not in keywords to specify an inverted range.
The following statement selects an inverted range by selecting all order events where the price is less then zero or the price is greater or equal to 10000:
select * from OrderEvent where price not in (0:10000]
In case the value of low endpoint is less then the value of high endpoint the in operator reverses the range.
The following two statements are also equivalent:
select * from OrderEvent where price in [10000:0]
select * from OrderEvent where price >= 0 and price <= 1000
The between keyword specifies a range to test. The syntax of the keyword is:
test_expression [not] between begin_expression and end_expression
The test_expression is any valid expression and is the expression to test for in the range defined by begin_expression and end_expression. The not keyword specifies that the result of the predicate be negated.
The result of a between expression is of type Boolean. If the value of test_expression is greater then or equal to the value of begin_expression and less than or equal to the value of end_expression, the result is true.
The next example shows how the between keyword can be used to select events with a price between 55 and 60 (endpoints inclusive).
select * from StockTickEvent where price between 55 and 60
The equivalent expression without between is:
select * from StockTickEvent where price >= 55 and price <= 60
And also equivalent to:
select * from StockTickEvent where price between 60 and 55
While the between keyword always includes the endpoints of the range, the in operator allows finer control of endpoint inclusion.
In case the value of begin_expression is less then the value of end_expression the between operator reverses the range.
The following two statements are also equivalent:
select * from StockTickEvent where price between 60 and 55
select * from StockTickEvent where price >= 55 and price <= 60
The like keyword provides standard SQL pattern matching. SQL pattern matching allows you to use '_' to match any single character and '%' to match an arbitrary number of characters (including zero characters). In Esper, SQL patterns are case-sensitive by default. The syntax of like is:
test_expression [not] like pattern_expression [escape string_literal]
The test_expression is any valid expression yielding a String-type or a numeric result. The optional not keyword specifies that the result of the predicate be negated. The like keyword is followed by any valid standard SQL pattern_expression yielding a String-typed result. The optional escape keyword signals the escape character to escape '_' and '%' values in the pattern. The default escape character is backslash (\).
The result of a like expression is of type Boolean. If the value of test_expression matches the pattern_expression, the result value is true. Otherwise, the result value is false.
An example for the like keyword is below.
select * from PersonLocationEvent where name like '%Jack%'
The escape character can be defined as follows. In this example the where-clause matches events where the suffix property is a single '_' character.
select * from PersonLocationEvent where suffix like '!_' escape '!'
The regexp keyword is a form of pattern matching based on regular expressions implemented through the Java java.util.regex package. The syntax of regexp is:
test_expression [not] regexp pattern_expression
The test_expression is any valid expression yielding a String-type or a numeric result. The optional not keyword specifies that the result of the predicate be negated. The regexp keyword is followed by any valid regular expression pattern_expression yielding a String-typed result.
The result of a regexp expression is of type Boolean. If the value of test_expression matches the regular expression pattern_expression, the result value is true. Otherwise, the result value is false.
An example for the regexp keyword is below.
select * from PersonLocationEvent where name regexp '.*Jack.*'
The rexexp function matches the entire region against the pattern via java.util.regex.Matcher.matches() method. Please consult the Java API documentation for more information or refer to Regular Expression Flavors.
The any operator is true if the expression returns true for one or more of the values returned by a list of expressions including array, Collection and Map values.
The synopsis for the any keyword is as follows:
expression operator any (expression [,expression...] )
The left-hand expression is evaluated and compared to each expression result using the given operator, which must yield a Boolean result. The result of any is "true" if any true result is obtained. The result is "false" if no true result is found (including the special case where the expressions are collections that return no rows).
The operator can be any of the following values: =, !=, <>, <, <=, >, >=.
The some keyword is a synonym for any. The in construct is equivalent to = any.
Expression may also return an array, a java.util.Collection or a java.util.Map. Thus event properties that are lists, sets or maps may provide values to compare against.
All expressions must be of the same type or a compatible type. The any keyword coerces number values to compatible types. If expression returns an array, then the component type of the array must be compatible, unless the component type of the array is Object.
If expression returns an array, the operation compares each element of the array.
If expression returns a Collection, the operation determines if the collection contains the value returned by the left-hand expression, applying contains semantics.
When using relationship operators <, <=, >, >= the operator applies to each element in the collection, and non-numeric elements are ignored.
If expression returns a Map, the operation determines if the map contains the key value returned by the left-hand expression, applying containsKey semantics.
When using relationship operators <, <=, >, >= the operator applies to each key in the map, and non-numeric map keys are ignored.
Constants, arrays, Collection and Map expressions or event properties can be used combined.
The next statement demonstrates the use of the any operator:
select * from ProductOrder where category != any (categoryArray)
The above query selects ProductOrder event that have a category field and a category array, and returns only those events in which the category value is not in the array.
Note that if there are no successes and at least one right-hand row yields null for the operator's result, the result of the any construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values.
The all operator is true if the expression returns true for all of the values returned by a list of expressions including array, Collection and Map values.
The synopsis for the all keyword is as follows:
expression operator all (expression [,expression...] )
The left-hand expression is evaluated and compared to each expression result using the given operator, which must yield a Boolean result. The result of all is "true" if all rows yield true (including the special case where the expressions are collections that returns no rows). The result is "false" if any false result is found. The result is null if the comparison does not return false for any row, and it returns null for at least one row.
The operator can be any of the following values: =, !=, <>, <, <=, >, >=.
The not in construct is equivalent to != all.
Expression may also return an array, a java.util.Collection or a java.util.Map. Thus event properties that are lists, sets or maps may provide values to compare against.
All expressions must be of the same type or a compatible type. The all keyword coerces number values to compatible types. If expression returns an array, then the component type of the array must be compatible, unless the component type of the array is Object.
If expression returns an array, the operation compares each element of the array.
If expression returns a Collection, the operation determines if the collection contains the value returned by the left-hand expression, applying contains semantics.
When using relationship operators <, <=, >, >= the operator applies to each element in the collection, and non-numeric elements are ignored.
If expression returns a Map, the operation determines if the map contains the key value returned by the left-hand expression, applying containsKey semantics.
When using relationship operators <, <=, >, >= the operator applies to each key in the map, and non-numeric map keys are ignored.
Constants, arrays, Collection and Map expressions or event properties can be used combined.
The next statement demonstrates the use of the all operator:
select * from ProductOrder where category = all (categoryArray)
The above query selects ProductOrder event that have a category field and a category array, and returns only those events in which the category value matches all values in the array.
The new has two uses:
Populate a new data structure by evaluating column names and assignment expressions.
Instantiate an object of a given class by its constructor.
The new data structure operator populates a new data structure by evaluating column names and assignment expressions. This is useful when an expression should return multiple results, for performing a transformation or inside enumeration method lambda expressions.
The synopsis is as follows:
new { column_name = [assignment_expression] [,column_name...] }The result of the new-operator is a map data structure that contains column_name keys and values. If an assignment expression is provided for a column, the operator evaluates the expression and assigns the result to the column name. If no assignment expression is provided, the column name is assumed to be an event property name and the value is the event property value.
The next statement demonstrates the use of the new operator:
select new {category, price = 2*price} as priceInfo from ProductOrder
The above query returns a single property priceInfo for each arriving ProductOrder event. The property value is itself a map that contains two entries: For the key name category the value of the category property and for the key name price the value of the price property multiplied by two.
The next EPL is an example of the new operator within an expression definition and a case-statement (one EPL statement not multiple):
expression calcPrice {
productOrder => case
when category = 'fish' then new { sterialize = 'XRAY', priceFactor = 1.01 }
when category = 'meat' then new { sterialize = 'UVL', priceFactor = 1 }
end
}
select calcPrice(po) as priceDetail from ProductOrder po
In above example the expression calcPrice returns both a sterialize string value and a priceFactor double value. The expression is evaluated as part of the select-clause and the map-type result placed in the priceDetail property of output events.
When used within the case operator, the operator validates that the data structure is compatible between each case-when result in terms of column names and types. The default value for else in case is null.
The new instantiation operator can instantiate an object of the given class.
The synopsis is as follows:
new class-name([parameter [, parameter [,...]]])
The class-name is the name of the class to instantiate an object for. The classname can either be fully-qualified or you can add the package or classname to the engine imports.
After the classname follow parenthesis and any number of parameter expressions. The engine expects that the class declares a public constructor matching the number and return types of parameter expressions.
Assuming that OrderHolder is an imported class, the next statement demonstrates the use of the new operator:
select new OrderHolder(po) as orderHolder from ProductOrder as po