You can write SQL statements to parse and process data when you create data forwarding rules. Binary data will not be parsed, but directly passed through to targets. This topic describes 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 being mapped to a virtual table, a JSON data record can be processed using SQL. The following example demonstrates how to represent a data forwarding rule as a SQL statement.

For example, an environmental sensor that is typically used for fire detection and collecting temperature, humidity, and atmospheric pressure data, reports the following data:
{
"temperature":25.1
"humidity":65
"pressure":101.5
"location":"xxx,xxx"
}
Assume that you need to set an alarm that is triggered when the temperature is higher than 38°C and the humidity is lower than 40%, write the following SQL statement as a rule:
SELECT temperature as t, deviceName() as deviceName, location FROM /ProductA/+/update WHERE temperature > 38 and humidity < 40
If the reported data meets the rule parameters, the rule is triggered and the temperature data is parsed to obtain the information about temperature, device name, and location for further processing.
FROM clause
You can enter a topic in the FROM clause. You can enter a wildcard character +
that includes all topics on the current category level to match the topic whose device
messages need to be processed. When a message that matches the specified topic is
received, only the message payload that is in the JSON format can be parsed and then
processed by the SQL statement that you have defined. Invalid messages are ignored.
You can use the topic()
function to reference a specific topic.
In this example, the "FROM /ProductA/+/update" clause indicates that only messages that match the /ProductA/+/update format are processed. For more information about matching rules, see Topic.
SELECT statement
- JSON data
In the SELECT statement, you can use the result of parsing the payload of the reported message that represents the keys and values in the JSON data. You can also use built-in functions in the SQL statement, such as
deviceName()
.You can combine
*
with functions. SQL subqueries are not supported.The reported JSON data can be an array or nested JSON data. You can also use a JSONPath expression to obtain values in the reported data record. For example, for a payload
{a:{key1:v1, key2:v2}}
, you can obtain the valuev2
by specifyinga.key2
as the JSON path. When specifying variables in SQL statements, note 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
represents a constant whose value isa.key2
.For more information about built-in functions, see Functions.
In the statement "SELECT temperature as t, deviceName() as deviceName, location" that is provided in the previous example, temperature and location are the fields in the reported message, and deviceName() is a built-in function.
- Binary data
- Enter
*
to pass through binary data directly. You cannot add a function after*
. - You can use built-in functions. The
to_base64(*)
function converts the payload that is binary data to a base64 string. ThedeviceName()
function extracts the name information of a device.
- Enter
WHERE clause
- JSON data
The WHERE clause is used as the condition for triggering the rule. SQL subqueries are not supported. The fields that can be used in the WHERE clause are the same as those that can be used in the SELECT statement. When a message of the corresponding topic is received, the results obtained using the WHERE clause will be used to determine whether a rule will be triggered. For more information about conditional expressions, see the following table: Supported conditional expressions.
In the previous example, "WHERE temperature > 38 and humidity < 40" indicates that the rule is triggered when the temperature is higher than 38°C and the humidity is lower than 40%.
- Binary data
If the reported message is composed of binary data, you can only use built-in functions and conditional expressions in the WHERE clause. You cannot use the fields in the payload of the reported message.
SQL results
The SQL result returned after the SQL statement is executed will be forwarded. If
an error occurs while parsing the payload of the reported message, the rule execution
fails. In the expression used for data forwarding, you must use ${expression}
to specify the data that you want to forward.
In the previous example, when configuring the data forwarding action, you can use ${t}, ${deviceName}, and ${loaction} to reference the SQL result. For example, if you want to forward the SQL result to Table Store, you can use ${t}, ${deviceName}, and ${loaction}.
Notes on arrays
Array expressions are enclosed with double quotation marks ("). Use $.
to obtain a JSONObject. $.
can be omitted. Use .
to obtain a JSONArray.
{"a":[{"v":0},{"v":1},{"v":2}]}
, results of different expressions are as follows:
- 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"
is1
- The result of
"$.a[1].v"
is1
- The result of
".a[1].v"
is[1]
Supported WHERE expressions
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' |
+ | Addition | 4 + 5 |
- | Subtraction | 5-4 |
/ | Division | 20 / 4 |
* | Multiplication | 5 * 4 |
% | Return 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 call | For more information about supported functions, see Functions. | deviceId() |
Attributes expressed in the JSON format | You can extract attributes from the message payload and express them in the JSON format. | state.desired.color,a.b.c[0].d |
CASE … WHEN … THEN … ELSE … END | CASE expression. Nested expressions are not supported. | CASE col WHEN 1 THEN 'Y' WHEN 0 THEN 'N' ELSE '' END as flag |
IN | Only listing 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 specific character. When you use a LIKE operator,
you can only use the % wildcard character to represent a character string.
|
WHERE c1 LIKE '%abc' and WHERE c1 not LIKE '%def%' |