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:

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:

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

1. TIMESTAMP and TIMESTAMP_NANO allow performing time arithmetic functions using expressions (see available period units here):

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

arithmetic operators.

Example:

1	dual(5) \| select(i,'i+1':i+1, 'i-1':i-1, 'i10':i10, '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:

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

comparison functions

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

Boolean functions

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:

@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

IS_NULL functions

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

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:

1	dual(3) \| select(s, s LIKE '%ho0')

s	str_like
0ho0	true
1ho1	false
2ho2	false

ILike Expr¶

SQL style pattern matching:: string_expr ILIKE pattern

Case insensitive version of Like Expr.

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:

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

CONTAINS function

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:

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

Expressions¶

Constant Expr¶

Variable Expr¶

Named Column Expr¶

Positioned Column Expr¶

Column By Name Expr¶

Resource Expr¶

Fetch Expr¶

Function Expr¶

Typed Selector Expr¶

Arithmetic Expr¶

Bitwise Expr¶

Comparison Expr¶

Boolean Expr¶

Concatenate Expr¶

Array Expr¶

Tuple Expr¶

Cast Expr¶

In Expr¶

In List Expr¶

In Stream Expr¶

Between Expr¶

Is Null Expr¶

Is TRUE or FALSE Expr¶

Like Expr¶

ILike Expr¶

Regexp Expr¶

Contains Expr¶

If Expr¶

Case Expr¶

Expr Expr¶