Skip to content

Built-in SQL Functions

EMQX rules use a SQL-like syntax and support a variety of built-in functions for doing basic data transformation, including mathematical, data type judgment, conversion, string, map, array, hash, compression and decompression, bit, decoding and encoding, and time and date functions that are available in EMQX.

TIP

Since EMQX 5.0 version, EMQX also supports using jq language 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: including exp, power, log, log10, and log2

See the table below for a complete list of mathematical functions supported.

Function NameDescriptionParameter
absAbsolute valueOperand
cosCosine valueOperand
coshHyperbolic cosine valueOperand
acosInverse cosine valueOperand
acoshInverse hyperbolic cosine valueOperand
sinSine valueOperand
sinhHyperbolic sine valueOperand
asinArcsine valueOperand
asinhInverse hyperbolic sine valueOperand
tanTangent valueOperand
tanhHyperbolic tangent valueOperand
atanArc tangent valueOperand
atanhInverse hyperbolic tangent valueOperand
ceilRound up (integer)Operand
floorRound down (integer)Operand
roundRounding (integer)Operand
fmodmodulo
(remainder)
1. Left Operand
2. Right Operand
expExponentiation
x power of e
Operand
powerExponential operation
y power of X
1. Left operand x
2. Right operand y
sqrtSquare rootOperand
logLogarithm to eOperand
log10Logarithm to 10Operand
log2Logarithm to 2Operand
randomPseudo-random numbers0 < Operand <= 1

Examples:

erlang
abs(-12) = 12
cos(1.5) = 0.0707372016677029
cosh(1.5) = 2.352409615243247
acos(0.0707372016677029) = 1.5
acosh(2.352409615243247) = 1.5
sin(0.5) = 0.479425538604203
sinh(0.5) = 0.5210953054937474
asin(0.479425538604203) = 0.5
asinh(0.5210953054937474) = 0.5
tan(1.4) = 5.797883715482887
tanh(1.4) = 0.8853516482022625
atan(5.797883715482887) = 1.4
atanh(0.8853516482022625) = 1.4000000000000001
ceil(1.34) = 2
floor(1.34) = 1
round(1.34) = 1
round(1.54) = 2
exp(10) = 22026.465794806718
power(2, 10) = 1024
sqrt(2) = 1.4142135623730951
fmod(-32, 5) = -2
log10(1000) = 3
log2(1024) = 10

Data Type Judgment Function

EMQX has built-in functions for data type judgments. These functions are used to check the data type of a specific field in a message and return a boolean value indicating whether or not the field conforms to the specified data type.

See the table below for a complete list of data type judgment functions supported.

Function NameDescriptionParameter
is_nullCheck if a field is undefined
Boolean
Data
is_not_nullCheck if a field is defined
Boolean
Data
is_strCheck if the value is of String type
Boolean
Data
is_boolCheck if the value is of Boolean type
Boolean
Data
is_intCheck if the value is of Integer type
Boolean
Data
is_floatCheck if the value is of Float type
Boolean
Data
is_numCheck if the value is of numeric type
Integer or Float
Boolean
Data
is_mapCheck if the value is of Map type
Boolean
Data
is_arrayCheck if the value is of Array type
Boolean
Data

Examples:

erlang
is_null(undefined) = true
is_not_null(1) = true
is_str(1) = false
is_str('val') = true
is_bool(true) = true
is_int(1) = true
is_float(1) = false
is_float(1.234) = true
is_num(2.3) = true
is_num('val') = false

Data Type Conversion Functions

EMQX has built-in functions that allow you to convert the data type of a specific field in a message to a new data type.

See the table below for a complete list of data type judgment functions supported.

Function NameDescriptionParameter
str *Convert data to String typeData
str_utf8Convert data to UTF-8 String typeData
boolConvert data to Boolean typeData
intConvert data to Integer typeData
floatConvert data to Float typeData
float2strConvert a float to a string with the given precision1. Float Number
2. Precision
mapConvert data to Map typeData

