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:

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

declaring user defined functions.

Example:

$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:

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:

@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:

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:

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:

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:

@[/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:

FETCH('sx:/user/files/example.txt');

Function Expr

Typed Selector Expr

1. TIMESTAMP and TIMESTAMP_NANO allow performing time arithmetic functions using expressions:

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)

2. Accessing ARRAY members

tuple['member']

3. Accessing TUPLE members

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

arithmetic operators.

Example:

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

Bitwise Functions

Example:

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

comparison functions

Example:

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

Boolean functions

Example:

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:

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:

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:

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:

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:

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:

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:

@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:

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

IS_NULL functions

Example:

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

FALSE_OR_NULL and TRUE_OR_NULL

Example

$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:

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

STR_REGEXP_LIKE(string, regexp)

Example:

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

CONTAINS function

Example:

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:

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:

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:

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