SQL運算式

建立資料流轉規則時，需編寫SQL來解析和處理裝置上報的JSON資料。二進位格式的資料不做解析，直接透傳。本文主要介紹如何編寫資料流轉規則的SQL運算式。

重要若物模型標識符以數字開頭，不支援使用SQL運算式訪問該物模型，即舊版雲產品流轉功能不支援流轉標識符以數字開頭的物模型資料。

您可使用新版雲產品流轉功能，通過編輯指令碼訪問標識符以數字開頭的物模型，進行資料流轉。具體內容，請參見指令碼編寫說明。

JSON資料可以映射為虛擬表，其中Key對應表的列，Value對應列值，因此可以使用SQL來進行資料處理。資料流轉規則的SQL表達示意圖如下：

資料流轉規則SQL樣本：

處理自訂Topic資料的SQL樣本。
某環境感應器可以採集溫度、濕度及氣壓資料。裝置上報到自訂Topic：/a1hRrzD****/+/user/update的資料如下：
```
{
    "temperature":25.1,
    "humidity":65,
    "pressure":101.5,
    "location":"***,***"
}
```
設定溫度大於38℃時觸發規則，並篩選出裝置名稱、溫度和位置資訊，SQL語句如下：
```
SELECT temperature as t, deviceName() as deviceName, location 
FROM "/a1hRrzD****/+/user/update" 
WHERE temperature > 38
```

處理基礎通訊Topic、物模型通訊Topic資料的SQL樣本。基礎通訊Topic、物模型通訊Topic中的資料流轉到規則引擎後，規則引擎會對資料進行解析，解析後的資料格式，請參見資料格式。

某溫濕度感應器的屬性如下：

溫濕度感應器上報的溫度和濕度屬性資料經規則引擎解析後，資料格式如下：

{
    "deviceType": "TemperatureHumidityDetector", 
    "iotId": "N5KURkKdibnZvSls****000100", 
    "productKey": "a15NNfl****", 
    "gmtCreate": 1564569974224, 
    "deviceName": "N5KURkKdibnZvSls3Yfx", 
    "items": {
        "CurrentHumidity": {
            "value": 70, 
            "time": 1564569974229
        }, 
        "CurrentTemperature": {
            "value": 23.5, 
            "time": 1564569974229
        }
    }
}

重要 SQL查詢時，必須通過items.${屬性標識符}.value來訪問指定屬性的資料。

設定溫度值大於38℃時觸發規則，並篩選出裝置名稱、當前溫度和當前濕度，SQL語句如下：

SELECTdeviceName() as deviceName, items.CurrentHumidity.value as Humidity, items.CurrentTemperature.value as Temperature
FROM "/sysa15NNfl****/N5KUR***/thing/event/property/post"
WHERE items.CurrentTemperature.value > 38

如果是自訂模組（testFB）下屬性，屬性標識符格式為${模組標識符}:${屬性標識符}，訪問指定屬性資料時，必須使用英文雙引號（“”），SQL語句如下：

SELECTdeviceName() as deviceName, "items.testFB:CurrentHumidity.value" as Humidity, "items.testFB:CurrentTemperature.value" as Temperature
FROM "/sysa15NNfl****/N5KUR***/thing/event/property/post"
WHERE "items.testFB:CurrentTemperature.value" > 38

SELECT

JSON資料格式
SELECT語句中的欄位，可以使用上報訊息的payload解析結果，即JSON中的索引值，也可以使用SQL內建的函數，如deviceName()。規則引擎內建的SQL函數，請參見函數列表。
支援*和函數的組合。不支援子SQL查詢。
上報的JSON資料格式，可以是數組或者嵌套的JSON。SQL語句支援使用JSONPath擷取其中的屬性值，如對於{a:{key1:v1, key2:v2}}，可以通過a.key2擷取到值v2。使用變數時，需要注意單雙引號區別：單引號表示常量，雙引號或不加引號表示變數。如使用單引號'a.key2'，值為a.key2。
本文SQL樣本中：
- 處理自訂Topic資料SQL的SELECT語句SELECT temperature as t, deviceName() as deviceName, location中，temperature和loaction來自於上報資料中的欄位，deviceName()則使用了內建的SQL函數。
- 處理上報屬性Topic資料SQL的SELECT語句SELECT deviceName() as deviceName, items.CurrentHumidity.value as Humidity, items.CurrentTemperature.value as Temperature中，items.CurrentHumidity.value和items.CurrentTemperature.value來自於上報的預設模組屬性資料中的欄位，deviceName()則使用了內建的SQL函數。
  說明 items.testFB:CurrentHumidity.value和items.testFB:CurrentTemperature.value來自於上報的自訂模組屬性資料中的欄位。
