IoT Platform:SQL statements

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 writing SQL statements. The following figure shows the format of SQL statements for data forwarding rules.

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":"***,***"
  }

  If 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 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 multiple properties, as shown in the following figure.

  

  The following sample 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
          }
      }
  }

  Important When you perform SQL queries, you must use the items.${Property identifier}.value variable to access the data of a specified property.

  If 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:

  

  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}. You must enclose the property identifier in a pair of double quotation marks ("") when you specify the property whose data you want to query. The following SQL statement provides an example on how to query property data:

  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

• The following example shows JSON data that contains labels:

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

  You can use asterisks (*) together 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 ("). Each constant is enclosed in a pair of single quotation marks (''). Each variable is enclosed in a pair of double quotation marks (""). A variable may alternatively 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 location fields are obtained from submitted data. deviceName() is a SQL built-in function.
  • 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. deviceName() is a SQL built-in function.
    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 an asterisk (*) to pass through binary data. After you specify an asterisk (*), you can no longer use functions.
  • You can use built-in functions. The to_base64(*) function converts an original binary payload to a Base64 string. The deviceName() function extracts the name of a device.

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 a plus sign (+) as a wildcard for the device name category. The plus sign (+) represents all categories at the current level. In this case, the plus sign (+) represents all devices of a specified product. After you specify a custom topic, you can specify a number sign (#) as a wildcard. The number sign (#) represents all categories at the current level and the subsequent levels. For more information about wildcards, see Custom topics.

When messages are received from a specified topic, the payload data of the messages are converted to data in the JSON format. The JSON data is processed based on the specified SQL statement. If the format of a message is invalid, the message is ignored. 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

• 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 is received from a specified topic, the results that are obtained by using the WHERE clause are used to check whether a rule is triggered. For more information, see the "Supported WHERE expressions" section of this topic.

  In the preceding 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 a received message consists of binary data, you can use only built-in functions and conditions in a WHERE clause. You cannot use the fields in the payload of the message.

SQL results

After the SQL statement is executed, you can forward the query result. If an error occurs when IoT Platform parses the payload of a received message, the rule fails to be executed.

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

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

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

Arrays

Enclose each array expression in a pair of double quotation marks (""). Use $. to obtain a JSON object. $. can be removed. Use . to obtain a JSON array.

Supported WHERE conditions

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 data that is submitted by the custom topic.
   • If you use a basic communication topic or a TSL-based communication topic, see Data formats.
   
3. Click the Commissioning results tab to view the result.