[^*]: When converting a floating-point type to a string, the output may need to be rounded.

Examples:

erlang
str(1234) = '1234'
str_utf8(1234) = '1234'
bool('true') = true
int('1234') = 1234
float('3.14') = 3.14
float2str(20.2, 10) = '20.2'
float2str(20.2, 17) = '20.19999999999999928'

TIP

Data type conversion failures will cause SQL matching to fail, please proceed with caution.

String Functions

EMQX provides several built-in functions for manipulating strings in the rule engine, for example, case conversion, space removing, and sting length count.

See the table below for a complete list of string functions supported.

Function NameDescriptionParameter
lowerConvert to lowercaseInput string
upperConvert to uppercaseInput string
trimRemove left and right spaceInput string
ltrimRemove left spaceInput string
rtrimRemove right spaceInput string
reverseString inversionInput string
strlenString lengthInput string
substrTake a substring of characters1. Input string
2. Start position (starting at position 0)
substr
(with end)
Take a substring of characters1. Input string
2. Start position (starting at position 0)
3. Substring length
splitString split1. Input string
2. Separator
split
(with direction)
String split1. Input string
2. Separator
3. Direction, optional value: leading or trailing
concatString concatenation1. Left string
2. Right substring
tokensString splitting (based on a specified delimiter)1. Input string
2. Delimiter or substring
tokensString splitting (based on a specified delimiter and line break)1. Input string
2. Delimiter or substring
3. 'nocrlf'
sprintfString formatting, see the Format section in https://erlang.org/doc/man/io.html#fwrite-1 for usage1. Format string
2,3,4... Parameter list. The number of parameters may vary
padString padding with spaces from the end1. Original string
2. Total character length
padString padding with spaces from the end1. Original string
2. Total character length
3. 'trailing'
padString padding with spaces from both sides1. Original string
2. Total character length
3. 'both'
padString padding with spaces from the beginning1. Original string
2. Total character length
3. 'leading'
padString padding with a specified character from the end1. Original string
2. Total character length
3. 'trailing'
4. Character used for padding
padString padding with a specified character from both sides1. Original string
2. Total character length
3. 'both'
4. Character used for padding
padString padding with a specified character from the beginning1. Original string
2. Total character length
3. 'leading'
4. Character used for padding
replaceReplace a substring in a string (replace all occurrences)1. Original string
2. Substring to be replaced
3. String used for replacement
replaceReplace a substring in a string (replace all occurrences)1. Original string
2. Substring to be replaced
3. String used for replacement
4. 'all'
replaceReplace a substring in a string (replace the first occurrence from the end)1. Original string
2. Substring to be replaced
3. String used for replacement
4. 'trailing'
replaceReplace a substring in a string (replace the first occurrence from the beginning)1. Original string
2. Substring to be replaced
3. String used for replacement
4. 'leading'
regex_matchCheck if a string matches a regular expression1. Original string
2. Regular expression
regex_replaceReplace substrings in a string that match a regular expression1. Original string
2. Regular expression
3. String used for replacement
asciiReturn the ASCII code of a character1. Character
findSearch and return a substring in a string (searching from the beginning)1. Original string
2. Substring to be found
findSearch and return a substring in a string (searching from the beginning)1. Original string
2. Substring to be found
3. 'leading'

Examples:

erlang
lower('AbC') = 'abc'
lower('abc') = 'abc'

upper('AbC') = 'ABC'` `lower('ABC') = 'ABC'

trim(' hello  ') = 'hello'

ltrim(' hello  ') = 'hello  '

rtrim(' hello  ') = ' hello'

reverse('hello') = 'olleh'

strlen('hello') = 5

substr('abcdef', 2) = 'cdef'
substr('abcdef', 2, 3) = 'cde'

split('a/b/ c', '/') = ['a', 'b', ' c']
split('a/b/ c', '/', 'leading') = ['a', 'b/ c']
split('a/b/ c', '/', 'trailing') = ['a/b', ' c']

