SELECT 子句 - AWS IoT Core

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

SELECT 子句

SELE AWS IoT CT 子句基本上與 ANSI SQL SELECT 子句相同,但有一些細微的差異。

SELECT 子句支援 資料類型運算子函數文字案例陳述式JSON Extensions替代範本 巢狀物件查詢二進位承載

以使用 SELECT 子句來擷取傳入的 MQTT 訊息內的資訊。您也可以使用 SELECT * 來擷取整個傳入訊息的承載。例如:

Incoming payload published on topic 'topic/subtopic': {"color":"red", "temperature":50} SQL statement: SELECT * FROM 'topic/subtopic' Outgoing payload: {"color":"red", "temperature":50}

如果承載為 JSON 物件,您可以參考物件中的鍵。您的傳出承載會包含鍵值組。例如:

Incoming payload published on topic 'topic/subtopic': {"color":"red", "temperature":50} SQL statement: SELECT color FROM 'topic/subtopic' Outgoing payload: {"color":"red"}

您可以使用 AS 關鍵字重新命名索引鍵。例如:

Incoming payload published on topic 'topic/subtopic':{"color":"red", "temperature":50} SQL:SELECT color AS my_color FROM 'topic/subtopic' Outgoing payload: {"my_color":"red"}

您可以選擇多個項目,以逗號分隔即可。例如:

Incoming payload published on topic 'topic/subtopic': {"color":"red", "temperature":50} SQL: SELECT color as my_color, temperature as fahrenheit FROM 'topic/subtopic' Outgoing payload: {"my_color":"red","fahrenheit":50}

您可以選擇多個項目,包括以「*」新增項目至來年的承載。例如:

Incoming payload published on topic 'topic/subtopic': {"color":"red", "temperature":50} SQL: SELECT *, 15 as speed FROM 'topic/subtopic' Outgoing payload: {"color":"red", "temperature":50, "speed":15}

您可以使用 "VALUE" 關鍵字產生傳出的承載不 JSON 物件。使用 SQL 版本 2015-10-08,您只能選取一個項目。使用 SQL 版本 2016-03-23 或更新版本,您也可以選取要作為頂層物件輸出的陣列。

Incoming payload published on topic 'topic/subtopic': {"color":"red", "temperature":50} SQL: SELECT VALUE color FROM 'topic/subtopic' Outgoing payload: "red"

您可以使用 '.' 語法來深入巢狀 JSON 物件傳入的承載。例如:

Incoming payload published on topic 'topic/subtopic': {"color":{"red":255,"green":0,"blue":0}, "temperature":50} SQL: SELECT color.red as red_value FROM 'topic/subtopic' Outgoing payload: {"red_value":255}

如需如何使用 JSON 物件和包含保留字元 (例如數字或連字號 (減號) 字元) 之屬性名稱的相關資訊,請參閱 JSON Extensions

您可以使用函數 (請參閱 函數),將傳入的承載。您可以使用括號進行分組。例如:

Incoming payload published on topic 'topic/subtopic': {"color":"red", "temperature":50} SQL: SELECT (temperature - 32) * 5 / 9 AS celsius, upper(color) as my_color FROM 'topic/subtopic' Outgoing payload: {"celsius":10,"my_color":"RED"}