位元據格式
- 可填*直接透傳資料。*後不能再使用函數。
- 可使用內建函數，如to_base64(*)函數，將原始payload位元據轉成base64的String提取出來；deviceName()函數，將裝置名稱資訊提取出來。

說明 SELECT語句中最多可包含50個欄位。

FROM

FROM可以填寫為Topic，用於匹配需要處理的裝置訊息來源Topic。Topic中的裝置名稱（deviceName）類目可以填寫為萬用字元加號（+），代表當前層級所有類目，即產品下的所有裝置；指定為自訂Topic時，還可以使用萬用字元井號（#），代表Topic中當前層級及之後的所有類目。萬用字元說明，請參見使用自訂Topic通訊。

當來自指定Topic的訊息到達時，訊息的payload資料以JSON格式解析，並根據SQL語句進行處理。如果訊息格式不合法，將忽略此條訊息。您可以使用topic()函數引用具體的Topic值。

以上SQL樣本中：

FROM "/a1hRrzD****/+/user/update"語句表示該SQL僅處理自訂Topic：/a1hRrzD****/+/user/update的訊息。
FROM "/sys/a15NNfl****/N5KURkKdibnZvSls3Yfx/thing/event/property/post"語句表示該SQL僅處理裝置N5KURkKdibnZvSls3Yfx上報屬性的Topic中的訊息。

WHERE

JSON資料格式
規則引發條件，條件運算式。不支援子SQL查詢。WHERE中可以使用的欄位和SELECT語句一致，當接收到對應Topic的訊息時，WHERE語句的結果會作為是否觸發規則的判斷條件。具體條件運算式，請參見以下章節：條件運算式支援列表。
上文兩個樣本中，條件陳述式WHERE temperature > 38表示溫度大於38℃時，才會觸發該規則。
位元據格式
目前二進位格式WHERE語句中，僅支援內建函數及條件運算式，無法使用payload中的欄位。

SQL結果

SQL語句執行完成後，會得到對應的SQL結果，用於下一步轉寄處理。如果payload資料解析過程中出錯，會導致規則運行失敗。

如果要將資料流轉到Table Store中，設定轉寄資料目的地時，需要使用變數格式${運算式} 引用對應的值。

如果以上兩個樣本SQL對應的規則需將資料流轉到Table Store的資料表中，主鍵對應的值可分別配置為：

${t}、${deviceName}和${loaction}。
${deviceName}、${Humidity}和${Temperature}。

數組使用說明

數組運算式需要使用雙引號，用$.表示取jsonObject，$.可以省略，.表示取jsonArray。

例如裝置訊息為：{"a":[{"v":0},{"v":1},{"v":2}]}，不同運算式結果如下：

"a[0]"結果為{"v":0}
"$.a[0]"結果為{"v":0}
".a[0]" 結果為[{"v":0}]

"a[1].v"結果為1
"$.a[1].v"結果為1
".a[1].v"結果為[1]

條件運算式支援列表

操作符	描述	舉例
=	相等	color = 'red'
<>	不等於	color <> 'red'
AND	邏輯與	color = 'red' AND siren = 'on'
OR	邏輯或	color = 'red' OR siren = 'on'
+	算術加法	4 + 5
-	算術減	5 - 4
/	除	20 / 4
*	乘	5 * 4
%	取餘數	20 % 6
<	小於	5 < 6
<=	小於或等於	5 <= 6
>	大於	6 > 5
>=	大於或等於	6 >= 5
函數調用	支援函數，更多資訊，請參見函數列表。	deviceId()
JSON屬性運算式	可以從訊息payload以JSON運算式提取屬性。	state.desired.color,a.b.c[0].d
CASE … WHEN … THEN … ELSE …END	Case運算式（不支援嵌套）。	CASE col WHEN 1 THEN 'Y' WHEN 0 THEN 'N' ELSE '' END as flag
IN	僅支援枚舉，不支援子查詢。	例如：where a in(1,2,3)。不支援以下形式：where a in(select xxx)。
like	匹配某個字元，僅支援`%`萬用字元，代表匹配任一字元串。 like：查詢包含某個字元的資料。 not like：查詢不包含某個字元的資料。	where c1 like '%abc'
not like		where c1 not like '%def%'

調試SQL

若建立資料流轉規則時，資料格式選擇了JSON，可在物聯網平台進行SQL線上調試。調試方法如下。

編寫SQL後，單擊SQL調試。
在SQL調試對話方塊的調試參數頁簽下，輸入用於調試資料，然後單擊調試。
請根據Topic上報的資料格式，輸入調試用的payload資料。資料格式說明：
- 若Topic為自訂Topic，輸入的payload資料格式需與Topic上報的資料格式一致。
- 若Topic為基礎通訊Topic或物模型通訊Topic，請參見資料格式。
單擊調試結果，查看結果。