SQL表达式

<p>创建规则时,需要编写SQL来处理数据。当规则为二进制时,不对数据内容做处理,直接透传。</p> <p>当规则为JSON数据时,可以将Jsons数据映射为虚拟表,其中Key对应表的列,Value对应列值。为了方便理解,我们将数据处理的某条规则抽象为SQL表达式(类似MySQL语法):</p> <p><img src="https://obs-cn-shanghai.yun.pingan.com/pcp-portal/20191907161121-1b7c9b5c97b3.png" style="height:372px; width:830px" /></p> <p>例如某智能电灯具有开、关、色度、亮度等属性,上报数据内容如下:</p> <p>{</p> <p>&quot;status&quot;:&rdquo;on&rdquo;</p> <p>&quot;bright&quot;:65</p> <p>&quot;color&quot;:3000</p> <p>}</p> <p>假定需要转发&ldquo;电灯在打开状态并且亮度在50以下&rdquo;的数据,可以编写如下的SQL语句:</p> <p>SELECT status, deviceName() as deviceName, bright FROM /ProductA/+/update WHERE status=&rdquo;on&rdquo; and bright &lt; =50</p> <p>当上报的数据符合电灯打开、并且亮度小于等于50时,会触发该规则,并且解析数据中的状态、设备名称、亮度,用于进一步处理。</p> <p><span style="font-size:16px"><strong>SELECT</strong></span></p> <p>1.&nbsp;&nbsp;JSON数据格式</p> <p>SELECT语句中的字段,可以使用上报消息的payload中的解析结果(即JSON中的键值),也可以使用SQL内置的函数,比如deviceName()。</p> <p>支持*和函数的组合。不支持子SQL查询。</p> <p>上报的JSON数据格式,可以是数组或者嵌套的JSON。SQL语句支持使用JSONPath获取其中的属性值,如对于{a:{key1:v1, key2:v2}},可以通过a.key2 获取到值v2。值得注意的是,单引号&#39;&#39;和双引号&quot;&quot;均表示常量,不带引号则表示变量,变量区分大小写。</p> <p>操作符不区分大小写,+-*/%均支持大数运算,除法除不尽时保留小数点后9位,%表示求余运算,不支持表示百分比,并且支持中文字符。</p> <p>例如上述的&quot; SELECT status, deviceName() as deviceName, bright FROM /ProductA/+/update WHERE status=&rdquo;on&rdquo; and bright &lt; =50&quot;语句,其中status和bright来自于上报数据中的字段,deviceName()则使用了内置的SQL函数。内置的SQL函数可以参考<a href="https://yun.pingan.com/ssr/help/iot/iotplatform/iotplatform_serviceManual.ruleengine.functionlist">函数列表</a>。</p> <p>2.&nbsp;&nbsp;二进制数据格式</p> <p>a. 可填*直接透传数据。*后不能再使用函数。</p> <p>b. 可使用内置函数,如to_base64(*)函数,将原始Payload二进制数据转成base64String提取出来等。</p> <table border="1" cellpadding="0" cellspacing="0"> <tbody> <tr> <td style="background-color:#ededed"> <p><strong>操作符</strong><strong>&nbsp;</strong></p> </td> <td style="background-color:#ededed"> <p><strong>描述</strong><strong>&nbsp;</strong></p> </td> <td style="background-color:#ededed"> <p><strong>举例</strong><strong>&nbsp;</strong></p> </td> </tr> <tr> <td> <p>&nbsp;+</p> </td> <td> <p>&nbsp;算数加法</p> </td> <td> <p>&nbsp;1+2</p> </td> </tr> <tr> <td> <p>&nbsp;-</p> </td> <td> <p>&nbsp;算数减法</p> </td> <td> <p>&nbsp;2-1</p> </td> </tr> <tr> <td> <p>&nbsp;*</p> </td> <td> <p>&nbsp;乘</p> </td> <td> <p>&nbsp;1*2</p> </td> </tr> <tr> <td> <p>&nbsp;/</p> </td> <td> <p>&nbsp;除</p> </td> <td> <p>&nbsp;1/2</p> </td> </tr> <tr> <td> <p>&nbsp;%</p> </td> <td> <p>&nbsp;求余</p> </td> <td> <p>&nbsp;1%2</p> </td> </tr> <tr> <td> <p>&nbsp;( )</p> </td> <td> <p>&nbsp;括号运算</p> </td> <td> <p>&nbsp;(1+2)*3</p> </td> </tr> <tr> <td> <p>函数调用</p> </td> <td> <p>&nbsp;支持函数,详细列表请参考<a href="https://yun.pingan.com/ssr/help/iot/iotplatform/iotplatform_serviceManual.ruleengine.functionlist">函数列表</a></p> </td> <td> <p>&nbsp;deviceName()</p> </td> </tr> <tr> <td> <p>JSON属性表达式</p> </td> <td> <p>&nbsp;可以使用JSON path引用属性</p> </td> <td> <p>&nbsp;status.light.temperature</p> </td> </tr> <tr> <td> <p>数学常量</p> </td> <td> <p>&nbsp;数学计算中的圆周率PI和无理数E</p> </td> <td> <p>&nbsp;PI=3.141592653589793 and E=2.718281828459045</p> </td> </tr> <tr> <td> <p>AS/as</p> </td> <td> <p>&nbsp;别名</p> </td> <td> <p>比如 deviceName() a 或者 deviceName() AS a</p> </td> </tr> <tr> <td> <p>CASE/case</p> </td> <td> <p>&nbsp;case语句</p> </td> <td> <p>比如&nbsp;select case&nbsp;a&nbsp;when&nbsp;1+2&nbsp;then&nbsp;a+b&nbsp;when&nbsp;abs(a+1)&nbsp;then&nbsp;&#39;yes&#39;&nbsp;when&nbsp;&quot;13&quot;&nbsp;then&nbsp;sin(a*b)&nbsp;when&nbsp;a*b&nbsp;then&nbsp;cos(a*b)&nbsp;else&nbsp;&#39;no&#39;&nbsp;end alias</p> </td> </tr> <tr> <td> <p>json数组</p> </td> <td> <p>&nbsp;支持json数组引用</p> </td> <td> <p>比如{&quot;a&quot;:[{&quot;a1&quot;:12},{&quot;a2&quot;:13}]},a[0]表示{&quot;a1&quot;:12},a[0].a1表示12,数组索引从0开始</p> </td> </tr> </tbody> </table> <p><span style="font-size:16px"><strong>FROM</strong></span></p> <p>FROM 可以是Topic信息。Topic中的设备名(deviceName)一级类目可以填写通配符<code>+</code>(代表本级所有类目),用于匹配需要处理的设备消息Topic。</p> <p>当有符合Topic规则的消息到达时,消息的payload数据以JSON格式解析,并根据SQL语句进行处理(如果消息格式不合法,将忽略此消息)。您可以使topic()用函数引用具体的Topic值。</p> <p>上文例子中,&quot;FROM /ProductA/+/update&quot;语句表示该SQL仅处理符合/ProductA/+/update格式的消息。</p> <p><span style="font-size:16px"><strong>WHERE</strong></span></p> <p>1.&nbsp;JSON数据格式</p> <p>WHERE为规则触发条件。不支持子SQL查询。WHERE中可以使用的字段和SELECT语句一致。当接收到对应Topic的消息时,WHERE语句的结果会作为是否触发规则的判断条件。具体条件表达式列表见下方表格。</p> <p>上文例子中 &quot; WHERE status=&rdquo;on&rdquo; and bright &lt; =50&quot; 表示状态为开并且亮度小于等于50时,才会触发该规则,执行配置。</p> <p>2.&nbsp;&nbsp;&nbsp;二进制数据格式</p> <p>目前二进制格式WHERE语句中仅支持内置函数及条件表达式,无法使用payload中的字段。</p> <table border="1" cellpadding="0" cellspacing="0" style="width:100%"> <tbody> <tr> <td style="background-color:#ededed"> <p><strong>操作符</strong><strong>&nbsp;</strong></p> </td> <td style="background-color:#ededed"> <p><strong>描述</strong><strong>&nbsp;</strong></p> </td> <td style="background-color:#ededed"> <p><strong>举例</strong><strong>&nbsp;</strong></p> </td> </tr> <tr> <td> <p>&nbsp;=</p> </td> <td> <p>&nbsp;相等</p> </td> <td> <p>&nbsp;light=&quot;on&quot;</p> </td> </tr> <tr> <td> <p>&nbsp;&gt;</p> </td> <td> <p>&nbsp;大于</p> </td> <td> <p>&nbsp;2&gt;1</p> </td> </tr> <tr> <td> <p>&nbsp;&lt;</p> </td> <td> <p>&nbsp;小于</p> </td> <td> <p>&nbsp;1&lt;2</p> </td> </tr> <tr> <td> <p>&nbsp;&gt;=</p> </td> <td> <p>&nbsp;大于等于</p> </td> <td> <p>&nbsp;2&gt;=1</p> </td> </tr> <tr> <td> <p>&nbsp;&lt;=</p> </td> <td> <p>&nbsp;小于等于</p> </td> <td> <p>&nbsp;1&lt;=2</p> </td> </tr> <tr> <td> <p>&nbsp;!=</p> </td> <td> <p>&nbsp;不等于</p> </td> <td> <p>&nbsp;1!=2</p> </td> </tr> <tr> <td> <p>&nbsp;AND/and</p> </td> <td> <p>&nbsp;逻辑与</p> </td> <td> <p>&nbsp;light=&quot;on&quot; and temperature&gt;16</p> </td> </tr> <tr> <td> <p>&nbsp;OR/or</p> </td> <td> <p>&nbsp;逻辑或</p> </td> <td> <p>&nbsp;light=&quot;on&quot; or temperature&gt;16</p> </td> </tr> <tr> <td> <p>&nbsp;+</p> </td> <td> <p>&nbsp;算数加法</p> </td> <td> <p>&nbsp;1+2=3</p> </td> </tr> <tr> <td> <p>&nbsp;-</p> </td> <td> <p>&nbsp;算数减法</p> </td> <td> <p>&nbsp;2-1=1</p> </td> </tr> <tr> <td> <p>&nbsp;*</p> </td> <td> <p>&nbsp;乘</p> </td> <td> <p>&nbsp;1*2=2</p> </td> </tr> <tr> <td> <p>&nbsp;/</p> </td> <td> <p>&nbsp;除</p> </td> <td> <p>&nbsp;1/2=0.5</p> </td> </tr> <tr> <td> <p>&nbsp;%</p> </td> <td> <p>&nbsp;求余</p> </td> <td> <p>&nbsp;1%2=1</p> </td> </tr> <tr> <td> <p>&nbsp;( )</p> </td> <td> <p>&nbsp;括号运算</p> </td> <td> <p>&nbsp;(1+2)*3=6</p> </td> </tr> <tr> <td> <p>&nbsp;函数调用</p> </td> <td> <p>&nbsp;支持函数,详细列表请参考<a href="https://yun.pingan.com/ssr/help/iot/iotplatform/iotplatform_serviceManual.ruleengine.functionlist">函数列表</a></p> </td> <td> <p>&nbsp;deviceName()</p> </td> </tr> <tr> <td> <p>&nbsp;JSON属性表达式</p> </td> <td> <p>&nbsp;可以使用JSON path引用属性</p> </td> <td> <p>&nbsp;status.light=&quot;on&quot; or status.light.temperature&gt;16</p> </td> </tr> <tr> <td> <p>&nbsp;数学常量</p> </td> <td> <p>&nbsp;数学计算中的圆周率PI和无理数E</p> </td> <td> <p>&nbsp;PI=3.141592653589793 and E=2.718281828459045</p> </td> </tr> <tr> <td> <p>&nbsp;IN/in</p> </td> <td> <p>&nbsp;仅支持枚举,不支持子查询</p> </td> <td> <p>&nbsp;比如where a in(1,2,3),不支持以下形式: where a in(select xxx)</p> </td> </tr> <tr> <td> <p>&nbsp;LIKE/like</p> </td> <td> <p>&nbsp;匹配多个字符或者匹配正则表达式</p> </td> <td> <p>&nbsp;比如where a like &quot;^abc.*$&quot;</p> </td> </tr> <tr> <td> <p>&nbsp;json数组</p> </td> <td> <p>&nbsp;支持json数组引用</p> </td> <td> <p>&nbsp;比如{&quot;a&quot;:[{&quot;a1&quot;:12},{&quot;a2&quot;:13}]},a[0]表示{&quot;a1&quot;:12},a[0].a1表示12,数组索引从0开始</p> </td> </tr> </tbody> </table> <p><span style="font-size:16px"><strong>SQL执行结果</strong></span></p> <p>SQL语句执行完成后,会得到对应的SQL结果,用于下一步转发处理。如果payload数据解析过程中出错则会导致规则运行失败。转发数据动作中的表达式需要使用 ${表达式} 引用对应的值。</p> <p>对于上文例子,配置转发动作时,可以${status}、${deviceName}和${bright}获取SQL解析结果。</p> <p><span style="font-size:16px"><strong>数组使用说明</strong></span></p> <p>支持JSON数组引用,比如{&quot;a&quot;:[{&quot;a1&quot;:12},{&quot;a2&quot;:13}]},a[0]表示{&quot;a1&quot;:12},a[0].a1表示12,数组索引从0开始。</p>
以上内容是否解决了您的问题?
请补全提交信息!
咨询·建议

电话咨询 - 7x24 小时

400-151-8800

邮件咨询

cloud@pingan.com

在线客服

7x24 小时,急速解答

工单支持

解决云产品相关技术问题