concat('a', '/bc') = 'a/bc'
'a' + '/bc' = 'a/bc'

tokens(' a/b/ c', '/') = [' a', 'b', ' c']
tokens(' a/b/ c', '/ ') = ['a', 'b', 'c']
tokens(' a/b/ c\n', '/ ') = ['a', 'b', 'c\n']
tokens(' a/b/ c\n', '/ ', 'nocrlf') = ['a', 'b', 'c']
tokens(' a/b/ c\r\n', '/ ', 'nocrlf') = ['a', 'b', 'c']

sprintf('hello, ~s!', 'steve') = 'hello, steve!'
sprintf('count: ~p~n', 100) = 'count: 100\n'

pad('abc', 5) = 'abc  '
pad('abc', 5, 'trailing') = 'abc  '
pad('abc', 5, 'both') = ' abc '
pad('abc', 5, 'leading') = '  abc'
pad('abc', 5, 'trailing', '*') = 'abc**'
pad('abc', 5, 'trailing', '*#') = 'abc*#*#'
pad('abc', 5, 'both', '*') = '*abc*'
pad('abc', 5, 'both', '*#') = '*#abc*#'
pad('abc', 5, 'leading', '*') = '**abc'
pad('abc', 5, 'leading', '*#') = '*#*#abc'

replace('ababef', 'ab', 'cd') = 'cdcdef'
replace('ababef', 'ab', 'cd', 'all') = 'cdcdef'
replace('ababef', 'ab', 'cd', 'trailing') = 'abcdef'
replace('ababef', 'ab', 'cd', 'leading') = 'cdabef'

regex_match('abc123', '[a-zA-Z1-9]*') = true

regex_replace('ab1cd3ef', '[1-9]', '[&]') = 'ab[1]cd[3]ef'
regex_replace('ccefacef', 'c+', ':') = ':efa:ef'

ascii('a') = 97

find('eeabcabcee', 'abc') = 'abcabcee'
find('eeabcabcee', 'abc', 'leading') = 'abcabcee'
find('eeabcabcee', 'abc', 'trailing') = 'abcee'

Map Functions

EMQX has built-in functions that allow you to manipulate maps, and perform operations such as adding key-value pairs to a map and retrieving values.

See the table below for a complete list of map functions supported.

Function NameDescriptionParameter
map_getRetrieve the value associated with a specified key in the Map
Or return null if the key is not found
1. Key
2. Map
map_get
(with default)
Retrieve the value associated with a specified key in the Map,
Or return the specified default value if the key is not found
1. Key
2. Map
3. Default Value
map_putInsert a key-value pair into the Map1. Key
2. Value
3. Map

Examples:

erlang
map_get('a', json_decode( '{ "a" : 1 }' )) = 1
map_get('b', json_decode( '{ "a" : 1 }' ), 2) = 2
map_get('a', map_put('a', 2, json_decode( '{ "a" : 1 }' ))) = 2

Array Functions

EMQX provides several built-in functions for working with arrays in the rule engine. These functions allow you to perform operations such as filtering, mapping, and reducing on arrays within incoming messages.

See the table below for a complete list of array functions supported.

Function NamePurposeParameters
nthReturns the nth element of an array.
Subscripts start at 1.
1. n (integer)
2. Array
lengthReturns the length of an array.Array
sublistReturns a sub-array of length len starting from the first element.
Subscripts start at 1.
1. len (integer)
2. Array
sublistReturns a sub-array of length len starting from the nth element.
Subscripts start at 1.
1. n (integer)
2. len (integer)
3. Array
firstReturns the first element of an array.
Subscripts start at 1.
Array
lastReturns the last element of an array.Array
containsReturns a boolean indicating if the data is in the array.1. Data
2. Array

Examples:

erlang
nth(2, [1,2,3,4]) = 2
length([1,2,3,4]) = 4
sublist(3, [1,2,3,4]) = [1,2,3,4]
sublist(1,2,[1,2,3,4]) = [1, 2]
first([1,2,3,4]) = 1
last([1,2,3,4]) = 4
contains(2, [1,2,3,4]) = true

