Updated on 2024-01-17 GMT+08:00

SQL Statements

When creating a data forwarding rule, you must compile SQL statements to parse and process JSON data reported by devices. For details about the JSON data format, see Data Transfer APIs. This topic describes how to compile SQL statements used in data forwarding rules.

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" : "d4922d8a-6c8e-4396-852c-164aefa6638f",
      "product_id" : "ABC123456789",
      "app_id" : "d4922d8a-6c8e-4396-852c-164aefa6638f",
      "gateway_id" : "d4922d8a-6c8e-4396-852c-164aefa6638f",
      "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": "d4922d8a-6c8e-4396-852c-164aefa6638f",
    "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. For example:

    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

Table 1 Restrictions on using SQL statements

Object

Restriction

SELECT clause

500 characters

WHERE clause

500 characters

AS clause

10 AS clauses

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.

Table 2 Function list

Function Name

Parameter

Function

Return Value Type

Restriction

GET_TAG

String tagKey

Obtains tag_value corresponding to a specified tag_key.

GET_TAG('testTagName')

String

-

CONTAINS_TAG

String tagKey

Checks whether the specified tag_key is contained.

CONTAINS_TAG('testTagName')

Boolean

-

GET_SERVICE

String serviceId and boolean fuzzy

Obtains the service. If fuzzy is set to false or left blank, the service with the specified service ID is obtained. If fuzzy is set to true, the service is queried through fuzzy match. If multiple services with the same service ID exist in a message body, the result is not guaranteed.

GET_SERVICE('Battery',true)

JSON structure

Used only for property reporting

GET_SERVICES

String serviceId and boolean fuzzy

Obtains services. If fuzzy is set to false or left blank, services with the specified service ID are obtained. If fuzzy is set to true, services are queried through fuzzy match. The query results are combined into an array.

GET_SERVICES('Battery',true)

JSON array

Used only for property reporting

CONTAINS_SERVICES

String serviceId and boolean fuzzy

If fuzzy is set to false or left empty, the system checks whether the specified service_id exists. If fuzzy is set to true, fuzzy match is used to determine whether service_id in the property contains the specified parameter.

CONTAINS_SERVICES('Battery',true)

Boolean

Used only for property reporting

GET_SERVICE_PROPERTIES

String serviceId

Obtains the properties field of the service with a specific service ID.

GET_SERVICE_PROPERTIES('Battery')

JSON structure

Used only for property reporting

GET_SERVICE_PROPERTY

String serviceId, String propertyKey

Obtain the value of propertyKey in properties of a service with a specific service ID.

Example:

GET_SERVICE_PROPERTY('Battery','batteryLevel')

String

Used only for property reporting

STARTS_WITH

String input, String prefix

Checks whether the value of input starts with prefix.

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

Boolean

-

ENDS_WITH

String input, String suffix

Checks whether the value of input ends with suffix.

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

Boolean

-

CONCAT

String input1, String input2

Concatenates character strings and returns the results.

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

String

-

REPLACE

String input, String target, String replacement

Replaces a part of a character string. That is, replace target in the input with replacement.

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

-

-

SUBSTRING

String input, int beginIndex, int endIndex(required=false)

Obtains the substring of the returned string. That is, the beginIndex (included) to endIndex (excluded) characters of the input value.

Note: endIndex is optional.

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

-

-

LOWER

String input

Converts all values in input to lowercase letters.

LOWER(notify_data.header.app_id)

-

-

UPPER

String input

Converts all values in input to uppercase letters.

UPPER(notify_data.header.app_id)

-

-