When you create data forwarding rules, you need to write SQL statements to parse and process JSON-formatted data that is submitted by devices. The system does not parse binary data. Binary data is passed through to a specified destination. This article describes how to write SQL statements for data forwarding rules.

SQL statements

JSON data can be mapped to a virtual table. Keys in a JSON data record correspond to the column names. Values in a JSON data record correspond to the column values. After a JSON data record is mapped to a virtual table, the JSON data record can be processed by using SQL. The following figure shows the format of SQL statements for data forwarding rules.

SQL statements

Examples:

  • The following SQL example shows how to process the data of custom topics.

    An environmental sensor can be used to collect temperature, humidity, and atmospheric pressure data. The following code shows the data that is submitted by a device to the /a1hRrzD****/+/user/update custom topic. 

    {
"temperature":25.1
"humidity":65
"pressure":101.5
"location":"***,***"
}

    When the temperature is higher than 38 degrees Celsius, a rule is triggered and the device name, temperature data, and location data are returned. To implement this use case, the following SQL statement is used. 

    SELECT temperature as t, deviceName() as deviceName, location 
FROM "/a1hRrzD****/+/user/update" 
WHERE temperature > 38
  • The following SQL example shows how to process the data of basic communication topics and Thing Specification Language (TSL)-based communication topics. Data can be forwarded from basic communication topics and Thing Specification Language (TSL)-based communication topics to the rules engine. After the data is received, the rules engine parses the data. For more information about the format of parsed data, see Data formats.

    For example, a temperature and humidity sensor has several properties, as shown in the following figure.

    Properties

    For example, the following code shows the result after the rules engine parses the temperature and humidity data that is submitted by the temperature and humidity sensor. 

    {
    "deviceType": "TemperatureHumidityDetector", 
    "iotId": "N5KURkKdibnZvSls****000100", 
    "productKey": "a15NNfl****", 
    "gmtCreate": 1564569974224, 
    "deviceName": "N5KURkKdibnZvSls3Yfx", 
    "items": {
        "CurrentHumidity": {
            "value": 70, 
            "time": 1564569974229
        }, 
        "CurrentTemperature": {
            "value": 23.5, 
            "time": 1564569974229
        }
    }
}
    Note When you issue SQL queries, the items. ${Property identifier}.value variable must be used to access the data of a specified property.

    When the temperature is higher than 38 degrees Celsius, a rule is triggered and the device name, current temperature data, and current humidity data are returned. To implement this use case, the following SQL statement is used.

    Edit an SQL statement
    SELECT deviceName() as deviceName, items.CurrentHumidity.value as Humidity, items.CurrentTemperature.value as Temperature 
FROM "/sysa15NNfl****/N5KUR***/thing/event/property/post" 
WHERE items.CurrentTemperature.value > 38

    If a property belongs to a custom module such as testFB, the format of the property identifier is Module identifier:Property identifier. When the expression is used to access the data of a specified property, you must enclose the property within a pair of double quotation marks (""). The following SQL statement shows an example. 

    SELECT deviceName() as deviceName, "items.testFB:CurrentHumidity.value" as Humidity, "items.testFB:CurrentTemperature.value" as Temperature 
FROM "/sysa15NNfl****/N5KUR***/thing/event/property/post" 
WHERE "items.testFB:CurrentTemperature.value" > 38

