SQL语句

提示

创建数据转发规则时，需要编写SQL来解析和处理设备上报的JSON数据，JSON数据具体格式参考数据转发触发事件列表。本文主要介绍如何编写数据转发规则的SQL表达式

SQL语句由SELECT子句和WHERE子句组成，每个子句不能大于500个字符，暂不支持中文等其他字符集。SELECT子句和WHERE子句里的内容大小写敏感，SELECT和WHERE，AS等关键字大小写不敏感。

以设备消息上报为SQL源数据示例：

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

在源数据中，body中的content是设备消息上报的数据，设置当设备上报数据中temperature大于38时触发条件，并筛选出device_id、content，不需要任何其他字段时，SQL语句示例如下：

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

当设备上报消息中temperature大于38度时，会触发转发，转发后的数据格式如下：

{
    "device_id": "d4922d8a-6c8e-4396-852c-164aefa6638f",
    "notify_data.body.content" : {
          "temperature" : 40,
          "humidity" : 24
     }
}

SELECT子句

SELECT子句由SELECT后跟多个SELECT子表达式组成，子表达式可以为*，JSON变量，字符串常量或整数常量。JSON变量后跟一个AS关键字和AS变量，长度不超过32个字符。如果使用常量或函数，则必须使用AS指定名称。

JSON变量

JSON变量支持大小写字母，数字，下划线和中划线，为了和减号的意思区分，当使用中划线的时候，请将JSON变量使用双引号进行引用，如："msg-type"。

Json变量抽取嵌套结构体的数据

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

c.d 即可抽取出字符串e，可以多层嵌套。

AS变量

AS变量由大小写字母组成，大小写敏感。目前支持[a-zA-Z_-]*的模式，如果使用中划线，需要使用双引号进行引用。

常数整数

正如标准的SQL一样，SELECT支持常数整数，常数后必须跟AS子句，如SELECT 5 AS number

注意

常数整数的大小范围：-2147483648~2147483647

常数字符串

正如标准的SQL一样，SELECT支持常数字符串,目前支持[a-zA-Z_-]*的模式，需要使用单引号进行引用，常数后必须跟AS子句，如SELECT 'constant_info' AS str

WHERE

在WHERE子句中，您可以用JSON变量进行布尔运算，进行一些非空判断，然后使用AND， OR关键字把结果组合起来

为空判断 IS NULL， IS NOT NULL

为空判断可以用在WHERE子句中，如果JSON变量抽取不到数据，或者抽取到的数组为空，那么IS NULL成立，反之IS NOT NULL成立。

WHERE data IS NULL

IN， NOT IN

IN运算符可以用于WHERE子句中，如果目标值在指定值集合中，则IN成立，NOT IN反之。IN运算符支持字符串和数字，IN集合只支持常量且各集合元素值类型必须一致，集合元素值类型与目标值类型必须一致。

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

大于小于运算符 > <

大于小于运算符可以用于WHERE子句中，当且仅当JSON变量的值为常量整数时，可以进行两个JSON变量的比较或者JSON变量和常量的比较。大于小于运算符也可以用于常量和常量的比较。也可以通过AND或者OR来连接起来运算

比如

WHERE data.number > 5 # 可以抽取出json表达式大于5的信息
WHERE data.tag < 4 # 可以抽取出json表达式中小于4的信息
WHERE data.number > 5 AND data.tag < 4 # 可以抽取出json表达式data.number大于5的信息并且json表达式data.tag中小于4的信息

=运算符可以用于WHERE子句中，用于JSON变量和JSON变量的比较、JSON变量整数和整数常量的比较、JSON变量字符串和字符串常量的比较。如果两个JSON变量IS NULL成立，那么=比较结果为false。也可以通过AND或者OR来连接起来运算

WHERE data.number = 5 # 可以抽取出json表达式等于5的信息
WHERE data.tag = 4 # 可以抽取出json表达式中等于4的信息
WHERE data.number = 5 OR data.tag = 4 # 可以抽取出json表达式data.number等于5的信息或者json表达式data.tag中等于4的信息

使用限制

表1 SQL语句使用限制

对象	限制
SELECT子句	500个字符
WHERE子句	500个字符

调试SQL语句

物联网平台提供SQL在线调试功能。调试方法如下。

编写SQL后，单击"调试语句”。
在SQL调试对话框的调试参数页签下，输入用于调试数据，然后单击“调试”。

函数列表

规则引擎提供多种函数，您可以在编写SQL时使用这些函数，实现多样化数据处理。

时间相关函数

函数名	携带参数	用途	返回值类型
`date()`	可选，为空则默认获取当前UTC时间	获取时间，格式为`YYYY-MM-DD`	字符串
`datetime()`	可选，为空则默认获取当前UTC时间	获取时间，格式为`YYYY-MM-DD HH:MM:SS`	字符串

可选参数：

date(timestring, [ modifier1, modifier2, ... modifier_n ])
datetime(timestring, [ modifier1, modifier2, ... modifier_n ])

modifier1，modifier2，...，modifier_n:修饰符是可选的。这些与时间字符串一起使用以增加或减少时间，日期或年份：