Hash Function

EMQX supports using D5, SHA, and SHA256 to ensure data integrity and security.

See the table below for a complete list of Hush functions supported.

Function NameDescriptionParameter
md5Calculate the MD5 hash valueData
shaCalculate the SHA hash valueData
sha256Calculate the SHA256 hash valueData

Examples:

erlang
md5('some val') = '1b68352b3e9c2de52ffd322e30bffcc4'
sha('some val') = 'f85ba28ff5ea84a0cbfa118319acb0c5e58ee2b9'
sha256('some val') = '67f97635d8a0e064f60ba6e8846a0ac0be664f18f0c1dc6445cd3542d2b71993'

Compression and Decompression Functions

EMQX uses compression and decompression functions to reduce network bandwidth usage and improve system performance, where, the compression functions are used to reduce the amount of data that needs to be transmitted over the network, the decompression functions are used to decompress the compressed payload data of MQTT messages.

See the table below for a complete list of compression and decompression functions supported.

FunctionPurposeParameters
gzipCompresses with gzip headers and checksum.raw_data
(binary)
gunzipDecompresses with gzip headers and checksum.compressed_data
(binary)
zipCompresses without zlib headers and checksum.raw_data (binary),
compression_level
(optional)
unzipDecompresses data without zlib headers and checksum.compressed_data
(binary)
zip_compressCompresses with zlib headers and checksum.raw_data (binary)
compression_level
(optional)
zip_uncompressDecompresses with zlib headers and checksum.compressed_data
(binary)

Examples:

erlang
bin2hexstr(gzip('hello world')) = '1F8B0800000000000003CB48CDC9C95728CF2FCA49010085114A0D0B000000'
gunzip(hexstr2bin('1F8B0800000000000003CB48CDC9C95728CF2FCA49010085114A0D0B000000')) = 'hello world'

bin2hexstr(zip('hello world')) = 'CB48CDC9C95728CF2FCA490100'
unzip(hexstr2bin('CB48CDC9C95728CF2FCA490100')) = 'hello world'

bin2hexstr(zip_compress('hello world')) = '789CCB48CDC9C95728CF2FCA4901001A0B045D'
zip_uncompress(hexstr2bin('789CCB48CDC9C95728CF2FCA4901001A0B045D')) = 'hello world'

Bitwise Operation Functions

A number of bitwise functions are provided to operate on integers.

FunctionDescriptionParameters
bitnotBitwise NOT1. Integer
bitandBitwise AND of two integers1. Integer
2. Integer
bitorBitwise OR of two integers1. Integer
2. Integer
bitxorBitwise XOR of two integers1. Integer
2. Integer
bitslLeft-shift an integer1. Integer to operate on
2. Number of bits to shift
bitsrRight-shift an integer1. Integer to operate on
2. Number of bits to shift

Bit Sequence Functions

A number of functions are provided to operate on bit sequence (binary or bitstring) inputs, e.g. subbits to extract a sequence of bits and convert it to a specified data type.

TIP

The binary type represents a sequence of bytes, each consisting of 8 bits. On the other hand, a bitstring denotes a sequence of bits that may not necessarily be a multiple of 8 in length.

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.

FunctionDescriptionParameters
bytesizeReturns the number of bytes of a byte sequence1. Binary
bitsizeReturns the number of bits of a bit sequence1. Binary or bitstring
subbitsReturns an unsigned integer (big-endian) obtained by extracting a specified number of bits from the beginning of a binary input.1. Binary input
2. Number of bits to extract
subbits
(with offset)
Returns an unsigned integer (big-endian) obtained by extracting a specified number of bits starting from a given offset in a binary input.
Offsets are indexed starting from 1.
1. Binary input
2. Starting offset
3. Number of bits to extract
subbits
(with offset and data type conversion)
Returns a data value obtained by extracting a specified number of bits starting from a given offset in a binary input and after data type conversion.
Offsets are indexed starting from 1.
1. Binary input
2. Starting offset
3. Number of bits to extract
4. Data Type, can be integer, float, bits

