IoT Platform:SQL expressions

SQL expressions

JSON data can be mapped to a virtual table. In this mapping, a key corresponds to a table column and a value corresponds to the column value. This lets you use SQL for data processing. The following figure shows an SQL expression for a data forwarding rule.

The following are SQL examples for data forwarding rules:

• SQL example for processing data from a custom topic.

  An environment sensor collects temperature, humidity, and atmospheric pressure data. The device reports the following data to the custom topic `/a1hRrzD****/+/user/update`:

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

  To trigger the rule when the temperature is greater than 38°C and filter for the device name, temperature, and location information, use the following SQL statement:

  SELECT temperature as t, deviceName() as deviceName, location 
  FROM "/a1hRrzD****/+/user/update" 
  WHERE temperature > 38

• SQL example for processing data from basic communication topics and Thing Specification Language model communication topics. When data from basic communication topics and Thing Specification Language model communication topics is forwarded to the rules engine, the rules engine parses the data. For more information about the data format after parsing, see Data formats. The sensor defines two custom properties: Current Humidity (identifier: CurrentHumidity, data type: float, value range: 0 to 100) and Current Temperature (identifier: CurrentTemperature, data type: float, value range: -40 to 120).

  After the rules engine parses the temperature and humidity property data reported by the sensor, the data is in the following format:

  {
      "deviceType": "TemperatureHumidityDetector", 
      "iotId": "N5KURkKdibnZvSls****000100", 
      "productKey": "a15NNfl****", 
      "gmtCreate": 1564569974224, 
      "deviceName": "N5KURkKdibnZvSls3Yfx", 
      "items": {
          "CurrentHumidity": {
              "value": 70, 
              "time": 1564569974229
          }, 
          "CurrentTemperature": {
              "value": 23.5, 
              "time": 1564569974229
          }
      }
  }

  Important In an SQL query, you must use items.${PropertyIdentifier}.value to access the data of a specific property.

  To trigger the rule when the temperature is greater than 38°C and filter for the device name, current temperature, and current humidity, use the following SQL statement:

  SELECTdeviceName() 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 the property belongs to a custom module, such as testFB, the property identifier is in the format ${ModuleIdentifier}:${PropertyIdentifier}. To access the data of a specific property, you must use double quotation marks (""). The SQL statement is as follows:

  SELECTdeviceName() 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

• For JSON data format

  The fields in a SELECT statement can be the key values from the JSON payload of a reported message. You can also use built-in SQL functions, such as deviceName(). For more information about the built-in SQL functions of the rules engine, see Function list.

  You can use * in combination with functions. Subqueries are not supported.

  The reported JSON data can be an array or a nested JSON object. SQL statements support JSONPath to retrieve property values. For example, for {a:{key1:v1, key2:v2}}, you can use a.key2 to retrieve the value v2. When you use variables, note the difference between single and double quotation marks. Single quotation marks indicate a constant. Double quotation marks or no quotation marks indicate a variable. For example, if you use single quotation marks for 'a.key2', the value is the string a.key2.

  In the SQL examples in this topic:

  • In a SQL SELECTSELECT temperature as t, deviceName() as deviceName, location, temperature and location are fields from the reported data, and deviceName() is a built-in SQL function.
  • In the SELECT statement SELECT deviceName() as deviceName, items.CurrentHumidity.value as Humidity, items.CurrentTemperature.value as Temperature for processing data from a property reporting topic, items.CurrentHumidity.value and items.CurrentTemperature.value are fields from the reported property data of the default module. deviceName() is a built-in SQL function.
    Note items.testFB:CurrentHumidity.value and items.testFB:CurrentTemperature.value are fields from the reported property data of a custom module.
• For binary data
  • You can enter * to pass through data directly. You cannot use functions after *.
  • You can use built-in functions. For example, use the to_base64(*) function to convert the original binary payload data to a Base64 string and extract it. Use the deviceName() function to extract the device name.

FROM

In the FROM clause, you can specify a topic to match the source topic of device messages that you want to process. For the device name level in the topic, you can use the plus sign (+) wildcard character. This character represents all devices at the current level, which means all devices under the product. When you specify a custom topic, you can also use the number sign (#) wildcard character. This character represents the current level and all subsequent levels in the topic. For more information about wildcard characters, see Use custom topics for communication.

When a message from the specified topic arrives, its payload is parsed as JSON and processed based on the SQL statement. If the message format is invalid, the message is ignored. You can use the topic() function to reference a specific topic value.

In the SQL examples above:

• FROM "/a1hRrzD****/+/user/update" statement indicates that the SQL statement processes only messages from the custom topic `/a1hRrzD****/+/user/update`.
• FROM "/sys/a15NNfl****/N5KURkKdibnZvSls3Yfx/thing/event/property/post" statement indicates that the SQL statement processes only messages from the topic to which the device N5KURkKdibnZvSls3Yfx reports properties.

WHERE

• For JSON data format

  The WHERE clause specifies the rule trigger condition, which is a conditional expression. Subqueries are not supported. The fields that you can use in the WHERE clause are the same as those in the SELECT statement. When a message is received from the corresponding topic, the WHERE statement is evaluated to determine whether to trigger the rule. For more information about conditional expressions, see the Supported conditional expressions section below.

  In the two examples above, the conditional statement WHERE temperature > 38 indicates that the rule is triggered only when the temperature is greater than 38°C.

• For binary data format

  For binary data, the WHERE clause supports only built-in functions and conditional expressions. You cannot use fields from the payload.

SQL results

After the SQL statement is executed, an SQL result is generated for the next forwarding step. If an error occurs during payload parsing, the rule fails to run.

If you want to forward data to Tablestore, you must use the variable format ${Expression} to reference the corresponding value when you set the destination for data forwarding.

If the rules from the two examples above need to forward data to a data table in Tablestore, the values for the primary key can be configured as follows:

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

Array usage

Array expressions must be enclosed in double quotation marks. The $. operator fetches a JSON object. The $. operator is optional. The . operator fetches a JSON array.

Supported conditional expressions

Debug SQL statements

If you set Data Format to JSON when creating a data forwarding rule, you can debug the SQL statement online in the IoT Platform console. The procedure is as follows.

1. After writing the SQL, click SQL Debug.
2. In the SQL Debugging dialog box, on the Debugging Parameters tab, enter the data for debugging and click Debug.

   Enter the payload data for debugging based on the data format of the topic. The data format is described as follows:

   • If the topic is a custom topic, the format of the payload data that you enter must be the same as the format of the data reported to the topic.
   • If the topic is a basic communication topic or a Thing Specification Language model communication topic, see Data formats.
   The debugging parameters include Product, Device, Device Tag (you can add or delete key-value pair tags), Topic, and Payload Data.
3. Debug ResultClick the tab to view the result. The debugging result is displayed in JSON format and includes fields such as attribute('b') with a value of "b", payload with a value of {"timeout":"test"}, attribute('a') with a value of "a", attribute('coordinate') for the device coordinate, deviceName for the device name, and timestamp with a value such as 1579004949668.