Built-in SQL Functions
The rule engine proffers a variety of built-in functions. You can utilize these functions within SQL to accomplish basic data processing, encompassing Mathematical, Data Type Judgment,Data Type Conversion, String Operations, Map Operations, Array Operations, Hashing, Compression and Decompression, Bit Operations, Bit Sequence Operations, Encoding and Decoding, as well as Date and Time Conversion.
In this section, all function declarations conform to the following format:
FuncName(Arg 1: Type 1 | ..., ...) -> Type 1 | ...For instance, abs(X: integer | float) -> integer | float implies that the data type of argument X can be either integer or float, and correspondingly, the return value's data type can also be integer or float.
Be aware that if the provided argument exceeds the stipulated range or employs an unsupported data type, it will result in the current SQL execution failing, incrementing the failure count by one.
TIP
Since EMQX 5.0 version, EMQX also supports using jq Syntax for complex data transformation, you may read the jq Fucntion section for more information.
Mathematical Functions
EMQX supports a wide range of mathematical functions:
- Trigonometric and hyperbolic functions, include sin, cos, tan, asin, acos, atan, sinh, cosh, tanh, asinh, acosh, atanh.
- Numerical functions, include abs, ceil, floor, round, sqrt, fmod.
- Exponential and logarithmic functions, include exp, power, log, log10, log2.
abs(X: integer | float) -> integer | float
Returns the absolute value of number X. Example:
abs(-12) = 12
abs(-1.2) = 1.2acos(X: integer | float) -> float
Returns the arc cosine of X, expressed in radians. The range of X is [-1, 1]. Example:
acos(0.5) = 1.0471975511965976acosh(X: integer | float) -> float
Returns the hyperbolic arccosine of X, expressed in radians. X must be greater than or equal to 1. Example:
acosh(1.5) = 0.9624236501192069asin(X: integer | float) -> float
Returns the arc sine of X, expressed in radians. The range of X is [-1, 1]. Example:
asin(0.5) = 0.5235987755982988asinh(X: integer | float) -> float
Returns the hyperbolic arcsine of X. Example:
asinh(0.5) = 0.48121182505960347atan(X: integer | float) -> float
Returns the arc tangent of X, expressed in radians. Example:
atan(0.5) = 0.46364760900080615atanh(X: integer | float) -> float
Returns the hyperbolic arctangent of X, where X ranges between (-1, 1). Example:
atanh(0.5) = 0.5493061443340549ceil(X: integer | float) -> integer
Rounds upward, yielding the smallest integer greater than or equal to the given X. Example:
ceil(0.8) = 1cos(X: integer | float) -> float
Returns the cosine of the angle X expressed in radians. Example:
cos(0.5) = 0.8775825618903728cosh(X: integer | float) -> float
Returns the hyperbolic cosine of X. Example:
cosh(0.5) = 1.1276259652063807exp(X: integer | float) -> float
Returns the natural number e to the power of X, i.e., e^X. Example:
exp(1) = 2.718281828459045floor(X: integer | float) -> integer
Returns the largest integer less than or equal to the given X. Example:
floor(3.6) = 3fmod(X: integer | float, Y: integer | float) -> float
Returns the remainder of X divided by Y as a floating-point number. Example:
fmod(6.5, 2.5) = 1.5log(X: integer | float) -> float
Returns the natural logarithm of the number X, where X must be greater than 0. Example:
log(7.38905609893065) = 2.0log10(X: integer | float) -> float
Returns the logarithm base 10 of the number X, where X must be greater than 0. Example:
log10(100) = 2.0log2(X: integer | float) -> float
Returns the logarithm base 2 of the number X, where X must be greater than 0. Example:
log2(8) = 3.0
log2(8.5) = 3.0874628412503395round(X: integer | float) -> integer
Rounds the number X to the nearest integer. Example:
round(4.5) = 5power(X: integer | float, Y: integer | float) -> float
Returns X to the power of Y, i.e., X^Y. Example:
power(2, 3) = 8.0random() -> float
Returns a random floating-point number in the range [0, 1). Example:
random() = 0.5400050092601868sin(X: integer | float) -> float
Returns the sine of angle X, expressed in radians. Example:
sin(0.5) = 0.479425538604203sinh(X: integer | float) -> float
Returns the hyperbolic sine of X. Example:
sinh(0.5) = 0.5210953054937474sqrt(X: integer | float) -> float
Returns the square root of the number X. Example:
sqrt(9) = 3.0tan(X: integer | float) -> float
Returns the tangent of angle X (expressed in radians). Example:
tan(0.5) = 0.5463024898437905tanh(X: integer | float) -> float
Returns the hyperbolic tangent of X. Example:
tanh(0.5) = 0.46211715726000974Data Type Judgment Functions
Data type judgment functions can be used to check the data type of a specified field and indicate whether the field conforms to the specified data type through a boolean value.
is_array(Term: any) -> boolean
'any' signifies all data types.
Determine whether Term is of array type. Example:
is_array([1, 2]) = true
is_array(json_decode('[{"value": 1}]')) = true
is_array(json_decode('{"value": 1}')) = false
is_array(0.5) = false
is_array('[1, 2]') = falseis_bool(Term: any) -> boolean
Determine whether Term is of boolean type. Example:
is_bool(true) = true
is_bool(false) = false
is_bool('true') = falseis_float(Term: any) -> boolean
Determine whether Term is of float type. Example:
is_float(123.4) = true
is_float(123) = falseis_int(Term: any) -> boolean
Determine whether Term is of integer type. Example:
is_int(123) = true
is_int(123.4) = falseis_map(Term: any) -> boolean
Determine whether Term is of map type. Example:
is_map(json_decode('{"value": 1}')) = true
is_map(json_decode('[{"value": 1}]')) = falseis_null(Term: any) -> boolean
Determine whether the variable Term is undefined. Example:
is_null(this_is_an_unassigned_variable) = true
is_null(map_get('b', json_decode('{"a": 1}'))) = trueis_num(Term: any) -> boolean
Determine whether Term is of integer or float type. Example:
is_num(123) = true
is_num(123.4) = true
is_num('123') = falseis_str(Term: any) -> boolean
Determine whether Term is of string type. Example:
is_str('123') = true
is_str(123) = falseData Type Conversion Functions
bool(Term: boolean | integer | string) -> boolean
Convert Term to a boolean. Term can only be boolean type, integer type with 0 and 1, or string type with true and false.
Example:
# Correct
bool(true) = true
bool(0) = false
bool('false') = false
# Wrong
bool(20)
bool('True')float(Term: float | integer | string) -> float
Convert Term to a float.
if the type of Term is string, scientific notation can be used, such as float('3.14e4'). The float type supports up to 16 significant digits. When the valid digits of the floating-point number represented by the string 'Term' exceed 16, rounding errors may occur in the conversion.
Example:
float(20) = 20.0
float('3.14') = 3.14
float('3.14e4') = 31400
float('3.14e+4') = 31400
float('3.14e-4') = 0.000314
float('3.14E-4') = 0.000314
# Once the significant digits exceed 16, due to rounding errors, disparate inputs may yield identical outputs.
float('0.12345678901234566') = 0.12345678901234566
float('0.12345678901234567') = 0.12345678901234566float(Term: float | integer | string, Decimals: integer) -> float
Convert Term to a floating-point number containing at most Decimals digits after the decimal point, with the range of Decimals being (0, 253]. The other behavior is the same as float/1. Example:
float('3.1415926', 3) = 3.142
float('0.000012345', 5) = 0.00001float2str(Float: float, Decimals: integer) -> string
Convert the floating-point number Float to a string, at most containing Decimals digits following the decimal point, with trailing zeros being truncated. The range for Decimals is [0, 253]. If the significant digits of Float exceed 16, rounding errors may occur during the conversion.
Since floating-point numbers cannot be stored precisely in computers, when Decimals is greater than the number of decimal places in Float (including leading zeros), float2str may return a decimal representation of the binary approximation of Float.
Example:
float2str(0.1, 5) = '0.1'
float2str(0.1, 20) = '0.10000000000000000555'
float2str(0.1, 25) = '0.1000000000000000055511151'
float2str(0.00000000001, 20) = '0.00000000001'
# trailing zeros will be truncated
float2str(0.100001, 5) = '0.1'
# Once the significant digits exceed 16, due to rounding errors, disparate inputs may yield identical outputs.
float2str(123456789.01234565, 8) = '123456789.01234566'
float2str(123456789.01234566, 8) = '123456789.01234566'int(Term: boolean | float | integer | string) -> integer
Convert Term into an integer.
When Term is a boolean, true will be converted to the number 1, and false will be converted to the number 0.
When Term is a float, Term will be rounded down, converting it into the largest integer less than or equal to Term.
When Term is a string, Term must contain at least one numerical character, can possess an optional prefix composed of a single + or - character, and leading zeros will be disregarded. Mathematical notation representation is supported.
When Term is an integer, Term will be returned as is.
Example:
# Correct
int(true) = 1
int(3.14) = 3
int(-3.14) = 4
int('-100') = -100
int('+200') = 200
int('0010') = 10
int('3.1415e2') = 314
int(substr('Number 100', 7)) = 100
# Wrong
int('-100+200')
int('Number 100')str(Term: any) -> string
Convert any type of Term into a string.
When Term is a map or array, the str function will attempt encode Term using JSON.
When Term is a float, the str function will return the corresponding string, truncating any zeros at the end. The returned string will house a maximum of 10 digits post decimal point. To return more decimal places, please use the float2str function.
Example:
str(100) = '100'
str(nth(1, json_decode('[false]'))) = 'false'
str(json_decode({"msg": "hello"})) = '{"msg":"hello"}'
str(json_decode('[{"msg": "hello"}]')) = '[{"msg":"hello"}]'
# Trailing zeros are truncated
str(0.300000004) = '0.3'
# Contains at most 10 number of digits past the decimal point
str(3.14159265359) = '3.1415926536'
str(0.000000314159265359) = '0.0000003142'String Operation Functions
String functions can be used for case transformations, space removal, substring extraction, replacement, and other operations.
ascii(Char: string) -> integer
Returns the ASCII code corresponding to character Char. If Char contains multiple characters, only the code for the first character is returned. Example:
ascii('a') = 97
ascii('abc') = 97concat(Str1: string, Str2: string) -> string
Concatenates Str1 and Str2 into a single string. Example:
concat('Name:', 'John') = 'Name:John'find(String: string, SearchPattern: string) -> string
Searches for the substring SearchPattern in String, deletes all content before SearchPattern in String, and returns the remaining part of the string. If SearchPattern is not found, an empty string will be returned. This function is equivalent to find(String, SearchPattern, 'leading').
Example:
find('..., Value: 1.2', 'Value:') = 'Value: 1.2'
find('..., Value: 1.2', 'Data') = ''find(String: string, SearchPattern: string, Direction: string) -> string
Same as find/2, but allows the specification of the direction of the search for the substring SearchPattern using Direction. Example:
find('Front, Middle, End', ', ', 'leading') = ', Middle, End'
find('Front, Middle, End', ', ', 'trailing') = ', End'lower(String: string) -> string
Converts uppercase letters in the string String to lowercase. Example:
lower('Hello') = 'hello'ltrim(String: string) -> string
Same as trim/1, but only removes leading whitespace characters from the String. Example:
ltrim('\t hello \n') = 'hello \n'
ltrim('\t hello \r\n') = 'hello \r\n'pad(String: string, Length: integer) -> string
Pads a String with trailing spaces to the specified length. Example:
pad('hello', 8) = 'hello 'pad(String: string, Length: integer, Direction: string) -> string
Same as pad/2, but you can use Direction to specify the direction of padding. leading means filling leading spaces, trailing means filling trailing spaces, and both means filling both leading and trailing spaces.
When specifying Direction as both, if the number of spaces to be filled is an odd number, the last space will be filled at the end.
Example:
pad('hello', 8, 'leading') = ' hello'
pad('hello', 8, 'trailing') = 'hello '
pad('hello', 8, 'both') = ' hello 'pad(String: string, Length: integer, Direction: string, Char: string) -> string
Same as pad/3, but can be padded with the specified grapheme cluster Char.
Since the rule engine does not check whether Char is a legal grapheme cluster, Char will be processed as one character length no matter how many characters it contains. Example:
pad('hello', 8, 'trailing', '!') = 'hello!!!'
pad('hello', 8, 'trailing', '\r\n') = 'hello\r\n\r\n\r\n'
pad('hello', 8, 'trailing', 'abc') = 'helloabcabcabc'regex_match(String: string, Expression: string) -> boolean
Determine whether the string Stringmatches the regular expression Expression. Example:
regex_match('123', '^\d+$') = true
regex_match('a23', '^\d+$') = falseregex_replace(String: string, Expression: string, Replacement: string) -> string
Use string Replacement to replace the portion of String that matches the regular expression Expression. If no matching part is found, the original String will be returned. Example:
regex_replace('hello 123', '\d+', 'world') = 'hello world'
regex_replace('a;b; c', ';\s*', ',') = 'a,b,c'replace(String: string, SearchPattern: string, Replacement: string) -> string
Replaces all SearchPatterns in String with Replacement. Example:
replace('ab..cd..ef', '..', '**') = 'ab**cd**ef'
replace('ab..cd..ef', '..', '') = 'abcdef'replace(String: string, SearchPattern: string, Replacement: string, Where: string) -> string
Replaces occurrences of SearchPattern in String with Replacement.
Where has the following possible values:
all: Replace allSearchPatterns, equivalent toreplace/3.leading: Replaces only the leadingSearchPattern.trailing: Replace only the trailingSearchPattern.
Example:
replace('ab..cd..ef', '..', '**', 'all') = 'ab**cd**ef'
replace('ab..cd..ef', '..', '**', 'leading') = 'ab**cd..ef'
replace('ab..cd..ef', '..', '**', 'trailing') = 'ab..cd**ef'reverse(String: string) -> string
Reverse a string. Example:
reverse('hello') = 'olleh'rtrim(String: string) -> string
Same as trim/1, but only removes trailing whitespace characters from the String. Example:
rtrim('\t hello \n') = '\t hello'
rtrim('\t hello \r\n') = '\t hello'split(String: string, Separator: string) -> array
Splits a String into substrings using Separator and returns an array of these substrings.
Two or more adjacent Separators are not treated as one, so the split result may contain empty strings. split/2 trims the output results by default and filters out the empty strings. If you want to remain them, please use split(String, Separator, 'notrim').
Separator can be composed of multiple characters, but they will be treated as a whole. If you want to specify multiple delimiting characters at once, please use the tokens function.
Example:
split('a;', ';') = ['a']
split('a;b;c', ';') = ['a', 'b', 'c']
split('a;;b;;c', ';') = ['a', 'b', 'c']
# Note the space before Howell Wise
split('Sienna Blake; Howell Wise', ';') = ['Sienna Blake', ' Howell Wise']
split('Sienna Blake; Howell Wise', '; ') = ['Sienna Blake', 'Howell Wise']split(String: string, Separator: string, Option: string) -> array
Same as split/2, but you can use Option to specify the position of the delimiter that needs to be processed, and whether the empty string needs to be returned.
Option has the following possible values:
notrim:handles all delimiters in the string, and the returned result may contain empty strings.leading: Only the leading delimiter is processed, and the returned result does not contain empty strings.leading_notrim: Only the leading delimiter is processed, and the returned result may contain an empty string.trailing: Only the trailing delimiter is processed, and the returned result does not contain the empty string.trailing_notrim: Only the trailing delimiter is processed, and the returned result may contain an empty string.
Example:
split('a;;b;;c', ';', 'notrim') = ['a', '', 'b', '', 'c']
split('a;b;c', ';', 'leading') = ['a', 'b;c']
split('a;b;c', ';', 'trailing') = ['a;b', 'c']
split(';a;b;c', ';', 'leading_notrim') = ['', 'a;b;c']
split('a;b;c;', ';', 'trailing_notrim') = ['a;b;c', '']sprintf(Format, ...) -> string
Returns a string formatted according to Format. The Format string contains ordinary characters and control sequences used for formatting.
The format of the control sequence is generally: ~F.P.PadModC.
Character C determines the type of control sequence to use. This is the only required field. F, P, Pad and Mod are all optional. For a detailed introduction to them, see: https://www.erlang.org/doc/man/io.html#fwrite-1.
Example:
sprintf('hello, ~s!', 'steve') = 'hello, steve!'
sprintf('count: ~p~n', 100) = 'count: 100\n'strlen(String: string) -> integer
Returns the length of String. Example:
strlen('hello') = 5
strlen('hello\n') = 6substr(String: string, Start: integer) -> string
Returns all characters in String starting from position Start to the end of the string. The subscript of the string starts from 0, that is, position 0 corresponds to "h" in the string "hello". Example:
substr('hello', 0) = 'hello'
substr('hello world', 6) = 'world'substr(String: string, Start: integer, Length: integer) -> string
Returns the substring starting from position Start in String and having a maximum length of Length. The subscript of the string starts from 0. Example:
substr('hello world!', 6, 5) = 'world'tokens(String: string, SeparatorList: string) -> array
Returns a list of substrings of String split by the characters in SeparatorList.
Two or more adjacent delimiters will be treated as one, so no empty string will occur.
Example:
tokens('a,b;c,d', ',;') = ['a', 'b', 'c', 'd']
tokens('a;;b', ';') = ['a', 'b']tokens(String: string, SeparatorList:string, NoCRLF: string) -> array
Same as tokens/2, but you can specify NoCRLF as nocrlf to split carriage return and line feed characters at the same time. Example:
tokens('a\rb\nc\r\nd', ';', 'nocrlf') = ['a', 'b', 'c', 'd']trim(String: string) -> string
Removes leading and trailing characters from a String that should be considered whitespace, such as spaces, tabs, form feeds, and newline characters. Note that \r\n is considered a grapheme cluster in the Unicode standard, so \r\n will be deleted altogether. Example:
trim('\t hello \n') = 'hello'
trim('\t hello \r\n') = 'hello'upper(String: string) -> string
Converts lowercase letters in a String to uppercase letters. Example:
upper('hello') = 'Hello'Map Operation Functions
map_get(Key: string, Map: map) -> any
Returns the value of the specified Key in the Map, or undefined if the Key does not exist in the Map. Example:
map_get('msg', json_decode('{"msg": "hello"}')) = 'hello'
map_get('data', json_decode('{"msg": "hello"}')) = undefinedmap_get(Key: srting, Map: map, Default: any) -> any
Same as map_get/2, but when Key does not exist, the specified Default will be returned. Example:
map_get('data', json_decode('{"msg": "hello"}'), '') = ''
map_get('value', json_decode('{"data": [1.2, 1.3]}'), []) = []map_put(Key: string, Value: any, Map: map) -> map
Insert the Key and associated Value into the Map and return the updated map. If the Key already exists in the original Map, the old associated value will be replaced with the new Value. Example:
map_get('b', map_put('b', 1, json_decode('{"a": 1}'))) = 1
map_get('a', map_put('a', 2, json_decode('{"a": 1}'))) = 2mget(Key: string | array, Map: map) -> any
Returns the value of the specified Key in the Map, or undefined if the Key does not exist in the Map. You can use an array to specify multiple keys at once to get associated values from a nested map. Example:
mget('c', json_decode('{"a": {"b": 1}}')) = undefined
json_decode(mget('a', json_decode('{"a": {"b": 1}}'))) = '{"b": 1}'
mget(['a', 'b'], json_decode('{"a": {"b": 1}}')) = 1mput(Key: string | array, Value: any, Map: map) -> map
Insert the Key and associated Value into the Map and return the updated map. If the Key already exists in the original Map, the old associated value will be replaced with the new value. You can use an array to specify multiple keys at once to insert data into a nested map. Example:
mget(['a', 'b'], mput(['a', 'b'], 2, json_decode('{"a": {"b": 1}}'))) = 2
mget(['a', 'b'], mput(['a', 'b'], 2, json_decode('{"c": 1}'))) = 2Array Operation Functions
contains(Item: any, Array: array) -> boolean
Determine whether the array Array contains the specified Item. Example:
contains(2, [1, 2, 3]) = true
contains(2.3, [1.8, 2.5, 2.0]) = false
contains('John', ['John', 'David']) = true
contains([1, 2], [a, b, [1, 2]]) = true
contains(json_decode('{"a": 1}'), [json_decode('{"a": 1}'), json_decode('{"b": 2}')]) = truefirst(Array: array) -> any
Returns the first element in the array Array. Array cannot be empty. Example:
# Correct
first(['John', 'David']) = 'John'
# Wrong
first([])last(Array: array) -> any
Returns the last element in the array Array. Array cannot be empty. Example:
# Correct
last(['John', 'David']) = 'David'
# Wrong
last([])length(Array: array) -> integer
Returns the length of the array Array, that is, the number of elements in the Array. Example:
length([1,2,3,4]) = 4
length([]) = 0nth(N: integer, Array: array) -> any
Returns the Nth element in Array. N should not be larger than the length of Array. Example:
# Correct
nth(1, [1,2,3]) = 1
# Wrong
nth(0, [1,2,3])
nth(4, [1,2,3])sublist(Length: integer, Array: array) -> any
Returns a subarray starting from the 1st element in the array Array and having a maximum length of Length. If Length is greater than the length of Array, the entire array will be returned. Example:
sublist(3, [1,2,3,4]) = [1,2,3]
sublist(10, [1,2,3,4]) = [1,2,3,4]sublist(Start: integer, Length: integer, Array:array) -> any
Same as sublist/2, but you can use Start to specify which element to start returning from. If Start + Length is greater than the length of Array, the entire array will be returned. Example:
sublist(2, 10, [1,2,3,4]) = [2,3,4]Hashing Functions
md5(String: string) -> string
Computes an MD5 hash value of a fixed length of 128 bits for a String of any length. The hash value will be returned as text consisting of 32 hexadecimal digits. The letters in the returned string are fixed to lowercase (a ~ f).
Example:
md5('hello') = '5d41402abc4b2a76b9719d911017c592'sha(String: string) -> string
Computes a SHA hash value of a fixed length of 160 bits for a String of any length using the SHA-1 algorithm. The hash value will be returned as text consisting of 40 hexadecimal digits. The letters in the returned string are fixed to lowercase (a ~ f).
Example:
sha('hello') = 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d'sha256(String: string) -> string
Computes a SHA hash value of a fixed length of 256 bits for a String of any length using the SHA-2 algorithm. The hash value will be returned as text consisting of 64 hexadecimal digits. The letters in the returned string are fixed to lowercase (a ~ f).
Example:
sha256('hello') = '2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824'Compression and Decompression Functions
Note: Binary data cannot be JSON encoded directly, you must call the bin2hexstr function to convert it into the corresponding string composed of hexadecimal digits.
gunzip(Data: binary) -> binary | string
To decompress Data, Data must contain a gz header and a checksum at the end. Example:
gunzip(hexstr2bin('1F8B0800000000000013CB48CDC9C9070086A6103605000000')) = 'hello'gzip(Data: binary | string) -> binary
Use the DEFLATE algorithm to compress Data, and the returned compression result includes the gz header and the checksum at the tail. Example:
bin2hexstr(gzip('hello')) = '1F8B0800000000000013CB48CDC9C9070086A6103605000000'unzip(Data: binary) -> binary | string
Decompress Data. Data should not contain the zlib header and the checksum at the tail. Example:
unzip(hexstr2bin('CB48CDC9C90700')) = 'hello'zip(Data: binary | string) -> binary
Use the DEFLATE algorithm to compress Data, and the returned compression result does not include the zlib header and the checksum at the tail. Example:
bin2hexstr(zip('hello')) = 'CB48CDC9C90700'zip_compress(Data: binary | string) -> binary
Use the DEFLATE algorithm to compress Data. The returned compression result contains the zlib header and the checksum at the tail. Example:
bin2hexstr(zip_compress('hello')) = '789CCB48CDC9C90700062C0215'zip_uncompress(Data: binary) -> binary | string
To decompress Data, Data must contain a zlib header and a checksum at the end. Example:
zip_uncompress(hexstr2bin('789CCB48CDC9C90700062C0215')) = 'hello'Bit Operation Functions
bitand(Num1: integer, Num2: integer) -> integer
Returns the bitwise AND result of Num1 and Num2. Both input and output are signed integers. Example:
bitand(10, 8) = 8
bitand(-10, -8) = -16bitnot(Num: integer) -> integer
Returns the bitwise negation result of Num. Both input and output are signed integers. Example:
bitnot(10) = -11
bitnot(-12) = 11bitsl(Num: integer, Shift: integer) -> integer
Shift Num bitwise to the left by Shift bits, filling the right margin with 0. Example:
bitsl(8, 2) = 32
bitsl(-8, 2) = -32bitsr(Num: integer, Shift: integer) -> integer
Shift Num to the right by Shift bits, and fill the left blank with the sign bit (that is, 0 for positive numbers and 1 for negative numbers). Example:
bitsr(8, 2) = 2
bitsr(8, 4) = 0
bitsr(-8, 2) = -2
bitsr(-8, 6) = -1bitor(Num1: integer, Num2: integer) -> integer
Returns the bitwise OR result of Num1 and Num2. Example:
bitor(10, 8) = 10
bitor(-10, -8) = -2bitxor(Num1: integer, Num2: integer) -> integer
Returns the bitwise XOR result of Num1 and Num2. Example:
bitxor(10, 8) = 2
bitxor(-10, -8) = 14Bit Sequence Operation Functions
The rule engine provides functions for manipulating bit sequences. For example subbits is used to extract a sequence of bits and convert it to a specified data type.
TIP
The binary type represents a byte sequence, each byte consists of 8 bits, so the number of bits in any binary must be an integer multiple of 8. The bitstring type represents a bit sequence, which can consist of any number of bits.
Put simply, while every binary is a bitstring, the reverse is not always true.
It's important to note that bitstring, when its length is not divisible by 8, is not directly serializable to external formats like JSON.
Typically, it serves as an intermediate value before being converted to an integer or other suitable types.
bitsize(Bin: binary) -> integer
Returns the number of bits in the bit sequence Bin. Example:
bitsize('abc') = 24
bitsize('你好') = 48byteszie(Bin: binary) -> integer
Returns the number of bytes in the byte sequence Bin. Example:
byteszie('abc') = 3
byteszie('你好') = 6subbits(Bin: binary, BitNum: integer) -> integer
Starting from the starting position of the byte sequence Bin, obtain the bits of length BitNum and convert them into unsigned integers according to the big-endian sequence. This function is equivalent to subbits(Bytes, 1, BitNum, 'integer', 'unsigned', 'big').
Example:
# 159 = 0x9F
subbits(hexstr2bin('9F4E58'), 8) = 159
# 40782 = 0x9F4E
subbits(hexstr2bin('9F4E58'), 16) = 40782
# bin2hexstr(base64_decode('n05Y')) = '9F4E58'
subbits(base64_decode('n05Y'), 8) = 159subbits(Bin: binary, Start: integer, BitNum: integer) -> integer
Starting from the position Start of the byte sequence Bin (the starting position is 1), obtain the bits with a length of BitNum and convert them into an unsigned integer according to the big-endian sequence. This function is equivalent to subbits(Bytes, Start, BitNum, 'integer', 'unsigned', 'big').
Example:
# 159 = 0x9F
subbits(hexstr2bin('9F4E58'), 1, 8) = 159
# 78 = 0x4E
subbits(hexstr2bin('9F4E58'), 9, 8) = 78
# bin2hexstr(base64_decode('n05Y')) = '9F4E58'
subbits(base64_decode('n05Y'), 9, 4) = 4subbits(Bin: binary, Start: integer, BitNum: integer, OutputType: string, Signedness: string, Endianness: string) -> bitstring | integer | float
Starting from position Start of the byte sequence Bin (the starting position is 1), obtain the bits of length BitNum, and in accordance with the requested byte order, Endianness, and sign attribute, Signedness, convert them into data of the specified type OutputType.
Possible values for OutputType are:
- bits: abbreviation of bitstring
- integer
- float
Possible values for Signedness are:
- signed
- unsigned
Possible values for Endianness are:
- big
- little
Note that when OutputType is float, the parameter Signedness does not take effect. When OutputType is bits, the parameters Signedness and Endianness do not take effect.
Example:
# 40782 = 0x9F4E
subbits(hexstr2bin('9F4E58'), 1, 16, 'integer', 'unsigned', 'big') = 40782
subbits(hexstr2bin('9F4E58'), 1, 16, 'integer', 'signed', 'big') = -24754
# 20127 = 0x4E9F
subbits(hexstr2bin('9F4E58'), 1, 16, 'integer', 'unsigned', 'little') = 20127
subbits(hexstr2bin('9F4E58'), 1, 16, 'float', 'unsigned', 'big') = -0.00713348388671875
subbits(hexstr2bin('9F4E58'), 1, 16, 'float', 'signed', 'big') = -0.00713348388671875Encoding and Decoding Functions
base64_decode(Data: string) -> bytes | string
Encode Data to base64 format. Example:
base64_decode('aGVsbG8=') = 'hello'
bin2hexstr(base64_decode('y0jN')) = 'CB48CD'base64_encode(Data: binary | string) -> string
Decode Data from base64 format. Example:
base64_encode('hello') = 'aGVsbG8='
base64_encode(hexstr2bin('CB48CD')) = 'y0jN'json_decode(Data: string) -> array | map
Decode Data from JSON format. Example:
map_get('a', json_decode('{"a": 1}')) = 1json_encode(Data: array | map) -> string
Encode Data to JSON format. Example:
json_encode([1,2,3]) = '[1,2,3]'bin2hexstr(Data: binary) -> string
Convert binary data to the corresponding string of hexadecimal digits. Example:
bin2hexstr(zip('hello')) = 'CB48CDC9C90700'hexstr2bin(Data: string) -> binary
Converts a string of hexadecimal digits to the corresponding binary data. Example:
unzip(hexstr2bin('CB48CDC9C90700')) = 'hello'Schema Registry Functions
EMQX Enterprise also supports using schema_encode and schema_decode functions to decode and encode Protobuf (Protocol Buffers) and Avro data according to a specified schema. You can read more about these functions in Schema Registry.
See the table below for a detailed explanation of the functions.
schema_encode(SchemaID: string, Data: map) -> binary
Encodes Data using the specified Avro Schema. Create a schema in the Schema Registry to get the ID.
schema_encode(SchemaID: string, Data: map, MsgType: string) -> binary
Encodes Data using the specified Protobuf Schema. Create a schema in the Schema Registry to get the ID. MsgType is used to specify the message type corresponding to Data in Protobuf Schema.
schema_decode(SchemaID: string, Bin: binary) -> map
Decodes Bin using the specified Avro Schema. Create a schema in the Schema Registry to get the ID.
schema_decode(SchemaID: string, Bin: binary, MsgType: string) -> map
Decodes Bin using the specified Protobuf Schema. Create a schema in the Schema Registry to get the ID. MsgType is used to specify the message type corresponding to Data in Protobuf Schema.
Sparkplug B Functions
In EMQX Enterprise, there are also special purpose functions for decoding and encoding Sparkplug B messages (sparkplug_decode and sparkplug_encode). You can read more about the sparkplug functions in Sparkplug B.
Date and Time Conversion Functions
date_to_unix_ts(Unit: string, FormatString: string, DateTimeString: string) -> integer
Parses the datetime string DateTimeString according to the format string FormatString, converting it to Unix time in specified time unit Unit.
second, millisecond, microsecond and nanosecond are available Units.
The placeholders that can be used in FormatString are as follows:
| Placeholder | Meaning | Value range | | ------ | ---------------------------------- | ----- ---------------- | | %Y | Four-digit year | 0000 - 9999 | | %m | Two-digit month | 01 - 12 | | %d | Two-digit day of the month | 01 - 31 | | %H | Two-digit hour in 24-hour format | 00 - 24 | | %M | Two-digit minute | 00 - 59 | | %S | Two-digit second | 00 - 59 | | %N | Nanoseconds | 000000000 - 999999999 | | %6N | Microseconds, the first six digits of nanoseconds | 000000 - 999999 | | %3N | Milliseconds, the first three digits of nanoseconds | 000 - 999 | | %z | Time zone offset in the format ±hhmm | -1159 - +1159 | | %:z | Time zone offset in the format ±hh:mm | -11:59 - +11:59 | | %::z | Time zone offset in the format ±hh:mm:ss | -11:59:59 - +11:59:59 |
Example:
date_to_unix_ts('second', '%Y-%m-%d %H:%M:%S%:z', '2024-02-23 15:00:00+08:00') = 1708671600date_to_unix_ts(Unit: string, Offset: string | integer, FormatString: string, DateTimeString: string) -> integer
If the DateTimeString does not contain a time zone offset, you can use Offset to manually specify the offset, other behavior are the same as date_to_unix_ts/3. Offset can be a string or the number of seconds expressed directly as an integer.
When Offset is a string, the following format can be used:
Zorz, representing UTC offset 00:00.±hh[:mm][:ss]or±hh[mm][ss], positive or negative time offset from UTC.local, indicates the offset corresponding to the system's local time zone.
Example:
date_to_unix_ts('second', '+08:00', '%Y-%m-%d %H:%M:%S%:z', '2024-02-23 15:00:00') = 1708671600
date_to_unix_ts('second', 'Z', '%Y-%m-%d %H:%M:%S%:z', '2024-02-23 07:00:00') = 1708671600
date_to_unix_ts('second', 14400, '%Y-%m-%d %H:%M:%S%:z', '2024-02-23 15:00:00') = 1708686000format_date(Unit: string, Offset: string | integer, FormatString: string, Time: Integer) -> string
Converts a Unix time to a datetime string in the specified format. Unit represents the time unit of the Unix time Time to be converted, Offset represents the time zone offset in the output date and time, and FormatString represents the output date and time format.
See date_to_unix_ts/3, 4 for possible values of Unit, Offset and FormatString.
Example:
format_date('millisecond', '+08:00', '%Y-%m-%d %H:%M:%S.%6N%z', 1708933353472) = '2024-02-26 15:42:33.472000+0800'
format_date('millisecond', '+08:00', '%Y-%m-%d %H:%M:%S.%6N%:z', 1708933353472) = '2024-02-26 15:42:33.472000+08:00'
format_date('millisecond', '+08:20:30', '%Y-%m-%d %H:%M:%S.%3N%::z', 1708933353472) = '2024-02-26 16:03:03.472+08:20:30'
format_date('millisecond', 'Z', '%Y-%m-%d %H:%M:%S.%3N%:z', 1708933353472) = '2024-02-26 07:42:33.472+08:00'
format_date('millisecond', 28800, '%Y-%m-%d %H:%M:%S.%3N%:z', 1708933353472) = '2024-02-26 15:42:33.472+08:00'now_rfc3339() -> string
Returns the current system time as an RFC3339 datetime string in seconds. Example:
now_rfc3339() = '2024-02-23T10:26:20+08:00'now_rfc3339(Unit: string) -> string
Same as now_rfc3339/0, but you can use Unit to specify the time unit, supporting second, millisecond, microsecond and nanosecond. Example:
now_rfc3339('microsecond') = '2024-02-23T10:26:38.009706+08:00'now_timestamp() -> integer
Returns the current system time as a Unix timestamp in seconds. Example:
now_timestamp() = 1708913853now_timestamp(Unit: string) -> integer
Same as now_timestamp/0, but you can use Unit to specify the time unit, supporting second, millisecond, microsecond and nanosecond. Example:
now_timestamp('microsecond') = 1708913828814315rfc3339_to_unix_ts(DateTimeString: string) -> integer
Converts an RFC3339-compliant datetime string to a Unix timestamp. 2024-02-23T15:56:30Z is a typical RFC3339 date and time string, which represents UTC time on February 23, 2024, 15:56:30.
Example:
rfc3339_to_unix_ts('2024-02-23T15:56:30Z') = 1708703790
rfc3339_to_unix_ts('2024-02-23T15:56:30+08:00') = 1708674990rfc3339_to_unix_ts(DateTimeString: string, Unit: string) -> integer
Same as rfc3339_to_unix_ts/1, but you can use Unit to specify the unit of returned Unix timestamp, supporting second, millisecond, microsecond and nanosecond. Example:
rfc3339_to_unix_ts('2024-02-23T15:56:30.87Z', 'second') = 1708703790
rfc3339_to_unix_ts('2024-02-23T15:56:30.87Z', 'millisecond') = 1708703790870
rfc3339_to_unix_ts('2024-02-23T15:56:30.87Z', 'microsecond') = 1708703790870000
rfc3339_to_unix_ts('2024-02-23T15:56:30.535904509Z', 'nanosecond') = 1708703790535904509timezone_to_offset_seconds(Offset: string) -> integer
Converts a time zone offset as a string to an integer in seconds. The following are supported time offset representations:
Zorz, representing UTC offset 00:00.±hh[:mm][:ss]or±hh[mm][ss], positive or negative time offset from UTC.local, indicates the offset corresponding to the system's local time zone.
Example:
timezone_to_offset_seconds('Z') = 0
timezone_to_offset_seconds('+08:00') = 28800
timezone_to_offset_seconds('local') = 28800unix_ts_to_rfc3339(Time: integer) -> string
Converts a Unix timestamp in seconds to an RFC3339-compliant datetime string, using the system's local time zone. Example:
unix_ts_to_rfc3339(1708671600) = '2024-02-23T15:00:00+08:00'unix_ts_to_rfc3339(Time: integer, Unit: string) -> string
Same as unix_ts_to_rfc3339/0, but you can use Unit to specify the time unit, supporting second, millisecond, microsecond and nanosecond. Example:
unix_ts_to_rfc3339(1708671600766, 'millisecond') = '2024-02-23T15:00:00.766+08:00'MongoDB Time Functions
mongo_date() -> MongoDB ISODate | string
Returns the current time as a MongoDB ISODate type or string. Only supported for use in MongoDB related actions and SQL tests, and only in SQL tests mongo_date() returns a string, such as ISODate("2024-02-23T15:00:00.123Z"). Returns other than strings from mongo_date() are not currently supported as input to other functions.
Example:
mongo_date() = 'ISODate("2024-02-23T15:00:00.123Z")'mongo_date(Timestamp: integer) -> MongoDB ISODate | string
Converts the specified Unix timestamp in milliseconds to the MongoDB ISODate type or string. Other behaviors are the same as mongo_date/0.
Example:
mongo_date(now_timestamp('millisecond')) = 'ISODate(2024-02-23T15:48:57.871Z)'mongo_date(Timestamp: integer, Unit: string) -> MongoDB ISODate | string
Converts the specified Unix timestamp to the MongoDB ISODate type or string. You can specify the unit of the input timestamp through Unit. Other behaviors are the same as mongo_date/0.
Possible values for Unit are:
secondmillisecondmicrosecondnanosecond
Example:
mongo_date(now_timestamp('microsecond'), 'microsecond') = 'ISODate(2024-02-23T15:51:01.232Z)'UUID Functions
uuid_v4() -> string
Generates a version 4 UUID. Example:
uuid_v4() = 'f5bb7bea-a371-4df7-aa30-479add04632b'uuid_v4_no_hyphen() -> string
Generates a version 4 UUID without hyphens. Example:
uuid_v4_no_hyphen() = 'd7a39aa4195a42068b962eb9a665503e'