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 Clauses

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 Clauses

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.

• Comparison operators: >, <, >=, <=, =, and <>

  Comparison operators can be used in the WHERE clause only when the value of the JSON variable is an integer constant. You can compare two JSON variables or compare a JSON variable with a constant. If IS NULL of two JSON variables is true, the comparison result of = is false. Comparison operators can also be used to compare constants. You can also use AND or OR to connect operators.

  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 or equal to 5 and data.tag less than or equal to 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.
  WHERE data.number <> 5 Obtains the information of the target whose data.number is not 5.

• Arithmetic operators: +, -, *, and /

  Arithmetic operators can be used to calculate values.

  For example:

  WHERE data.number + 2 > 5 Extracts information whose number plus 2 is greater than 5.
  WHERE data.tag - 1 < 3 Extracts information whose tag minus 1 is less than 3.
  WHERE data.price * 2 > 100 Extracts information whose price multiplied by 2 is greater than 100.
  WHERE data.total / data.count > 10 Extracts information whose total divided by count is greater than 10.

• Pattern matching operators: LIKE

  Pattern matching operators are used to determine whether a string complies with a specified pattern.

  Wildcards:

  • %: matches a string of any length (including an empty string).
  • _: matches any single character.

  For example:

  WHERE data.name LIKE 'A%' Extracts data whose name starts with 'A'.
  WHERE data.description LIKE '%test%' Extracts data whose description contains test.
  WHERE data.email NOT LIKE '%.com' Extracts data whose email does not end with .com.
  WHERE data.name LIKE 'a_b' Matches all strings whose length is 3 and the second character is _ (any single character), for example, abb and acb.
  WHERE data.name LIKE 'a_b%' Matches all strings that start with a_b.

• String concatenation operator: ||

  String concatenation operators are used to concatenate two or more strings into one string.

  For example:

  WHERE data.first_name || ' ' || data.last_name = 'John Doe' Extracts data where the concatenated value of first_name and last_name is John Doe.

• 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

• Set operators: IN and NOT IN

  Set operators 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')

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)