Esper arithmetic and logical operator precedence follows standard arithmetic and logical operator precedence.
The below table outlines the logical and comparison operators available.
Table 7.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. |
=, !=, <, > <=, >=, | Comparison. |
The { and } curly braces are array definition operators following the 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 System.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 System.Int32[]. 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, System.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 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 13.4.18, “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 System.Collection.Generic.ICollection or a System.Collection.Generic.IDictionary. 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 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 (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
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 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 System.Text.RegularExpression provider. 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 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 System.Collections.Generic.ICollection or a System.Collections.Generic.IDictionary. 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 System.Collection.Generic.ICollection or a System.Collection.Generic.IDictionary. 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.