Functions

Here is a list of all functions that are currently supported in 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

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
<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'

Arrays

All array functions can be called directly on tag dimensions or array-type dimensions of a single event. For example, array_position(SEQ.name, 'trailer_start') will find the index of the first event with trailer_start event name (or null, if not found).

Operation	Logic	Example	Output
array_extract(<array>, <index>)	Extracts an item at specified index (0-based) from the array	array_extract(['a', 'b', 'c'], 1)	'b'
array_position(<array>, <item>)	Find the index (0-based) of the specified item in array. Returns null if the item is not found	array_position(['a', 'b', 'c'], 'b')	1
array_remove(<array>, <item>)	Removes all occurrences of the item/items (can be also another array) in the array	array_remove(['a', null, 'b', null], null)	['a',b']
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'
flatten(<array>)	The flattened array	flatten([[1],[2],[3]])	[1,2,3]

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

Geospatial

Geospatial functions operate on two types of input parameters:

• Points. Each point is a numeric array with two items. The first item describes longitude and the second item describes latitude in WGS84 coordinates. E.g. [-74.0, 40.7] describes the location of New York City
• Bounding boxes. A 4-element numeric array, where the first two items describe South-West corner longitude/latitude and the two last items describe North-East corner longitude/latitude. E.g. [113.3, -43.6, 153.6, -10.7] describes a bounding box for Australia.

Operation	Logic	Example	Output
geo_area(<bounding_box>)	Area of the specified bounding box in square kilometers	geo_area([10,50,11,51])	7865
geo_centroid(<bounding_box>)	Centroid point of the specified bounding box	geo_centroid([10,50,11,51])	[10.5,50.5]
geo_contains(<point>,<bounding_box>)	True if the point is contained within the bounding box	geo_contains([-74,40.7],[10,50,11,51])	false
geo_distance(<point_1>, <point_2>)	Distance between the two points in kilometers	geo_distance([-74,40.7],[0.01,51.49])	5580.4
geo_intersection(<bounding_box_1>, <bounding_box_2>)	The intersection bounding box between two specified boxes. Null if no intersection.	geo_intersection([0,0,10,10],[5,5,20,20])	[5,5,10,10]
geo_overlap(<bounding_box_1>, <bounding_box_2>)	Proportion of the area overlap between two bounding boxes. 1 indicates that bounding boxes are exactly the same and 0 - that they don't overlap at all.	geo_overlap([0,0,10,10],[5,5,20,20])	0.156

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