SQL Statements

SQL Statements

An SQL statement consists of the SELECT and WHERE clauses. Each clause can contain a maximum of 500 characters. Chinese and other character sets are not supported. Contents in the SELECT and WHERE clauses are case-sensitive. However, keywords such as SELECT, WHERE, and AS are case-insensitive.

The following example uses messages reported by a device as the source data.

{
  "resource" : "device.message",
  "event" : "report",
  "event_time" : "20151212T121212Z",
  "notify_data" : {
    "header" : {
      "device_id" : "********",
      "product_id" : "ABC123456789",
      "app_id" : "********",
      "gateway_id" : "********",
      "node_id" : "ABC123456789",
      "tags" : [ {
        "tag_value" : "testTagValue",
        "tag_key" : "testTagName"
      } ]
    },
    "body" : {
      "topic" : "topic",
      "content" : {
          "temperature" : 40,
          "humidity" : 24
      }
    }
  }
}

In the source data, content in the body is the data reported by the device. You can set temperature greater than 38 as the trigger condition, and filter out other fields to obtain only device_id and content. The example SQL statement is as follows:

SELECT notify_data.header.device_id AS device_id, notify_data.body.content WHERE notify_data.body.content.temperature > 38

When the temperature reported by the device is higher than 38°C, data forwarding is triggered. The data format after forwarding is as follows:

{
    "device_id": "********",
    "notify_data.body.content" : {
          "temperature" : 40,
          "humidity" : 24
     }
}

SELECT Clause

The SELECT clause consists of SELECT followed by multiple SELECT subexpressions, which can be *, JSON variables, string constants, or integer constants. A JSON variable is followed by an AS keyword and an AS variable, 32 characters in total. If a constant or function is used, you must use AS to specify the name.

• JSON variable

  A JSON variable can contain letters, digits, underscores (_), and hyphens (-). To distinguish a hyphen (-) from the minus sign, use double quotation marks (") to enclose the JSON variable with a hyphen, for example, "msg-type".

  The JSON variable extracts data of the nested structure.

  {
    "a":"b",
    "c": {
      "d" : "e"
    }
  }

  c.d can be used to extract character string e, which can be nested at multiple layers.

• AS variable

  An AS variable consists of letters and is case sensitive. The variable [a-zA-Z_-]* is supported. If a hyphen (-) is used, enclose it with double quotation marks (").

• Constant integer

  SELECT supports constant integers, which must be followed by an AS clause. For example:

  

  Value range of the constant integer: –2147483648 to 2147483647

  SELECT 5 AS number

• Constant character string

  SELECT supports constant character strings, which must meet the [a-zA-Z_-]* expression. The character strings must be enclosed in single quotation marks (') and followed by an AS clause.

  

  A string can contain up to 50 characters.

  SELECT 'constant_info' AS str

WHERE Clause

In the WHERE clause, you can perform Boolean operations using JSON variables, make some non-null judgments, and combine the results using AND or OR.

• IS NULL and IS NOT NULL

  Null judgment can be used in the WHERE clause. If the JSON variable cannot extract data or the extracted array is empty, IS NULL is true. Otherwise, IS NOT NULL is true.

  WHERE data IS NULL

• IN and NOT IN

  The IN operator can be used in the WHERE clause. If the target value is in the specified value set, IN is true. Otherwise, NOT IN is true. The IN operator supports strings and numbers, the IN set only supports constants. The types of all elements in the IN set must be the consistent and be the same as the type of the target value.

  WHERE notify_data.header.product_id IN ('productId1','productId2')

• Operators > <

  The greater than (>) and less than (<) operators can be used in the WHERE clause. The operators can be used between two JSON variables, between a JSON variable and a constant, or between a constant and a constant only when the value of a JSON variable is a constant integer. The operators can be used together with AND or OR.

  For example:

  WHERE data.number > 5 Obtains the information of the target whose data.number is greater than 5.
  WHERE data.tag < 4 Obtains the information of the target whose data.tag is less than 4.
  WHERE data.number > 5 AND data.tag < 4 Obtains the information of the target whose data.number is greater than 5 and data.tag less than 4.

• Equals sign (=)

  The equals sign (=) can be used in the WHERE clause for comparison between JSON variables, between JSON variable integers and integer constants, and between JSON variable strings and string constants. If IS NULL for the two JSON variables is true, the comparison result of the equals sign (=) is false. The operators can be used together with AND or OR.

  WHERE data.number = 5 Obtains the information of the target whose data.number is 5.
  WHERE data.tag = 4 Obtains the information of the target whose data.tag is 4.
  WHERE data.number = 5 OR data.tag = 4 Obtains the information of the target whose data.number is 5 or data.tag is 4.

Constraints

Debugging SQL Statements

The IoT platform provides the online SQL debugging function. To debug SQL statements, perform the following operations:

1. After compiling the SQL statements, click Debug.

2. On the Debug Parameters tab page, enter the data to debug, and click Start Debugging.

Function List

Multiple functions are used in rules. You can use these functions when compiling SQL statements to implement diversified data processing.

GET_TAG('testTagName')

CONTAINS_TAG('testTagName')

GET_SERVICE('Battery',true)

GET_SERVICES('Battery',true)

CONTAINS_SERVICES('Battery',true)

GET_SERVICE_PROPERTIES('Battery')

GET_SERVICE_PROPERTY('Battery','batteryLevel')

STARTS_WITH('abcd','abc')
STARTS_WITH(notify_data.header.device_id,'abc')
STARTS_WITH(notify_data.header.device_id,notify_data.header.product_id)

ENDS_WITH('abcd','bcd')
ENDS_WITH(notify_data.header.device_id,'abc')
ENDS_WITH(notify_data.header.device_id,notify_data.header.node_id)

CONCAT('ab','cd')
CONCAT(notify_data.header.device_id,'abc')
CONCAT(notify_data.header.product_id,notify_data.header.node_id)

REPLACE(notify_data.header.node_id,'nodeId','IMEI')

SUBSTRING(notify_data.header.device_id,3)
SUBSTRING(notify_data.header.device_id,3,12)

LOWER(notify_data.header.app_id)

UPPER(notify_data.header.app_id)