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

Tuple Expr and Variable Expr

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 ,…)

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)

Note

If the URI refers to any defined data store then access to its target is subject to the data store’s read 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

  1. 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)
  1. Accessing ARRAY members
1
tuple['member']
  1. 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

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

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

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

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

string functions.

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

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.

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.

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.

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.

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

Expr Expr