SELECT

  • JSON data

    You can use the parsing result for the payload of a submitted message as the fields of a SELECT statement. The parsing result includes the keys and values of JSON data. You can also use SQL built-in functions as the fields, for example, deviceName(). For more information about the SQL built-in functions of the rules engine, see Functions.

    You can combine * with functions. SQL subqueries are not supported.

    The data that is submitted in the JSON format can be arrays or nested JSON data. You can use JSONPath to obtain a property value from an SQL statement. For example, you can use a.key2 to obtain the v2 value from the {a:{key1:v1, key2:v2}} statement. When you specify variables in SQL statements, take note of the difference between single quotation marks (') and double quotation marks ("). Constants are enclosed with single quotation marks ('). Variables are enclosed with double quotation marks ("). Variables may also be written without being enclosed by quotation marks. For example, 'a.key2' indicates a constant whose value is a.key2.

    In this SQL example:

    • The SELECT temperature as t, deviceName() as deviceName, location statement is used to process the data of a custom topic. The temperature and loaction fields are obtained from submitted data. The deviceName() SQL built-in function is used as a field.
    • The SELECT deviceName() as deviceName, items.CurrentHumidity.value as Humidity, items.CurrentTemperature.value as Temperature statement is used to process the submitted property data of a topic. The items.CurrentHumidity.value and items.CurrentTemperature.value fields are obtained from the submitted property data of the default module. The deviceName() SQL built-in function is used as a field.
      Note The items.testFB:CurrentHumidity.value and items.testFB:CurrentTemperature.value fields are obtained from the submitted property data of a custom module.
  • Binary data
    • Enter * to pass through binary data. After you specify *, you can no longer use functions.
    • You can use built-in functions. The to_base64(*) function converts an original binary payload into a Base64 string. The deviceName() function extracts the name information of a device.
Note A SELECT statement can include a maximum of 50 fields.

FROM

You can specify a topic in the FROM clause. This topic is the source from which the device messages to be processed are obtained. In this topic, you can specify the + wildcard as the device name category. The wildcard represents all categories at the current level. In this case, this wildcard represents all the devices of a specified product. After you specify a custom topic, you can specify the # wildcard. This wildcard represents all the categories of the current level and the next levels. For more information about wildcards, see Edit a topic category.

When the messages of a specified topic are received, the payload data of these messages are converted into the JSON format and processed based on the specified SQL statement. If the format of a message is invalid, the message is disregarded. You can use the topic() function to specify a topic.

In the preceding SQL example:

  • The FROM "/a1hRrzD****/+/user/update" clause indicates that the related SQL statement processes only the messages of the /a1hRrzD****/+/user/update custom topic.
  • The FROM "/sys/a15NNfl****/N5KURkKdibnZvSls3Yfx/thing/event/property/post" clause indicates that the related SQL statement processes only the messages from the topic of the N5KURkKdibnZvSls3Yfx device. The topic is used by the device to submit property data.

WHERE

  • The following example provides an example on JSON data:

    The WHERE clause is used as the condition to trigger the rule. SQL subqueries are not supported. The fields that can be used in the WHERE clause are the same as the fields that can be used in the SELECT statement. When a message of the related topic is received, the results obtained by using the WHERE clause will be used to check whether a rule will be triggered. For more information, see the "Supported WHERE expressions" section.

    In the preceding two examples, the WHERE temperature > 38 condition indicates that the rules are triggered only when the temperature is higher than 38 degrees Celsius.

  • Binary data

    If the reported message is composed of binary data, you can only use built-in functions and conditions in a WHERE clause. You cannot use the fields in the payload of the reported message.

SQL results

The SQL result that is returned after the SQL statement is executed will be forwarded. If an error occurs when the system parses the payload of the reported message, the rule execution fails.

You can forward data to Tablestore. In this case, you must use the ${expression} variable to specify the required value when you specify the data forwarding destination.

In the preceding two SQL examples, if you want to use the related rules to forward data to Tablestore tables, you can specify the following variables as the primary keys:

  • ${t}, ${deviceName}, and ${loaction}.
  • ${deviceName}, ${Humidity}, and ${Temperature}.

Arrays

Each array expression is enclosed within a pair of double quotation marks ("). Use $. to obtain a JSONObject. $. can be removed. Use . to obtain a JSONArray.

If a device message is {"a":[{"v":0},{"v":1},{"v":2}]}, the following results are obtained based on the specified expressions:
  • The result of "a[0]" is {"v":0}
  • The result of "$.a[0]" is {"v":0}
  • The result of ".a[0]" is [{"v":0}]
  • The result of "a[1].v" is 1
  • The result of "$.a[1].v" is 1
  • The result of ".a[1].v" is [1]

Supported WHERE conditions

Operator Description Example
= Equal to color = 'red'
<> Not equal to color <> 'red'
AND Logic AND color = 'red' AND siren = 'on'
OR Logic OR color = 'red' OR siren = 'on'
+ Add 4 + 5
- Subtract 5 - 4
/ Divide 20 / 4
* Multiply 5 * 4
% Returns the remainder 20 % 6
< Less than 5 < 6
<= Less than or equal to 5 <= 6
> Greater than 6 > 5
>= Greater than or equal to 6 >= 5
Function calls Functions. For more information, see Functions. deviceId()
Fields that are specified in the JSON format You can extract fields from a message payload and specify these fields in the JSON format. state.desired.color,a.b.c[0].d
CASE … WHEN … THEN … ELSE … END CASE expressions. Nested expressions are not supported. CASE col WHEN 1 THEN 'Y' WHEN 0 THEN 'N' ELSE '' END as flag
IN Only enumeration is supported. Subqueries are not supported. For example, you can use WHERE a IN(1, 2, 3 ). However, you cannot use WHERE a IN(select xxx).
like This operator is used to match a character. When you use a LIKE operator, you can use only the % wildcard to specify an arbitrary character string. where c1 like '%abc', where c1 not like '%def%'

Debug SQL statements

If you select JSON in the Data Type field when you create a data forwarding rule, you can debug an SQL statement in the IoT Platform console. Procedure:

  1. After an SQL statement is written, click Debug SQL.
  2. In the Debug SQL panel, click the Debug parameters tab. On the tab that appears, enter the required debugging data and click Debugging.

    Enter the required payload data for debugging based on the type of the data that is submitted by a topic. Description of data types:

    • If you use a custom topic, the type of the specified payload data must be the same as the type of the type of the data that is submitted by the custom topic.
    • If you use a basic communication topic or a TSL-based communication topic, see Data formats.
    SQLtest1
  3. Click the Commissioning results tab to view the result.SQLtestResult