If set to integer, you can continue to set:
- Signedness: unsigned, signed,
- Endianness: big, little

Examples:

erlang
subbits('abc', 8) = 97
subbits('abc', 9, 8) = 98
subbits('abc', 17, 8) = 99
subbits('abc', 9, 16, 'integer', 'signed', 'big') = 25187
subbits('abc', 9, 16, 'integer', 'signed', 'little') = 25442

Decoding and Encoding Functions

EMQX uses encoding and decoding functions to convert data from one format to another.

See the table below for a complete list of encoding and decoding functions supported.

FunctionDescriptionParameters
base64_encodeBASE64 encodeBinary to be encoded
base64_decodeBASE64 decodeBbase64-formatted string to be decoded
json_encodeJSON encodeData to be encoded
json_decodeJSON decodeJSON string to be decoded
bin2hexstrBinary to Hex StringBinary
hexstr2binBinary to Hex Stringhex string

Examples:

erlang
base64_encode('some val') = 'c29tZSB2YWw='
base64_decode('c29tZSB2YWw=') = 'some val'
json_encode(json_decode( '{ "a" : 1 }' )) = '{"a":1}'
bin2hexstr(hexstr2bin('ABEF123')) = 'ABEF123'

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.

FunctionDescriptionParameters
schema_encodeEncode data according to a pre-defined schema.1. Schema ID defined by schema registry
2. Data to be encoded
3 ... N. Remaining arguments according to the schema type
schema_decodeDecode data according to a pre-defined schema.1. Schema ID defined by schema registry
2. Data to be decoded
3..N. Remaining arguments according to the schema type

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.

Time and Date Functions

EMQX uses the following functions for handling time and date, and the time unit supported by these functions are second, millisecond, microsecond, and nanosecond.

FunctionPurposeParameters
now_timestampReturn the current unix epoch timestamp
Unit: second
-
now_timestampReturn the current unix epoch timestamp with a self-defined unitTime unit
now_rfc3339Create the current RFC3339 time string
Unit: second
-
now_rfc3339Create the current RFC3339 time string with a self-defined unitTime unit
unix_ts_to_rfc3339Convert an unix epoch (in second) to RFC3339 time stringUnix epoch in second
unix_ts_to_rfc3339Convert an unix epoch to RFC3339 time string1. Unix epoch
2. Time unit
rfc3339_to_unix_tsConvert an RFC3339 time string (in second) to unix epoch1. Time string of format RFC3339
rfc3339_to_unix_tsConvert an RFC3339 time string to unix epoch with a self-defined unit1. Time string of format RFC3339
2. Time unit
format_dateConvert timestamp to formatted time1. Time unit (can be second, millisecond, microsecond, or nanosecond)
2. Time offset (refer to Time Offset definition)
3. Date format (refer to Time String Codec Format)
4. Timestamp (optional parameter, default is current time)
date_to_unix_tsConvert formatted time to timestamp1. Time unit (can be second, millisecond, microsecond, or nanosecond)
2. Time offset (optional, when not filled, use the time offset in the formatted time string, refer to the refer to Time Offset definition)
3. Date format (refer to Time String Codec Format)
4. Formatted time string
timezone_to_offset_secondsConvert a timezone offset string to an integer representing secondsThis function accepts a timezone offset string as a parameter. Valid formats include "+hh:mm", "+hh:mm:ss", "Z" for Coordinated Universal Time (UTC), or "local" for the system's current timezone. Examples include "+02:00", "+00:00:42", "Z", and "local".

Syntax of Time String Format

