SQL Statement

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:

Filtering data point messages uploaded by a device. When body is assigned a value greater than 10, extract the appProperty.deviceId property and rename it to did; extract the body property, rename it to weather, and forward the message after reorganization.

The message processed by this SQL outputs the following information:

{
    "did": "102839",
    "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 extracting and renaming multiple properties with a comma (,), as shown in the example below:

  SELECT appProperty.deviceId as did, appProperty.dataTimestamp as t
  

• Do not support Child SQL subquery.

• The SELECT statement supports up to 10 fields.

• Support inserting constants, and insert values into the original JSON data.

• Support inserting numeric data and strings.

• All values obtained by inserting built-in functions are of type String.

• Support nested JSON insert, and insert up to 5 nested layers, e.g. SELECT 1 as a.b.c.d.e

• When the insert operation has the same level and name as key in the original data, the original content gets overwritten.

Examples of SELECT are listed as follows:

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 evaluating multiple expressions with conditional logical AND body. Humidity OR operators, as shown in the example below:

  SELECT * WHERE body.temperature > 10 AND body.temperature < 30
  

  See the table below for further information about expression support.

Conditional Expression Support List