Functions - IoT Platform - Alibaba Cloud Documentation Center

The rules engine provides functions that you can call in SQL statements to process data.

Functions that are supported by the data forwarding feature

Function	Description
abs(number)	Returns the absolute value of the number.
asin(number)	Returns the arcsine of the number.
attribute(key)	Returns the device tag value of the tag key. If the tag with the specified key is not attached to a device, no tag value is returned. When you debug your SQL statement, a null string is returned because no actual device or tag exists.
concat(string1, string2)	Concatenates the strings. A concatenated string is returned. Example: `concat(field,'a')`.
cos(number)	Returns the cosine of the number.
cosh(number)	Returns the hyperbolic cosine of the number.
crypto(field,String)	Encrypts the value of the field. The String parameter specifies an encryption algorithm. The following algorithms are supported: MD2, MD5, SHA1, SHA-256, SHA-384, and SHA-512.
deviceName()	Returns the name of the current device. When you debug your SQL statement, a null string is returned because no actual device exists.
endswith(input, suffix)	Checks whether the input string ends with a string that is specified by the suffix parameter.
exp(number)	Returns the value of the mathematical constant e raised to the power of a specified number.
floor(number)	Returns the largest integer that is less than or equal to the number.
log(n, m)	Returns the logarithm of number n to base m. If you do not specify m, the default base 10 is used. In this case, `log(n)` is returned.
lower(string)	Converts all letters in a specified string to lowercase and returns the lowercase string.
mod(n, m)	Returns the remainder after number n is divided by the divisor m.
nanvl(value, default)	Returns the value of the property. The value parameter specifies the name of the property. If the value of the property is null, the function returns the value of the default parameter.
newuuid()	Returns a random universally unique identifier (UUID).
payload(textEncoding)	Returns the message payload that is sent by a device. The payload is encoded by using the encoding scheme that is specified by the textEncoding parameter. The default encoding scheme is UTF-8. This indicates that the `payload()` and payload('utf-8') functions return the same result.
power(n,m)	Returns number n raised to the power of m.
rand()	Returns a random number that is greater than or equal to 0 and less than 1.
replace(source, substring, replacement)	Replaces the substring in the source column with the replacement string. Example: `replace(field,'a','1')`.
sin(n)	Returns the sine of number n.
sinh(n)	Returns the hyperbolic sine of number n.
tan(n)	Returns the tangent of number n.
tanh(n)	Returns the hyperbolic tangent of number n.
thingPropertyFlatMap(property)	Returns the values of a property in a Thing Specification Language (TSL) model. If the property has multiple values, separate the values with underscores (_). If a TSL model contains more than 50 properties, the data forwarding feature cannot forward the entire TSL model. You can call this function to extract property data from the TSL model. This way, all properties of the TSL model can be forwarded to other Alibaba Cloud services. You can specify multiple properties for the property parameter of the function. If you do not specify a property, all values of the properties are extracted. For example, the function `thingPropertyFlatMap('Power', 'Position')` adds `"Power": "On", "Position_latitude": 39.9, "Position_longitude": 116.38` to a message body.
timestamp(format)	Returns the timestamp of the current system time in GMT+8. The format parameter is optional. If you do not configure the format parameter, the timestamp of the current system time is returned. For example, the `timestamp()` function returns 1543373798943 and the `timestamp('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'')` function returns 2018-11-28T10:56:38Z.
timestamp_utc(format)	Returns the UTC timestamp of the current system time in a specified format. The format parameter is optional. If you do not configure the format parameter, the 13-digit timestamp of the current system time is returned. For example, the timestamp_utc() function returns `1543373798943` and the timestamp_utc('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'') function returns `2018-11-28T02:56:38Z`.
topic(number)	Returns the topic information at a specified level. For example, a topic is named `/alDbcLe**/TestDevice/user/set`. The `topic()` function returns the complete topic name /alDbcLe/TestDevice/user/set. The `topic(1)` function returns the first level `alDbcLe**`. The `topic(2)` function returns the second level `TestDevice`.
upper(string)	Converts all letters in a specified string to uppercase and returns the uppercase string. For example, the upper(alibaba) function returns `ALIBABA`.
to_base64(*)	Converts the message payload from binary data to a Base64-encoded string, and returns the message payload after conversion. If the original message payload is of the binary data type, you can call this function.
messageId()	Returns the message ID that is generated by IoT Platform.
substring(target, start, end)	Returns part of a specified string. Parameter description target: the original string. This parameter is required. start: the position from which the returned characters start. The character at the position is also returned. This parameter is required. end: the position at which the returned characters end. The character at the position is not returned. This parameter is optional. Note Only data of the string and integer types is supported. An integer is converted to a string before the integer is processed. Enclose a constant string in a pair of single quotation marks (''). Data that is enclosed in double quotation marks (") is parsed as integers. If an input parameter is invalid, for example, the data type is not supported, SQL parsing fails and the rules are not triggered. Examples: substring('012345', 0) = "012345" substring('012345', 2) = "2345" substring('012345', 2.745) = "2345" substring(123, 2) = "3" substring('012345', -1) = "012345" substring(true, 1.2) error substring('012345', 1, 3) = "12" substring('012345', -50, 50) = "012345" substring('012345', 3, 1) = ""
to_hex(*)	Converts the message payload from binary data to a hexadecimal string and returns the message payload after conversion. If the original message payload is of the binary data type, you can call this function.
user_property()	Obtains the value of the UserProperty parameter when MQTT 5.0 is used by a device. The `user_property()` function is called to obtain all property data. The `user_property('${Key}')` function is called to obtain the data of a specified key. For example, the value of the UserProperty parameter that is reported by a device is `{"a": "1", "b": "2"}`. The `user_property()` function returns `"{\"a\": \"1\", \"b\": \"2\"}"`. The `user_property('a')` function returns `"{\"a\": \"1\"}"`.
things_function_type()	Queries the type of the reported TSL data. This function applies only to TSL events and TSL services. The return value of this function varies based on the type of the TSL feature that is queried. If you query an event, the identifier of the event is returned. If you query a service, the identifier of the service is returned. For example, the `/SdfgeW***/device1/thing/event/BrokenInfo/post` topic is used to forward the BrokenInfo event. If you call the `things_function_type()` function to query the event, the identifier of the reported event `BrokenInfo` is returned.
things_property('${Parameter name}')	Queries the value of a TSL property, service, or event. This function applies only to TSL data. For example, the `/${productKey}/${deviceName}/thing/event/property/post` topic is used to forward the CurrentTemperature property. The `things_property('CurrentTemperature')` function returns the reported value of the CurrentTemperature property.

Examples

You can call functions to obtain or process data in the SELECT and WHERE clauses of SQL statements.

For example, a temperature sensor product has the Temperature property. The following script shows the TSL property data that is submitted by a device.

{
    "deviceType": "Custom",
    "iotId": "H5KURkKdibnZvSls****000100",
    "productKey": "a1HHrkm****",
    "gmtCreate": 1564569974224,
    "deviceName": "TestDevice1",
    "items": {
        "Temperature": {
            "value": 23.5,
            "time": 1564569974229
        }
    }
}

The temperature sensor product has multiple devices named TestDevice1, TestDevice2, and TestDevice3. The temperature property is forwarded to Function Compute for processing only if the submitted property value is greater than 38. The following figure shows the SQL statement of the rule that is used to filter submitted device data.

SQL statement:

SELECT deviceName() as deviceName,things_property('Temperature') as Temperature 
FROM "/g5or0***/+thing/event/property/post" 
WHERE things_property('Temperature')>38 and deviceName() in ('TestDevice1', 'TestDevice2', 'TestDevice3')

In this example, the deviceName() and things_property('Temperature') functions are called.

The functions are configured in the SELECT clause to filter the submitted data to obtain a device name and the value of the Temperature parameter.
These functions are configured in the WHERE clause to specify a condition. >38 and in ('TestDevice1', 'TestDevice2', 'TestDevice3') indicate that the property data is forwarded only if the temperature is greater than 38 degrees Celsius and the device name is TestDevice1, TestDevice2, or TestDevice3.

For more information about how to write the SELECT and WHERE clauses in an SQL statement and which conditional expressions are supported by the rules engine, see SQL statements.