PlaceholderDefinitionRange
%Yyear0000 - 9999
%mmonth01 - 12
%dday01 - 31
%Hhour00 - 12
%Mminute00 - 59
%Ssecond01 - 59
%Nnanosecond000000000 - 999999999
%3Nmillisecond000000 - 999999
%6Nmicrosecond000 - 000
%ztime offset [+|-]HHMM-1159 to +1159
%:ztime offset [+|-]HH:MM-11:59 to +11:59
%::ztime offset [+|-]HH:MM:SS-11:59:59 to +11:59:59

Time Offset

OffsetDefinitionExamples
zUTC Zulu Time+00:00
ZUTC Zulu Time. Same as z+00:00
localSystem TimeAutomatic
Beijing +08:00
Zulu +00:00
Stockholm, Sweden +02:00
Los Angeles -08:00
[+|-]HHMM%zBeijing +0800
Zulu +0000
Stockholm, Sweden +0200
Los Angeles -0800
[+|-]HH:MM%:zBeijing +08:00
Zulu +00:00
Stockholm, Sweden +02:00
Los Angeles -08:00
[+|-]HH:MM:SS%::zBeijing +08:00:00
Zulu +00:00:00
Stockholm, Sweden +02:00:00
Los Angeles -08:00:00
integer()SecondsBeijing 28800
Zulu 0
Stockholm, Sweden 7200
Los Angeles -28800

Examples:

SQL
now_timestamp() = 1650874276
now_timestamp('millisecond') = 1650874318331
now_rfc3339() = '2022-04-25T16:08:41+08:00'
now_rfc3339('millisecond') = '2022-04-25T16:10:10.652+08:00'
unix_ts_to_rfc3339(1650874276) = '2022-04-25T16:11:16+08:00'
unix_ts_to_rfc3339(1650874318331, 'millisecond') = '2022-04-25T16:11:58.331+08:00'
rfc3339_to_unix_ts('2022-04-25T16:11:16+08:00') = 1650874276
rfc3339_to_unix_ts('2022-04-25T16:11:58.331+08:00', 'millisecond') = 1650874318331
format_date('second', '+0800', '%Y-%m-%d %H:%M:%S%:z', 1653561612) = '2022-05-26 18:40:12+08:00'
format_date('second', 'local', '%Y-%m-%d %H:%M:%S%:z') = "2022-05-26 18:48:01+08:00"
format_date('second', 0, '%Y-%m-%d %H:%M:%S%:z') = '2022-05-26 10:42:41+00:00'
date_to_unix_ts('second', '%Y-%m-%d %H:%M:%S%:z', '2022-05-26 18:40:12+08:00') = 1653561612
date_to_unix_ts('second', 'local', '%Y-%m-%d %H-%M-%S', '2022-05-26 18:40:12') = 1653561612
date_to_unix_ts('second', '%Y-%m-%d %H-%M-%S', '2022-05-26 10:40:12') = 1653561612

MongoDB Time Functions

FunctionPurposeParameters
mongo_dateCreate a mongodb ISODate type of now-
mongo_dateCreate a mongodb ISODate type from the given unix epoch in millisecond1. Unix epoch in millisecond
mongo_dateCreate a mongodb ISODate type from the given unix epoch in given time unit1. Unix epoch
2. Time unit, can be one of 'second', 'millisecond', 'microsecond' or 'nanosecond'

The time unit can be one of 'second', 'millisecond', 'microsecond' or 'nanosecond'.

SQL
mongo_date() = 'ISODate("2012-12-19T06:01:17.171Z")'
mongo_date(timestamp) = 'ISODate("2012-12-19T06:01:17.171Z")'
mongo_date(timestamp, 'millisecond') = 'ISODate("2012-12-19T06:01:17.171Z")'

UUID Function

FunctionPurposeParametersReturn Value
uuid_v4Generates Version 4 standard UUID-UUID
uuid_v4_no_hyphenGenerates Version 4 standard UUID without hyphens-UUID
erlang
uuid_v4() = '4b90d7b7-a185-4bf0-9b97-3f6b8f83b61d'
uuid_v4_no_hyphen() = 'fb00db84f64a4731b49f42b9ea2e3e34'