Expressions¶
Constant Expr¶
Constant values of STRING, BOOLEAN, INTEGER, FLOAT, DOUBLE, IPV4 address, IPV4 socket and IPV4 network, MACADDR can be expressed as Literals.
Note
Other data types (TIMESTAMP, IPv6 address, BYTES, GEOPOINT) can be expressed using cast functions.
See also
Example:
1 2 3 4 5 6 7 8 9 10 11 12 | dual | select(string:"this is string",
boolean:true,
integer:1,
float:2.0f,
double:3e2,
ipv4:192.168.0.1,
ipv4_socket:192.168.0.1:443,
ipv4_net:192.168.0.0/24,
macaddr:00-01-02-03-04-05,
array:[1,2,3],
tuple:{a:3, b:"test"}
)
|
string | boolean | integer | float | double | ipv4 | ipv4_socket | ipv4_net | macaddr | array | tuple |
---|---|---|---|---|---|---|---|---|---|---|
this is string | true | 1 | 2.0 | 300.0 | 192.168.0.1 | 192.168.0.1:443 | 192.168.0.0/24 | 00-01-02-03-04-05 | [1, 2, 3] | {a=3 b=”test”} |
Variable Expr¶
- Variable name of expression returning single value of any Query Data Types must be prepended by dollar “$” character:
- $variable_name
- A variable can take optional input arguments, which will be passed to expression the variable is assigned to:
- $variable_name(args ,…)
See also
Example:
1 2 3 4 5 6 | $the_question = 'What is the meaning of life, universe and everything?';
$get_ultimate_answer(question) =
IF($question is not NULL, 'The answer is: 42', 'The answer is still: 42');
dual | select($the_question, $get_ultimate_answer($the_question));
|
the_question | get_ultimate_answer |
---|---|
What is the meaning of life, universe and everything? | The answer is: 42 |
Named Column Expr¶
- Columns of the structured stream can be accessed by name:
- column_name
Example:
1 | dual(5) | select(t, i, s);
|
- The name may optionally be prepended by the alias of the structured stream it belongs to:
- stream_alias.column_name
Example:
1 2 3 4 | @second = dual(5) | select(t, s);
@first = dual(5) | select(t, ip);
@first | join(@second on left.t = right.t) | select(t, ip, s);
|
t | ip | s |
---|---|---|
2019-09-25 14:07:47.119 +0000 | 0.0.0.0 | 0ho0 |
2019-09-25 14:07:47.120 +0000 | 0.0.0.1 | 1ho1 |
2019-09-25 14:07:47.121 +0000 | 0.0.0.2 | 2ho2 |
2019-09-25 14:07:47.122 +0000 | 0.0.0.3 | 3ho3 |
2019-09-25 14:07:47.123 +0000 | 0.0.0.4 | 4ho4 |
- Columns or expressions can be assigned an alias name:
- alias:column_name or expr
Example:
1 | dual(0xfffffff,5) | select(ip_addr:ip, ip_country:CC(ip), is_even:i%2==0);
|
ip_addr | ip_country | is_even |
---|---|---|
15.255.255.255 | US | false |
16.0.0.0 | US | true |
16.0.0.1 | US | false |
16.0.0.2 | US | true |
16.0.0.3 | US | false |
Positioned Column Expr¶
- Columns of the structured stream can be accessed by their position (from left, starting from 1):
- @position
Example:
1 | dual(5) | select(@6, @1, @7);
|
ip | i | t |
---|---|---|
0.0.0.0 | 0 | 2019-09-25 13:34:02.070 +0000 |
0.0.0.1 | 1 | 2019-09-25 13:34:02.071 +0000 |
0.0.0.2 | 2 | 2019-09-25 13:34:02.072 +0000 |
0.0.0.3 | 3 | 2019-09-25 13:34:02.073 +0000 |
0.0.0.4 | 4 | 2019-09-25 13:34:02.074 +0000 |
Column By Name Expr¶
- Columns of the structured stream can be accessed dynamically at runtime using COLUMN expression:
- COLUMN(expr)
COLUMN comes useful in resolving column names dynamically at runtime, for instance when passed as parameters to @@stream_udf.
Example:
1 | dual(5) | select(COLUMN('ip'));
|
ip |
---|
0.0.0.0 |
0.0.0.1 |
0.0.0.2 |
0.0.0.3 |
0.0.0.4 |
Resource Expr¶
- Query scripts, saved in the resource tree, can be executed from any other script:
@[path_to_script]
- The path to target script must point to target script (with .sx extension) in the resource tree
- The path can be either absolute (starting from root of the resource tree) or relative to the current script location.
Note
The current script must be saved in order to use a relative path (i.e it must have a place in the resource tree).
Example: executing script example.sx saved in the /user/ directory:
1 | @[/user/example.sx];
|
Note
Use CTR+SPACE
shortcut key to fill in the path!
Fetch Expr¶
- Loads content (as a string) of a file from specified URI:
FETCH(uri)
- the uri is a string specifying target Data Access URI without wildcards
Note
If the URI refers to any defined data store then access to its target is subject to the data store’s ACL.
Example: loading content of example.txt file located in the user’s “files” folder in the resource tree:
1 | FETCH('sx:/user/files/example.txt');
|
Function Expr¶
Typed Selector Expr¶
- TIMESTAMP and TIMESTAMP_NANO allow performing time arithmetic functions using expressions:
1 2 3 4 5 6 | time_expr[+123 ms] // Same as TIME_ADD(ts_expr, 123)
time_expr[-30 sec] // Same as TIME_SUB(ts_expr, -30*1000L)
time_expr[%10 min] // Same as TIME_SUBMOD(ts_expr, 10*60*1000L)
time_expr[40 hour] // Same as TIME_SUBMOD(ts_expr, 40*60*60*1000L)
time_expr[+14 day] // Same as TIME_ADD(ts_expr, 14*24*60*60*1000L)
time_expr[-2 week] // Same as TIME_SUB(ts_expr, 2*7*24*60*60*1000L)
|
- Accessing ARRAY members
1 | tuple['member']
|
- Accessing TUPLE members
1 | tuple['member']
|
Arithmetic Expr¶
- Addition:
- numeric_expr1 + numeric_expr2
- Subtraction:
- numeric_expr1 - numeric_expr2
- Multiplication:
- numeric_expr1 * numeric_expr2
- Division:
- numeric_expr1 / numeric_expr2
- Modulo:
- numeric_expr1 % numeric_expr2
See also
Example:
1 | dual(5) | select(i,'i+1':i+1, 'i-1':i-1, 'i*10':i*10, 'i/2':i/2, 'i%2':i%2);
|
i | i+1 | i-1 | i*10 | i/2 | i%2 |
---|---|---|---|---|---|
0 | 1 | -1 | 0 | 0 | 0 |
1 | 2 | 0 | 10 | 0 | 1 |
2 | 3 | 1 | 20 | 1 | 0 |
3 | 4 | 2 | 30 | 1 | 1 |
4 | 5 | 3 | 40 | 2 | 0 |
Bitwise Expr¶
- Bitwise AND:
- numeric_expr1 & numeric_expr2
- Bitwise OR:
- numeric_expr1 | numeric_expr2
- Bitwise XOR:
- numeric_expr1 ^ numeric_expr2
- Bitwise Shift Left:
- numeric_expr1 << numeric_expr2
- Bitwise Shift Right:
- numeric_expr1 >> numeric_expr2
- Bitwise Zero Fill Shift Right:
- numeric_expr1 >>> numeric_expr2
- Bitwise NOT:
- ~ numeric_expr
See also
Example:
1 | dual(0xffff,1) | select(i,expr_and:(i & 0xff00));
|
i | expr_and |
---|---|
65535 | 65280 |
Comparison Expr¶
- Equal:
- expr1 = expr2, expr1 == expr2
- Not Equal:
- expr1 != expr2, expr1 <> expr2
- Greater:
- expr1 > expr2
- Less:
- expr1 < expr2
- Greater or Equal:
- expr1 >= expr2
- Less or Equal:
- expr1 <= expr2
See also
Example:
1 | dual | select(i = 0);
|
equal |
---|
true |
Boolean Expr¶
- And
- boolean_expr1 AND boolean_expr2
- Or
- boolean_expr1 OR boolean_expr2
- Xor
- boolean_expr1 XOR boolean_expr2
- Not
- NOT boolean_expr
See also
Example:
1 | dual | select(true XOR true);
|
logical_or |
---|
true |
Concatenate Expr¶
- String concatenation:
- string_expr1 || string_expr2 or string_expr1 + string_expr2
See also
Example:
1 | dual | select(s, s || "! Red fox jumps over lazy dog!");
|
s | concat |
---|---|
0ho0 | 0ho0! Red fox jumps over lazy dog! |
Array Expr¶
- ARRAY can be expressed as a comma-separated list of items of the same type, enclosed in square brackets:
- [ item ,… ]
Example:
1 | dual| select(array1:[], array2:[0, 1, 2]);
|
array1 | array2 |
---|---|
[] | [0, 1, 2] |
Tuple Expr¶
- TUPLE can be expressed as comma separated list of key-value pairs (separated by colon “:”) and enclosed in curly brackets:
- {name:value, …}
Example:
1 | dual | select(t1:{}, t2:{name:'Homer', age:42});
|
t1 | t2 |
---|---|
{} | {name=”Homer” age=42} |
Cast Expr¶
- Types of values can be changed using cast expression:
- CAST(expr AS type)
Note
The types can not be cast arbitrarily from one type to another. See Cast functions for details
Example:
1 | dual | select(int_val:CAST('0' as INT)) | select(int_val, TYPE(int_val));
|
int_val | type |
---|---|
0 | INTEGER |
In Expr¶
- A value can be tested of being a member of an ARRAY using IN expression:
- expr IN array
- A negated version of the above:
- expr NOT IN array
Note
The value being tested has to be the same type as the array.
Example:
1 | dual | select(1 IN [1,2,3]);
|
is_in |
---|
true |
In List Expr¶
- A value can be tested of being a member of a list using IN expression:
- expr IN (expr, …)
- The negated version of the above:
- expr NOT IN (expr, …)
Note
All members of the list have to be the same type as the value being tested.
Example:
1 | dual(3) | select(ip, ip IN (0.0.0.1, 0.0.0.2, 0.0.0.3));
|
ip | is_in |
---|---|
0.0.0.0 | false |
0.0.0.1 | true |
0.0.0.2 | true |
In Stream Expr¶
- A value can be tested of being included in the query stream using IN expression:
- expr IN (@stream)
- The negated version of the above:
- expr NOT IN (@stream)
Note
The stream has to contain only one field of the same type as the value being tested.
Example:
1 2 | @stream = dual(3) | select(ip);
dual(5) | select(ip, ip IN (@stream));
|
ip | f_2 |
---|---|
0.0.0.0 | true |
0.0.0.1 | true |
0.0.0.2 | true |
0.0.0.3 | false |
0.0.0.4 | false |
Between Expr¶
- A value can be tested of being within the range using BETWEEN expression:
- expr BETWEEN min AND max
- The negated version of the above:
- expr NOT BETWEEN min AND max
Note
the value has to be the same type as the range being tested against.
Example:
1 | dual(5) | select(i, in_range:i BETWEEN 1 AND 3);
|
i | in_range |
---|---|
0 | false |
1 | true |
2 | true |
3 | true |
4 | false |
Is Null Expr¶
- A value can be tested if it is NULL:
- expr IS NULL
- The negated version of the above:
- expr IS NOT NULL
See also
Example:
1 | dual(3) | select(str:PREV(s)) | select(str, str IS NULL);
|
str | is_null |
---|---|
NULL | true |
0ho0 | false |
1ho1 | false |
Is TRUE or FALSE Expr¶
- A result value of an Boolean returning expression can be tested if is TRUE or FALSE:
expr IS TRUE
expr IS FALSE
expr IS NOT TRUE
expr IS NOT FALSE
This differs from expr = const, expr != const by returning TRUE/FALSE even for NULL inputs.
See also
Example
1 2 3 | $test_first_bit(i) = $i & 0x01 = 1;
dual(2) | select(i, is_first_bit_set:$test_first_bit(i) is not false);
|
i | is_first_bit_set |
---|---|
0 | false |
1 | true |
NULL | true |
Like Expr¶
- SQL style pattern matching:
- string_expr LIKE pattern
Returns true if pattern matches string. If the pattern does not contain percent signs then LIKE acts as = operator. A percent character in the pattern (%) matches any sequence of zero or more characters. An underscore in the pattern (_) matches a single character.
- The negated version of the above:
- string_expr NOT LIKE pattern
Note
matching is case sensitive.
Example:
1 | dual(3) | select(s, s LIKE '%ho0')
|
s | str_like |
---|---|
0ho0 | true |
1ho1 | false |
2ho2 | false |
Regexp Expr¶
- Regular expression matching:
- string_expr REGEXP pattern
Returns true if regular expression pattern matches string_expr.
- The negated version of the above:
- string_expr NOT REGEXP pattern
Note
matching is case sensitive.
See also
Example:
1 | dual(3) | select(s, s REGEXP '.ho0');
|
s | str_regexp_like |
---|---|
0ho0 | true |
1ho1 | false |
2ho2 | false |
Contains Expr¶
- A string value can be tested if it is a substring of another string:
- string_expr1 CONTAINS string_expr2
Returns true if string_expr1 contains string_expr2. Otherwise returns false.
- The negated version of the above:
- string_expr1 NOT CONTAINS string_expr2
Note
Comparison is case sensitive.
See also
Example:
1 | dual(3) | select(s, s CONTAINS 'ho0');
|
s | contains |
---|---|
0ho0 | true |
1ho1 | false |
2ho2 | false |
If Expr¶
- A single boolean expression based decision:
- IF(boolean_expr,true_expr,else_expr)
Returns true_expr if boolean_expr evaluates to true, otherwise returns else_expr.
- A simplified version of the above, (without else choice):
- IF(boolean_expr,true_expr)
Returns true_expr if boolean_expr evaluates to true, otherwise returns NULL.
Example:
1 | dual(2) | select(s, IF(s CONTAINS '0', 'contains zero'));
|
s | if_then |
---|---|
0ho0 | contains zero |
1ho1 | NULL |
Case Expr¶
CASE WHEN boolean_expr THEN true_expr … ELSE else_expr END
Returns true_expr if boolean_expr evaluates to true, otherwise returns else_expr. If else_expr is omitted then returns NULL.
Example:
1 2 3 4 5 6 7 8 | dual(3)
| select(i, case_when:
CASE
WHEN i=1 THEN 'equals 1'
WHEN i<1 THEN 'less than 1'
ELSE 'greater than 1'
END
);
|
i | case_when |
---|---|
0 | less than 1 |
1 | equals 1 |
2 | greater than 1 |
CASE expr1 WHEN expr2 THEN true_expr … ELSE else_expr END
A simplified version of the general form above. Returns true_expr when expr2 equals to expr1, otherwise returns else_expr.
If else_expr is omitted then returns NULL.
output type: | the type returned by true_expr or else_expr (must be the same). |
---|
Example:
1 2 3 4 5 6 7 8 | dual(3)
| select(i, case_when:
CASE i
WHEN 1 THEN 'this is number 1'
WHEN 2 THEN 'this is number 2'
ELSE 'unknown number'
END
);
|
i | case_when |
---|---|
0 | unknown number |
1 | this is number 1 |
2 | this is number 2 |