SQL Statement

Directory of this page

Examples

SQL SELECT statement

SQL WHERE 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:

Select the message uploaded to the data point by the device. When the value of body is bigger than 10, extract appProperty.deviceId property and rename it as did; extract body property and rename it as temperature, and forward it after message restructuring.

The message processed by this SQL outputs the following information:

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

SELECT

• By default, it’s *, namely, no extraction and restructuring operation is 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 more than one properties by using comma (,), as shown below:

  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" 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