OneNET designs the basic message format of the system as json, of which the data can be mapped to a virtual table, with Key corresponding to the column and Value corresponding to the column value. Rule Engine supports SQL statements to process the data, as shown below:

Examples

When device data point message is selected as source, the basic message format is as follows:

{
    "sysProperty": {
        "messageType": "deviceDatapoint",
        "productId": "90273",
    },
    "appProperty":{
        "deviceId": "102839",
        "dataTimestamp": 15980987429000,
        "datastream":"weather"
    },
    "body":{
        "temperature": 30,
        "humidity": "47%"
    }
}

SQL example:

SELECT appProperty.deviceId as did, body as weather FROM /deviceDatapoint/ds WHERE body.temperature > 10

This SQL statement indicates:

Filter the messages of data points uploaded by the device. When the value of the body is greater than 10, extract the appProperty.deviceId and rename it to “did”; and then extract the body property, rename it to “temperature”, and forward the message after reorganization.

The message processed by this SQL outputs the following information:

{
    "did": "90273",
    "weather": {
        "temperature": 30,
        "humidity": "47%"
    }
}

SELECT

The field is marked with an asterisk (*) by default, that is, no extraction and reorganization operations are performed.
The field supports as, and can rename the extracted property, as shown in the example below:
```
SELECT body as temperature
```
Support using json path to fetch property values from nested json, as shown in the example below:
```
SELECT appProperty.deviceId as did
```
Support the extraction and renaming of properties with commas (,). An example is as follows:
```
SELECT appProperty.deviceId as did, appProperty.dataTimestamp as t
```
Do not support Child SQL subquery.
The SELECT statement supports up to 10 fields.

WHERE

The WHERE statement is used to define rule trigger conditions.

Support using json path to fetch property values from nested json, which is the same as SELECT, as shown in the example below:
```
SELECT * WHERE body.temperature > 10
```
Support numerical judgments such as Greater Than, Less Than and Equal To, as shown in the example below:
```
SELECT * WHERE body.temperature = 10
```
Support character matching judgment, as shown in the example below:
```
SELECT * WHERE body.humidity = '47%'
```
Support “AND” and “OR” conditional operations of multiple expressions. An example is as follows:
```
SELECT * WHERE body.temperature > 10 AND body.temperature < 30
```
See the table below for further information about expression support.

Conditional Expression Support List

Operator	Description	Example
＝	Equal to	temperature＝20
! =	Not equal to	temperature! 20
AND	Logical AND	temperature＝20 AND country＝'CN'
OR	Logical OR	temperature＝20 OR country＝'CN'
( )	Give priority to evaluating expressions in parentheses	temperature＝20 AND (country＝'CN' OR online ＝ true)
+	Arithmetic addition	4+5
-	Arithmetic subtraction	5-4
/	Arithmetic division	10/5
*	Arithmetic multiplication	2*5
%	Remainder	20%7
\<	Less than	5\<6
\<=	Less than or equal to	5\<=6
>	Greater than	5>4
>=	Greater than or equal to	5>=4

SQL Statement

Examples

SELECT

WHERE

Conditional Expression Support List

个搜索结果，搜索内容 “”

0 个搜索结果，搜索内容 “”