修饰符	说明
[+-] NNN year	用于指定添加/减去该日期的年数
[+-] NNN months	用于指定添加/减去该日期的月份数
[+-] NNN days	用于指定添加/减去日期的天数
[+-] NNN hours	用于指定添加/减去日期的小时数
[+-] NNN minutes	用于指定添加/减去日期的分钟数
[+-] NNN second	用于指定添加/减去日期的秒数
[+-] NNN.NNNN second	用于指定添加/减去日期的秒数(和小数秒)
start of year	用于将日期移回至年初
start of month	用于将日期移回到月初
start of day	用于将日期移回一天的开始
weekday N	用于将日期向前移动到工作日编号为N的下一个日期 (0 =星期日，1 =星期一，2 =星期二，3 =星期三，4 =星期四，5 =星期五，6 =星期六)
unixepoch	它与DDDDDDDDDD时间字符串一起使用，以将日期解释为UNIX时间(即:自1970-01-01起的秒数)
localtime	用于将日期调整为本地时间，假设时间字符串以UTC表示
utc	假设时间字符串以当地时间表示，则用于将日期调整为utc

示例

select date() as date_now, datetime('now', 'start of month') as start_of_month from table

结果:

{
    "date_now": "2023-05-17",
    "start_of_month": "2023-05-01 00:00:00"
}

字符串相关函数

函数名	携带参数	用途	返回值类型
`length()`	必选，字符串	获取字符串长度	整数
`lower()`	必选，字符串	将字符串转换为小写	字符串
`upper()`	必选，字符串	将字符串转换为大写	字符串
`substr(string, start, length)`	必选，字符串；必选，开始位置；可选，截取长度	返回输入字符串`string`中以第`start`个字符开始，`length`个字符长的子串。开始字符序号为1。若`start`为负，则从右至左数起	字符串
`replace(string, substring, replacement)`	必选，字符串；必选，被替换的子串；必选，替换子串的字符串	返回字符串`string`，其中所有出现的子字符串`substring`都被替换为`replacement`	字符串
`trim(string)`	必选，字符串	返回字符串`string`，其中前导和尾随空格已被删除	字符串
`ltrim(string)`	必选，字符串	返回字符串`string`，其中前导空格已被删除	字符串
`rtrim(string)`	必选，字符串	返回字符串`string`，其中尾随空格已被删除	字符串
`instr(string, substring)`	必选，字符串；必选，子串	返回子串`substring`在字符串`string`中第一次出现的位置。如果子串`substring`不在字符串`string`中，则返回0	整数

示例

输入数据：

{
    "event_type": "device_property_report",
    "instance_id": "yvS58FQ8",    
    "event_time": "2023-04-05 17:24:02",
    "event_id": "b8e9f4f7-d647-40aa-b0a4-b5008b83e69e",
    "body": {
        "device_id": "0a329b1dfb394ef3b",
        "device_name": "device1",
        "time": "2023-04-05 17:23:05",
        "description": "watermeter device",
        "product_id": "6c771e17e5f1f80",
        "product_name": "product1",
        "module_id": "module1",
        "data": {
            "temperature": 25.5,
            "humidity": 80.5         
        }
    }
}

select 
upper(body.device_name) as device_name_upper,
lower(body.device_name) as device_name_lower,
length(body.device_name) as device_name_length,
substr(body.product_id, -6, 6) as produdct_suffix
from table

结果：

{
    "device_name_upper": "DEVICE1",
    "device_name_lower": "device1",
    "device_name_length": 7,
    "produdct_suffix": "5f1f80"
}

数学函数

函数名	携带参数	用途	返回值类型
`abs()`	必选，数字	返回数字的绝对值	数字
`ceil()`	必选，数字	返回大于或等于数字的最小整数	整数
`floor()`	必选，数字	返回小于或等于数字的最大整数	整数
`round()`	必选，数字	返回数字的四舍五入值	整数
`sqrt()`	必选，数字	返回数字的平方根	数字
`power()`	必选，数字；必选，指数	返回数字的指定次幂	数字
`random()`	无	返回介于-2^31和2^31之间的随机整数	字符串
`randomblob(N)`	必选，整数	返回一个包含N个随机字节的BLOB	BLOB

示例

输入数据：

{
    "event_type": "device_property_report",
    "instance_id": "yvS58FQ8",    
    "event_time": "2023-04-05 17:24:02",
    "event_id": "b8e9f4f7-d647-40aa-b0a4-b5008b83e69e",
    "body": {
        "device_id": "0a329b1dfb394ef3b",
        "device_name": "device1",
        "time": "2023-04-05 17:23:05",
        "description": "watermeter device",
        "product_id": "6c771e17e5f1f80",
        "product_name": "product1",
        "module_id": "module1",
        "data": {
            "temperature": 25.5,
            "humidity": 80.5
        }
    }
}

select
abs(body.data.temperature) as temperature_abs,
ceil(body.data.temperature) as temperature_ceil,
floor(body.data.temperature) as temperature_floor,
random() as random_number,
randomblob(10) as random_blob
from table

结果：

{
    "temperature_abs": 25.5,
    "temperature_ceil": 26,
    "temperature_floor": 25,
    "random_number": "8440370465644625511",
    "random_blob": "gojGnuP443H08A=="
}