SOL Functions

Here is a list of all functions, which are currently supported in the Sequence Operations Language (SOL).

Logical

Operation	Logic	Example	Output
<boolean 1> and <boolean 2>	AND of two values	true AND false	false
<boolean 1> or <boolean 2>	OR of two values	true OR false	true
not <boolean>	Negation of the value	NOT false	true

Relational

Operation	Logic	Example	Output
=, !=, <, >, <=, >=	Relational comparison	10 > 8	true
<value> between <value1> and <value2>	Relational comparison	'2019-01-02' between '2019-01-01' and '2019-01-03'	true

Array tags

Operation	Logic	Example	Output
[n], [n, m], [n:], [:m], [-1], [-n:-m], [-n:], [:-m]	Follows Python array slicing	SEQ[4].ts	2023-04-24 11:45:09.123
<value> in <array>	True when the value is present in the array	'foo' in ['bar', 'baz']	false
position(<tag>)	Returns index of the 1st event of a tag within a sequence	position(A)	4

String

Operation	Logic	Example	Output
array_to_string(<array>, <delimiter>)	Converts an array to a string by concatenating its elements with the specified delimiter	array_to_string(['a', 'b', 'c'], ',')	'a,b,c'
<string> like '<like pattern>'	True if the string matches LIKE pattern	'foobar' like '%ba_'	true
	("%" represents any number of characters and "_" represents any single character)
<string> similar to '<regex pattern>'	True if the regex pattern is matched in the string	'foobar' similar to 'oo[b\|c]'	true
concat(<string>, <string>, …, <string>)	Concatenates all string arguments	concat('a', 'b', 'c')	'abc'
strlen(<string>)	Length of the input string	strlen('hello world')	12
edit_distance(<string1>, <string2>)	Levenshtein edit distance between the two strings	edit_distance('hello', 'Jelly')	2
regex_count(<string>, <pattern>)	Number of times the regex appears in the string	regex_count('code line', 'e')	2
regex_substr(<string>, <pattern>)	Extracts the first substring that matches the pattern	regex_substr('ABC123', '[A-Z][0-9]')	C1
regex_replace(<string>, <pattern>, <replacement>)	Replaces every substring that matches the pattern	regex_replace('ABC123', '([A-Z])', 'x$1')	xAxBxCx123
lower(<string>)	Lowercase value of the input string	lower('Hello World')	hello world
upper(<string>)	Uppercase value of the input string	upper('Hello World')	HELLO WORLD

Regex patterns in Motif follow ECMA regular expression rules.

Mathematical

Operation	Logic	Example	Output
+, -, \*, /, ^	Arithmetic operation	4 + 3	7
abs(<value or array>)	Absolute value	abc(-4)	4
ceiling(<value or array>)	Ceiling of the value	ceiling(4.4)	5
floor(<value or array>)	Floor of the value	floor(4.4)	4
log(<value or array>)	Log_10 of the value	log(100)	`2``
round(<value or array>)	Round number of the value	round(4.4)	4
rand(<optional max>)	Random number in the range of 0.0 to 1.0	rand()	0.572

Aggregations

Operation	Logic	Example	Output
all(<array>)	True if all elements are non-null	all(['foo', null])	false
any(<array>)	True if any element is non-null	any(['foo', null])	true
min(<array>)	The smallest argument value	min([100,70,10])	10
max(<array>)	The largest argument value	max([100,70,10])	100
avg(<array>)	The average of elements, including nulls	avg([100,70,10])	60
length(<array>)	The length of the array or tag	length([100,70,10])	3
sum(<array>)	The sum of all elements	sum([100,70,10])	180
unique(<array>)	The unique elements of the provided array	unique(['a','b','a'])	['a','b']

Conditional

Operation	Logic	Example	Output
if(<boolean expression>, <value if true>, <value if false>)	The specific value depending if the condition is true or false	if(length(SEQ) > 20, 'long', 'short')	'long'

Time

All time functions can be called directly on tags, for example, datetime(A), which is equivalent to calling them on the .ts event dimension - datetime(A.ts).

Operation	Logic	Example	Output
duration literals	Number with the time granularity suffix. Serves as a shorthand for numbers in microseconds. Supported suffixes: us (microsecond), ms (millisecond), s (second), m or min (minute), h (hour), d (day), w (week), y (year).	5.5s	5500000
date(<timestamp or datetime_string>)	String in the format YYYY-MM-DD	date(SEQ[0])	'2023-04-21'
datepart( <datepart_value>, <timestamp or datetime_string> )	Supports the same date parts as SQL server: year, quarter, month, month_name, day, dayofyear, week, weekday, weekday_name, hour, minute, second, millisecond	datepart(hour, SEQ[0])	10
datetime( <timestamp or datetime_string> )	A string in the format YYYY-MM-DD HH:MM:SS.XXX. Granularity below seconds is optional depending on input.	datetime( SEQ[0].ts )	'2023-04-21 10:40:02.817'
duration( <tag1>, <tag2> )	Shorthand for <tag2>[-1].s - <tag1>[0].ts	duration(SEQ[0], SEQ[4])	'35s'
now()	Current timestamp during query execution	now()	2023-05-24 15:41:23.825
time(<timestamp or datetime_string>)	String in the format HH:MM:SS.XXX. Granularity below seconds is optional depending on input.	time(SEQ[0].ts)	'10:40:02.817'
time_bucket( <bucket>, <timestamp or datetime_string> )	Timestamp truncated to a specified granularity bucket: ms, s, m, h, d, w, month, q, y.	time_bucket(1q, SEQ[0])	2023-04-01 00:00:00.000

Miscellaneous

Operation	Logic	Example	Output
coalesce(<value 1>, <value 2>, …)	The first non-null, non-empty argument	coalesce(null, 'abc', 'foo')	abc
cast(<value> AS <data type>)	The value casted to a specific data type: number, string, boolean, timestamp (unix epoch number in microseconds), duration (number representing interval in microseconds)	cast('123' as number)	123