Skip to main content

Functions

Here is a list of all functions that are currently supported in SOL.

Logical

OperationLogicExampleOutput
<boolean 1> and <boolean 2>AND of two valuestrue AND falsefalse
<boolean 1> or <boolean 2>OR of two valuestrue OR falsetrue
not <boolean>Negation of the valueNOT falsetrue

Relational

OperationLogicExampleOutput
=, !=, <, >, <=, >= Relational comparison10 > 8true
<value> between <value1> and <value2>Relational comparison'2019-01-02' between '2019-01-01' and '2019-01-03'true

Tags

OperationLogicExampleOutput
[n], [n, m], [n:], [:m], [-1], [-n:-m], [-n:], [:-m]Follows Python array slicingSEQ[4].ts2023-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 sequenceposition(A)4

String

OperationLogicExampleOutput
<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 argumentsconcat('a', 'b', 'c')'abc'
strlen(<string>)Length of the input stringstrlen('hello world')12
edit_distance(<string1>, <string2>)Levenshtein edit distance between the two stringsedit_distance('hello', 'Jelly')2
regex_count(<string>, <pattern>)Number of times the regex appears in the stringregex_count('code line', 'e')2
regex_substr(<string>, <pattern>)Extracts the first substring that matches the patternregex_substr('ABC123', '[A-Z][0-9]')C1
regex_replace(<string>, <pattern>, <replacement>)Replaces every substring that matches the patternregex_replace('ABC123', '([A-Z])', 'x$1')xAxBxCx123
lower(<string>)Lowercase value of the input stringlower('Hello World')hello world
upper(<string>)Uppercase value of the input stringupper('Hello World')HELLO WORLD

Regex patterns in Motif follow ECMA regular expression rules.

Mathematical

OperationLogicExampleOutput
+, -, \*, /, ^Arithmetic operation4 + 37
abs(<value or array>)Absolute valueabc(-4)4
ceiling(<value or array>)Ceiling of the valueceiling(4.4)5
floor(<value or array>)Floor of the valuefloor(4.4)4
log(<value or array>)Log_10 of the valuelog(100)`2``
round(<value or array>)Round number of the valueround(4.4)4
rand(<optional max>)Random number in the range of 0.0 to 1.0rand()0.572

Aggregations

OperationLogicExampleOutput
all(<array>)True if all elements are non-nullall(['foo', null])false
any(<array>)True if any element is non-nullany(['foo', null])true
min(<array>)The smallest argument valuemin([100,70,10])10
max(<array>)The largest argument valuemax([100,70,10])100
avg(<array>)The average of elements, including nullsavg([100,70,10])60
length(<array>)The length of the array or taglength([100,70,10])3
sum(<array>)The sum of all elementssum([100,70,10])180
unique(<array>)The unique elements of the provided arrayunique(['a','b','a'])['a','b']

Conditional

OperationLogicExampleOutput
if(<boolean expression>, <value if true>, <value if false>)The specific value depending if the condition is true or falseif(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).

OperationLogicExampleOutput
array_extract(<array>, <index>)Extracts an item at specified index (0-based) from the arrayarray_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 foundarray_position(['a', 'b', 'c'], 'b')1
array_remove(<array>, <item>)Removes all occurrences of the item/items (can be also another array) in the arrayarray_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 delimiterarray_to_string(['a', 'b', 'c'], ',')'a,b,c'
flatten(<array>)The flattened arrayflatten([[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).

OperationLogicExampleOutput
duration literalsNumber 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.5s5500000
date(<timestamp or datetime_string>)String in the format YYYY-MM-DDdate(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, milliseconddatepart(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].tsduration(SEQ[0], SEQ[4])'35s'
now()Current timestamp during query executionnow()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.
OperationLogicExampleOutput
geo_area(<bounding_box>)Area of the specified bounding box in square kilometersgeo_area([10,50,11,51])7865
geo_centroid(<bounding_box>)Centroid point of the specified bounding boxgeo_centroid([10,50,11,51])[10.5,50.5]
geo_contains(<point>,<bounding_box>)True if the point is contained within the bounding boxgeo_contains([-74,40.7],[10,50,11,51])false
geo_distance(<point_1>, <point_2>)Distance between the two points in kilometersgeo_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

OperationLogicExampleOutput
coalesce(<value 1>, <value 2>, …)The first non-null, non-empty argumentcoalesce(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