SELECT 子句 - Amazon IoT Core
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

SELECT 子句

Amazon IoT SELECT 子句基本与 ANSI SQL SELECT 子句相同,仅存在一些细微区别。

SELECT 子句支持 数据类型OperatorsFunctionsLiteralsCase 语句JSON 扩展替换模板嵌套对象查询二进制负载

您可以使用 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 扩展

您可以使用函数 (参阅 Functions) 来转换传入负载。您可以使用括号进行分组。例如:

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