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

Functions 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, a null string is returned for the device. When you debug your SQL statements, a null string is returned because no real device or tag exists.
concat(string1, string2) Concatenates the strings and returns the concatenation result.

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 the encryption algorithm. Available algorithms include MD2, MD5, SHA1, SHA-256, SHA-384, and SHA-512.

deviceName() Returns the name of the current device. When you debug your SQL statements, a null string is returned because no real device exists.
endswith(input, suffix) Validates whether the input string ends with the suffix string.
exp(number) Returns the mathematical constant e raised to the power of the 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.

The default base is 10. This means that lg(n) is returned if you do not specify m.

lower(string) Converts all letters in the specified string into lowercase and returns the lowercase string.
mod(n, m) Returns the remainder after number n is divided by 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 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. It means that payload() and payload(‘utf-8’) return the same result.

power(n,m) Returns the 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 n.
thingPropertyFlatMap(property) Returns the values of the property in the Thing Specification Language (TSL) model. If a property has multiple values, the values are separated with underscores (_). If a TSL model contains more than 50 properties, the data forwarding feature cannot forward the entire original TSL model. You can use this function to extract data from the items property of the TSL model. In this way, all properties of the TSL model can be forwarded to other Alibaba Cloud services.

You can specify multiple properties as the input of the function. If you do not specify the property, values of all the properties are returned.

Example: The thingPropertyFlatMap("Power", "Position") function 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 the specified format.

The format parameter is optional. If you do not specify the format parameter, the timestamp of the current system time is returned. For example, timestamp() returns 1543373798943 and timestamp('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'') returns 2018-11-28T10:56:38Z.

timestamp_utc(format) Returns the UTC timestamp of the current system time in the specified format.

The format parameter is optional. If you do not specify the format parameter, the 13-digit timestamp of the current system time is returned. For example, timestamp_utc() returns 1543373798943 and timestamp_utc('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'') returns 2018-11-28T02:56:38Z.

topic(number)

Returns the entire topic name or a category of the topic name.

For example, a topic is named /alDbcLe****/TestDevice/user/. topic() returns the entire topic name /alDbcLe****/TestDevice/user/set. topic(1) returns the level-1 category alDbcLe****. topic(2) returns the level-2 category TestDevice.

upper(string) Converts all letters in the specified string into uppercase and returns the uppercase string.

For example, upper(alibaba) returns ALIBABA.

to_base64(*) Converts the message payload from binary data into a Base64-encoded string, and returns the message payload after conversion. You can use this function if the original message payload is of the binary data type.
messageId() Returns the message ID that is generated by IoT Platform .
substring(target, start, end) Returns part of a string.

Parameters:

  • target: the original string. This parameter is required.
  • start: the index from which the returned characters start. The character at the start index is also returned. This parameter is required.
  • end: the index at which the returned characters end. The character at the end index 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 being processed.
  • Enclose a constant string in a pair of single quotation marks ('). The data enclosed in double quotation marks (") is parsed as Integer-type data.
  • If an input parameter is invalid (for example, the data type is not supported), SQL parsing fails and the rules are not run.

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 into a hexadecimal string, and returns the message payload after conversion. You can use this function if the original message payload is of the binary data type.

Example

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 model of the product.

{
    "PolicyType": "Custom",
    "iotId": "H5KURkKdibnZvSls****000100",
    "productKey": "a1HHrkm****",,
    "gmtCreate": 1564569974224,
    "DeviceName": "testDevice1",
    "items": {
        "Temperature": {
            "value": 23.5,
            "time": 1564569974229
        }
    }
}

The temperature sensor product has multiple devices. The device names are TestDevice1, TestDevice2, and TestDevice3. The temperature property must be forwarded for computing and processing only if the property value reported by one of the devices is greater than 38. The following figure shows the SQL statement of the rule that is used to filter the data reported by devices.

Example

SQL statement:

SELECT deviceName() as deviceName,items.Temperature.value as Temperature FROM "/sys/a1HHrkm****/+/thing/event/property/post" WHERE Temperature>38 and (topic(3) = 'TestDevice1' or topic(3) = 'TestDevice2' or topic(3) = 'TestDevice3')

In this example, the following functions are used:

  • deviceName() function in the SELECT clause to obtain the device name from the reported data.
  • topic(number) function in the WHERE clause to specify a query condition (topic(3) = 'TestDevice1' or topic(3) = 'TestDevice2' or topic(3) = 'TestDevice3'). topic(3) indicates the level-3 category of the /sys/a1HHrkmIE8h/+/thing/event/property/post topic. Topics are categorized by device name at level 3. The plus sign (+) in the topic name is a wildcard that matches all device names under the product. In this example, data is forwarded only if the device name is TestDevice1, TesteDevice2, or TestDevice3.

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