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, no tag value is returned. When you debug your SQL statements, a null string is returned because no actual device or tag exists.
concat(string1, string2) Concatenates the strings and returns a 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 an 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 statement, a null string is returned because no actual device exists.
endswith(input, suffix) Validates whether the input string ends with the suffix string.
exp(number) Returns the value of the mathematical constant e that is 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 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 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 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 a property in a 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 TSL model. You can use 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 as the input parameters of the function. If you do not specify a property, all the values of properties are extracted.

For example, the thingPropertyFlatMap('Power', 'Position') function adds the following properties to a message body:"Power": "On", "Position_latitude": 39.9, "Position_longitude": 116.38.

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 topic information in a specified level.

For example, a topic is named /alDbcLe****/TestDevice/user/. topic() returns the complete topic name /alDbcLe****/TestDevice/user/set. topic(1) returns the first level alDbcLe****. topic(2) returns the second level 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 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 into a string before the integer is processed.
  • Enclose a constant string in a pair of single quotation marks ('). The 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 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.
user_property() Obtains the value of the UserProperty parameter when MQTT 5.0 is used by a device.
  • user_property() is used to obtain all property data.
  • user_property('${Key}') is used to obtain the data of the specified key.

For example, the value of the UserProperty parameter that is reported by the device is {"a": "1", "b": "2"}.

  • user_property() returns "{\"a\": \"1\", \"b\": \"2\"}".
  • user_property('a') returns "{\"a\": \"1\"}".
things_function_type() Obtains the type of the reported TSL data. This function is used only to query TSL data. The return value of this function varies based on the type of the TSL feature that is queried.
  • If you query a property, the value property is returned.
  • 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 use the things_function_type() function to query the event, the identifier of the reported event BrokenInfo is returned.

things_property('${Parameter name}') Obtains the value of a TSL property, service, or event. This function is used only to query TSL data.

For example, the /${productKey}/${deviceName}/thing/event/property/post topic is used to forward the CurrentTemperature property. things_property('CurrentTemperature') returns the reported value of the CurrentTemperature property.

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 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. The device names are 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.

Edit an SQL statement

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 used.

  • These functions are used in the SELECT clause to filter a device name and the value of the Temperature parameter from the submitted data.
  • These functions are used 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 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.