# AWS IoT SQL reference In AWS IoT, rules are defined using an SQL-like syntax. SQL statements are composed of three types of clauses: **SET** (Optional) Defines variables that you can reuse throughout SQL statements and substitution templates. Assign values to variables using expressions. Reference these variables in SELECT and WHERE clauses, and in action substitution templates. The SET clause supports [Data types](iot-sql-data-types.md), [Operators](iot-sql-operators.md), [Functions](iot-sql-functions.md), [Literals](iot-sql-literals.md), [Case statements](iot-sql-case.md), [JSON extensions](iot-sql-json.md), [Variables](iot-sql-set.md#iot-sql-set-usage), and [Nested object queries](iot-sql-nested-queries.md). **SELECT** (Required) Extracts information from the payload of an incoming message and performs transformations on the information. The messages to use are identified by the [topic filter](topics.md#topicfilters) specified in the FROM clause. The SELECT clause supports [Data types](iot-sql-data-types.md), [Operators](iot-sql-operators.md), [Functions](iot-sql-functions.md), [Literals](iot-sql-literals.md), [Case statements](iot-sql-case.md), [JSON extensions](iot-sql-json.md), [Substitution templates](iot-substitution-templates.md), [Variables](iot-sql-set.md#iot-sql-set-usage), [Nested object queries](iot-sql-nested-queries.md), and [Binary payloads](binary-payloads.md). **FROM** The MQTT message [topic filter](topics.md#topicfilters) that identifies the messages to extract data from. The rule is activated for each message sent to an MQTT topic that matches the topic filter specified here. Required for rules that are activated by messages that pass through the message broker. Optional for rules that are only activated using the [Basic Ingest](iot-basic-ingest.md) feature. **WHERE** (Optional) Adds conditional logic that determines whether the actions specified by a rule are carried out. The WHERE clause supports [Data types](iot-sql-data-types.md), [Operators](iot-sql-operators.md), [Functions](iot-sql-functions.md), [Literals](iot-sql-literals.md), [Case statements](iot-sql-case.md), [JSON extensions](iot-sql-json.md), [Variables](iot-sql-set.md#iot-sql-set-usage), and [Nested object queries](iot-sql-nested-queries.md). An example SQL statement looks like this: ``` SELECT color AS rgb FROM 'topic/subtopic' WHERE temperature > 50 ``` An example MQTT message (also called an incoming payload) looks like this: ``` { "color":"red", "temperature":100 } ``` If this message is published on the `'topic/subtopic'` topic, the rule is triggered and the SQL statement is evaluated. The SQL statement extracts the value of the `color` property if the `"temperature"` property is greater than 50. The WHERE clause specifies the condition `temperature > 50`. The `AS` keyword renames the `"color"` property to `"rgb"`. The result (also called an *outgoing payload*) looks like this: ``` { "rgb":"red" } ``` This data is then forwarded to the rule's action, which sends the data for more processing. For more information about rule actions, see [AWS IoT rule actions](iot-rule-actions.md). **Note** Comments are not currently supported in AWS IoT SQL syntax. Attribute names with spaces in them can't be used as field names in the SQL statement. While the incoming payload can have attribute names with spaces in them, such names can't be used in the SQL statement. They will, however, be passed through to the outgoing payload if you use a wildcard (\$1) field name specification. # SELECT clause The AWS IoT SELECT clause is essentially the same as the ANSI SQL SELECT clause, with some minor differences. The SELECT clause supports [Data types](iot-sql-data-types.md), [Operators](iot-sql-operators.md), [Functions](iot-sql-functions.md), [Literals](iot-sql-literals.md), [Case statements](iot-sql-case.md), [JSON extensions](iot-sql-json.md), [Variables](iot-sql-set.md#iot-sql-set-usage), [Nested object queries](iot-sql-nested-queries.md), and [Binary payloads](binary-payloads.md). You can use the SELECT clause to extract information from incoming MQTT messages. You can also use `SELECT *` to retrieve the entire incoming message payload. For example: ``` Incoming payload published on topic 'topic/subtopic': {"color":"red", "temperature":50} SQL statement: SELECT * FROM 'topic/subtopic' Outgoing payload: {"color":"red", "temperature":50} ``` If the payload is a JSON object, you can reference keys in the object. Your outgoing payload contains the key-value pair. For example: ``` Incoming payload published on topic 'topic/subtopic': {"color":"red", "temperature":50} SQL statement: SELECT color FROM 'topic/subtopic' Outgoing payload: {"color":"red"} ``` You can use the AS keyword to rename keys. For example: ``` 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"} ``` You can select multiple items by separating them with a comma. For example: ``` 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} ``` You can select multiple items including '\$1' to add items to the incoming payload. For example: ``` 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} ``` You can use the `"VALUE"` keyword to produce outgoing payloads that are not JSON objects. With SQL version `2015-10-08`, you can select only one item. With SQL version `2016-03-23` or later, you can also select an array to output as a top-level object. **Example** ``` Incoming payload published on topic 'topic/subtopic': {"color":"red", "temperature":50} SQL: SELECT VALUE color FROM 'topic/subtopic' Outgoing payload: "red" ``` You can use `'.'` syntax to drill into nested JSON objects in the incoming payload. For example: ``` 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} ``` For information about how to use JSON object and property names that include reserved characters, such as numbers or the hyphen (minus) character, see [JSON extensions](iot-sql-json.md) You can use functions (see [Functions](iot-sql-functions.md)) to transform the incoming payload. You can use parentheses for grouping. For example: ``` 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"} ``` # FROM clause The FROM clause subscribes your rule to a [topic](topics.md#topicnames) or [topic filter](topics.md#topicfilters). Enclose the topic or topic filter in single quotes ('). The rule is triggered for each message sent to an MQTT topic that matches the topic filter specified here. You can subscribe to a group of similar topics using a topic filter. **Example:** Incoming payload published on topic `'topic/subtopic'`: `{temperature: 50}` Incoming payload published on topic `'topic/subtopic-2'`: `{temperature: 50}` SQL: `"SELECT temperature AS t FROM 'topic/subtopic'"`. The rule is subscribed to `'topic/subtopic'`, so the incoming payload is passed to the rule. The outgoing payload, passed to the rule actions, is: `{t: 50}`. The rule is not subscribed to `'topic/subtopic-2'`, so the rule is not triggered for the message published on `'topic/subtopic-2'`. **\$1 Wildcard Example:** You can use the '\$1' (multi-level) wildcard character to match one or more particular path elements: Incoming payload published on topic `'topic/subtopic'`: `{temperature: 50}`. Incoming payload published on topic `'topic/subtopic-2'`: `{temperature: 60}`. Incoming payload published on topic `'topic/subtopic-3/details'`: `{temperature: 70}`. Incoming payload published on topic `'topic-2/subtopic-x'`: `{temperature: 80}`. SQL: `"SELECT temperature AS t FROM 'topic/#'"`. The rule is subscribed to any topic that begins with `'topic'`, so it's executed three times, sending outgoing payloads of `{t: 50}` (for topic/subtopic), `{t: 60}` (for topic/subtopic-2), and `{t: 70}` (for topic/subtopic-3/details) to its actions. It's not subscribed to `'topic-2/subtopic-x'`, so the rule isn't triggered for the `{temperature: 80}` message. **\$1 Wildcard Example:** You can use the '\$1' (single-level) wildcard character to match any one particular path element: Incoming payload published on topic `'topic/subtopic'`: `{temperature: 50}`. Incoming payload published on topic `'topic/subtopic-2'`: `{temperature: 60}`. Incoming payload published on topic `'topic/subtopic-3/details'`: `{temperature: 70}`. Incoming payload published on topic `'topic-2/subtopic-x'`: `{temperature: 80}`. SQL: `"SELECT temperature AS t FROM 'topic/+'"`. The rule is subscribed to all topics with two path elements where the first element is `'topic'`. The rule is executed for the messages sent to `'topic/subtopic'` and `'topic/subtopic-2'`, but not `'topic/subtopic-3/details'` (it has more levels than the topic filter) or `'topic-2/subtopic-x'` (it doesn't start with `topic`). # SET clause Use the SET clause to define variables that store expression results. You can reuse these variables in SELECT and WHERE clauses, and in substitution templates. This helps you avoid duplicating complex expressions and reduce the number of function calls in your SQL statement. The SET clause supports [Data types](iot-sql-data-types.md), [Operators](iot-sql-operators.md), [Functions](iot-sql-functions.md), [Literals](iot-sql-literals.md), [Case statements](iot-sql-case.md), [JSON extensions](iot-sql-json.md), [Variables](#iot-sql-set-usage), and [Nested object queries](iot-sql-nested-queries.md). ## SET clause syntax The SET clause must appear before the SELECT clause in your SQL statement. Use the following syntax: ``` SET @variable_name = expression [, @variable_name2 = expression2] ``` Syntax rules: + Start variable names with `@` + Variable names can contain letters, numbers, and underscores + Variable names can be up to 64 characters long + Multiple variables can be set in a single SET clause, separated by commas + Each variable can only be assigned once (variables are immutable) + The SET keyword can only be used once per SQL statement ## Using variables After you define variables, you can use them in: + SELECT clauses + WHERE clauses + Other SET variable assignments + Action substitution templates + Error action substitution templates + Nested SELECT queries + Function parameters (certain parameters such as roleArn parameters and parameters that switch the mode of a function similar to `transform("enrichArray", attributes, values)` do not support variables) Variables are referenced using the same `@variable_name` syntax used in the SET clause. You can also use JSON extension syntax to access properties of variables that contain objects, such as `@variable_name.property`. ## SET clause examples **Basic variable usage** The following example shows a payload published on topic `device/data`: `{"temp_fahrenheit": 75, "humidity": 60}` SQL statement: ``` SET @temp_celsius = (temp_fahrenheit - 32) * 5 / 9 SELECT @temp_celsius AS celsius, humidity FROM 'device/data' ``` Outgoing payload: `{"celsius": 23.89, "humidity": 60}` **Access members in embedded JSON objects ** The following example shows a payload published on topic `device/data`: `{"device1": {"deviceId":"weather_sensor", "deviceData": {"sensors": {"temp_fahrenheit": 75, "humidity": 60}, "location": [47.606,-122.332]}}}` SQL statement: ``` SET @device_sensor_data = device1.deviceData.sensors SELECT @device_sensor_data.temp_fahrenheit AS temp_fahrenheit, @device_sensor_data.humidity as humidity, device1.deviceId as deviceId FROM 'device/data' ``` Outgoing payload: `{"temp_fahrenheit":75,"humidity":60,"deviceId":"weather_sensor"}` for more information on how to work with JSON extensions, reference [JSON extensions](iot-sql-json.md) **Avoiding duplicate function calls** SET variables help avoid duplicating complex decode operations: ``` SET @decoded_data = decode(encode(*, 'base64'), 'proto', 'schema', 'schema.desc', 'message.proto', 'Message') SELECT @decoded_data.sensor_id, @decoded_data.reading FROM 'device/protobuf' WHERE @decoded_data.reading > 100 ``` Without SET variables, you would need to repeat the decode function three times, which exceeds the function call limits. **Multiple variables** You can define multiple variables in a single SET clause by separating them with commas: ``` SET @user_data = get_user_properties(device_id), @threshold = 50 SELECT @user_data.name, temp_fahrenheit FROM 'sensors/+' WHERE temp_fahrenheit > @threshold AND @user_data.active = true ``` **Using variables in substitution templates** Variables can also be used in action substitution templates, allowing you to reuse computed values across both the SQL statement and rule actions. SQL statement: ``` SET @temp_celsius = (temp_fahrenheit - 32) * 5 / 9 SELECT @temp_celsius AS celsius, humidity FROM 'device/data' ``` Action configuration: ``` { "s3": { "roleArn": "arn:aws:iam::123456789012:role/testRuleRole", "bucketName": "bucket", "key": "temperature-data/${device_id}/temp-${@temp_celsius}C.json" } } ``` In this example, the SET variable `@temp_celsius` is used in a substitution template to construct the key field of the S3 action. **Non-JSON payload usage** SET variables does not support non-JSON payloads directly, so the payload must be encoded or decoded first: ``` SET @encoded_payload = encode(*, 'base64') SELECT @encoded_payload AS raw_data FROM 'device/binary' ``` for more information on how to work with non-JSON payloads, reference [Working with binary payloads](binary-payloads.md) ## SET clause limits The following limits apply to SET variables: + Maximum of 10 unique variables per SQL statement + Maximum variable value size of 128 KiB (minified UTF-8 JSON string) + Maximum total value size of 128 KiB for all variables + Variable names limited to 64 characters + Variables can accept JSON payloads directly as is (non-JSON payloads must first be encoded/decoded) # WHERE clause The WHERE clause determines if the actions specified by a rule are carried out. If the WHERE clause evaluates to true, the rule actions are performed. Otherwise, the rule actions are not performed. The WHERE clause supports [Data types](iot-sql-data-types.md), [Operators](iot-sql-operators.md), [Functions](iot-sql-functions.md), [Literals](iot-sql-literals.md), [Case statements](iot-sql-case.md), [JSON extensions](iot-sql-json.md), [Variables](iot-sql-set.md#iot-sql-set-usage), and [Nested object queries](iot-sql-nested-queries.md). **Example:** Incoming payload published on `topic/subtopic`: `{"color":"red", "temperature":40}`. SQL: `SELECT color AS my_color FROM 'topic/subtopic' WHERE temperature > 50 AND color <> 'red'`. In this case, the rule will be triggered, but the actions specified by the rule will not be performed. There will be no outgoing payload. You can use functions and operators in the WHERE clause. However, you cannot reference any aliases created with the AS keyword in the SELECT. The WHERE clause is evaluated first, to determine if SELECT is evaluated. **Example with non-JSON payload:** Incoming non-JSON payload published on `topic/subtopic`: `80` SQL: ``SELECT decode(encode(*, 'base64'), 'base64') AS value FROM 'topic/subtopic' WHERE decode(encode(*, 'base64'), 'base64') > 50` In this case, the rule will be triggered, and the actions specified by the rule will be performed. The outgoing payload will be transformed by the SELECT clause as a JSON payload `{"value":80}`. # Data types The AWS IoT rules engine supports all JSON data types. **Supported data types** | Type | Meaning | | --- | --- | | Int | A discrete Int. 34 digits maximum. | | Decimal | A `Decimal` with a precision of 34 digits, with a minimum non-zero magnitude of 1E-999 and a maximum magnitude 9.999...E999. Some functions return `Decimal` values with double precision rather than 34-digit precision. With SQL V2 (2016-03-23), numeric values that are whole numbers, such as `10.0`, are processed as an `Int` value (`10`) instead of the expected `Decimal` value (`10.0`). To reliably process whole number numeric values as `Decimal` values, use SQL V1 (2015-10-08) for the rule query statement. | | Boolean | True or False. | | String | A UTF-8 string. | | Array | A series of values that don't have to have the same type. | | Object | A JSON value consisting of a key and a value. Keys must be strings. Values can be any type. | | Null | Null as defined by JSON. It's an actual value that represents the absence of a value. You can explicitly create a Null value by using the Null keyword in your SQL statement. For example: "SELECT NULL AS n FROM 'topic/subtopic'" | | Undefined | Not a value. This isn't explicitly representable in JSON except by omitting the value. For example, in the object `{"foo": null}`, the key "foo" returns NULL, but the key "bar" returns `Undefined`. Internally, the SQL language treats `Undefined` as a value, but it isn't representable in JSON, so when serialized to JSON, the results are `Undefined`. 
 {"foo":null, "bar":undefined}
is serialized to JSON as: 
 {"foo":null}
Similarly, `Undefined` is converted to an empty string when serialized by itself. Functions called with invalid arguments (for example, wrong types, wrong number of arguments, and so on) return `Undefined`. | ## Conversions The following table lists the results when a value of one type is converted to another type (when a value of the incorrect type is given to a function). For example, if the absolute value function "abs" (which expects an `Int` or `Decimal`) is given a `String`, it attempts to convert the `String` to a `Decimal`, following these rules. In this case, 'abs("-5.123")' is treated as 'abs(-5.123)'. **Note** There are no attempted conversions to `Array`, `Object`, `Null`, or `Undefined`. **To decimal** | Argument type | Result | | --- | --- | | Int | A Decimal with no decimal point. | | Decimal | The source value. | | Boolean | Undefined. (You can explicitly use the cast function to transform true = 1.0, false = 0.0.) | | String | The SQL engine tries to parse the string as a Decimal. AWS IoT attempts to parse strings matching the regular expression:^-?\$1d\$1(\$1.\$1d\$1)?((?i)E-?\$1d\$1)?\$1. "0", "-1.2", "5E-12" are all examples of strings that are converted automatically to Decimals. | | Array | Undefined. | | Object | Undefined. | | Null | Null. | | Undefined | Undefined. | **To int** | Argument type | Result | | --- | --- | | Int | The source value. | | Decimal | The source value rounded to the nearest Int. | | Boolean | Undefined. (You can explicitly use the cast function to transform true = 1.0, false = 0.0.) | | String | The SQL engine tries to parse the string as a Decimal. AWS IoT attempts to parse strings matching the regular expression:^-?\$1d\$1(\$1.\$1d\$1)?((?i)E-?\$1d\$1)?\$1. "0", "-1.2", "5E-12" are all examples of strings that are converted automatically to Decimals. AWS IoT attempts to convert the String to a Decimal, and then truncates the decimal places of that Decimal to make an Int. | | Array | Undefined. | | Object | Undefined. | | Null | Null. | | Undefined | Undefined. | **To Boolean** | Argument type | Result | | --- | --- | | Int | Undefined. (You can explicitly use the cast function to transform 0 = False, any\$1nonzero\$1value = True.) | | Decimal | Undefined. (You can explicitly use the cast function to transform 0 = False, any\$1nonzero\$1value = True.) | | Boolean | The original value. | | String | "true"=True and "false"=False (case insensitive). Other string values are Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | **To string** | Argument type | Result | | --- | --- | | Int | A string representation of the Int in standard notation. | | Decimal | A string representing the Decimal value, possibly in scientific notation. | | Boolean | "true" or "false". All lowercase. | | String | The original value. | | Array | The Array serialized to JSON. The resultant string is a comma-separated list, enclosed in square brackets. A String is quoted. A Decimal, Int, Boolean, and Null is not. | | Object | The object serialized to JSON. The resultant string is a comma-separated list of key-value pairs and begins and ends with curly braces. A String is quoted. A Decimal, Int, Boolean, and Null is not. | | Null | Undefined. | | Undefined | Undefined. | # Operators The following operators can be used in SELECT and WHERE clauses. ## AND operator Returns a `Boolean` result. Performs a logical AND operation. Returns true if left and right operands are true. Otherwise, returns false. `Boolean` operands or case insensitive "true" or "false" string operands are required. *Syntax:* ` expression AND expression`. **AND operator** | Left operand | Right operand | Output | | --- | --- | --- | | Boolean | Boolean | Boolean. True if both operands are true. Otherwise, false. | | String/Boolean | String/Boolean | If all strings are "true" or "false" (case insensitive), they are converted to Boolean and processed normally as boolean AND boolean. | | Other value | Other value | Undefined. | ## OR operator Returns a `Boolean` result. Performs a logical OR operation. Returns true if either the left or the right operands are true. Otherwise, returns false. `Boolean` operands or case insensitive "true" or "false" string operands are required. *Syntax:* ` expression OR expression`. **OR operator** | Left operand | Right operand | Output | | --- | --- | --- | | Boolean | Boolean | Boolean. True if either operand is true. Otherwise, false. | | String/Boolean | String/Boolean | If all strings are "true" or "false" (case insensitive), they are converted to Booleans and processed normally as boolean OR boolean. | | Other value | Other value | Undefined. | ## NOT operator Returns a `Boolean` result. Performs a logical NOT operation. Returns true if the operand is false. Otherwise, returns true. A `Boolean` operand or case insensitive "true" or "false" string operand is required. *Syntax:* `NOT expression`. **NOT operator** | Operand | Output | | --- | --- | | Boolean | Boolean. True if operand is false. Otherwise, true. | | String | If string is "true" or "false" (case insensitive), it is converted to the corresponding Boolean value, and the opposite value is returned. | | Other value | Undefined. | ## IN operator Returns a `Boolean` result. You can use the IN operator in a WHERE clause to check if a value matches any value in an array. It returns true if the match is found, and false otherwise. *Syntax:* ` expression IN expression`. **IN operator** | Left operand | Right operand | Output | | --- | --- | --- | | Int/Decimal/String/Array/Object | Array | True if the Integer/Decimal/String/Array/Object element is found in the array. Otherwise, false. | *Example:* ``` SQL: "select * from 'a/b' where 3 in arr" JSON: {"arr":[1, 2, 3, "three", 5.7, null]} ``` In this example, the condition clause `where 3 in arr` will evaluate to true because 3 is present in the array named `arr`. Hence in the SQL statement, `select * from 'a/b'` will execute. This example also shows that the array can be heterogeneous. ## EXISTS operator Returns a `Boolean` result. You can use the EXISTS operator in a conditional clause to test for the existence of elements in a subquery. It returns true if the subquery returns one or more elements and false if the subquery returns no elements. *Syntax:* ` expression`. *Example:* ``` SQL: "select * from 'a/b' where exists (select * from arr as a where a = 3)" JSON: {"arr":[1, 2, 3]} ``` In this example, the condition clause `where exists (select * from arr as a where a = 3)` will evaluate to true because 3 is present in the array named `arr`. Hence in the SQL statement, `select * from 'a/b'` will execute. *Example:* ``` SQL: select * from 'a/b' where exists (select * from e as e where foo = 2) JSON: {"foo":4,"bar":5,"e":[{"foo":1},{"foo":2}]} ``` In this example, the condition clause `where exists (select * from e as e where foo = 2)` will evaluate to true because the array `e` within the JSON object contains the object `{"foo":2}`. Hence in the SQL statement, `select * from 'a/b'` will execute. ## > operator Returns a `Boolean` result. Returns true if the left operand is greater than the right operand. Both operands are converted to a `Decimal`, and then compared. *Syntax:* `expression > expression`. **> operator** | Left operand | Right operand | Output | | --- | --- | --- | | Int/Decimal | Int/Decimal | Boolean. True if the left operand is greater than the right operand. Otherwise, false. | | String/Int/Decimal | String/Int/Decimal | If all strings can be converted to Decimal, then Boolean. Returns true if the left operand is greater than the right operand. Otherwise, false. | | Other value | Undefined. | Undefined. | ## >= operator Returns a `Boolean` result. Returns true if the left operand is greater than or equal to the right operand. Both operands are converted to a `Decimal`, and then compared. *Syntax:* `expression >= expression`. **>= operator** | Left operand | Right operand | Output | | --- | --- | --- | | Int/Decimal | Int/Decimal | Boolean. True if the left operand is greater than or equal to the right operand. Otherwise, false. | | String/Int/Decimal | String/Int/Decimal | If all strings can be converted to Decimal, then Boolean. Returns true if the left operand is greater than or equal to the right operand. Otherwise, false. | | Other value | Undefined. | Undefined. | ## < operator Returns a `Boolean` result. Returns true if the left operand is less than the right operand. Both operands are converted to a `Decimal`, and then compared. *Syntax:* `expression < expression`. **< operator** | Left operand | Right operand | Output | | --- | --- | --- | | Int/Decimal | Int/Decimal | Boolean. True if the left operand is less than the right operand. Otherwise, false. | | String/Int/Decimal | String/Int/Decimal | If all strings can be converted to Decimal, then Boolean. Returns true if the left operand is less than the right operand. Otherwise, false. | | Other value | Undefined | Undefined | ## <= operator Returns a `Boolean` result. Returns true if the left operand is less than or equal to the right operand. Both operands are converted to a `Decimal`, and then compared. *Syntax:* `expression <= expression`. **<= operator** | Left operand | Right operand | Output | | --- | --- | --- | | Int/Decimal | Int/Decimal | Boolean. True if the left operand is less than or equal to the right operand. Otherwise, false. | | String/Int/Decimal | String/Int/Decimal | If all strings can be converted to Decimal, then Boolean. Returns true if the left operand is less than or equal to the right operand. Otherwise, false. | | Other value | Undefined | Undefined | ## <> operator Returns a `Boolean` result. Returns true if both left and right operands are not equal. Otherwise, returns false. *Syntax:* ` expression <> expression`. **<> operator** | Left operand | Right operand | Output | | --- | --- | --- | | Int | Int | True if left operand is not equal to right operand. Otherwise, false. | | Decimal | Decimal | True if left operand is not equal to right operand. Otherwise, false.Int is converted to Decimal before being compared. | | String | String | True if left operand is not equal to right operand. Otherwise, false. | | Array | Array | True if the items in each operand are not equal and not in the same order. Otherwise, false | | Object | Object | True if the keys and values of each operand are not equal. Otherwise, false. The order of keys/values is unimportant. | | Null | Null | False. | | Any value | Undefined | Undefined. | | Undefined | Any value | Undefined. | | Mismatched type | Mismatched type | True. | ## = operator Returns a `Boolean` result. Returns true if both left and right operands are equal. Otherwise, returns false. *Syntax:* ` expression = expression`. **= operator** | Left operand | Right operand | Output | | --- | --- | --- | | Int | Int | True if left operand is equal to right operand. Otherwise, false. | | Decimal | Decimal | True if left operand is equal to right operand. Otherwise, false.Int is converted to Decimal before being compared. | | String | String | True if left operand is equal to right operand. Otherwise, false. | | Array | Array | True if the items in each operand are equal and in the same order. Otherwise, false. | | Object | Object | True if the keys and values of each operand are equal. Otherwise, false. The order of keys/values is unimportant. | | Any value | Undefined | Undefined. | | Undefined | Any value | Undefined. | | Mismatched type | Mismatched type | False. | ## \$1 operator The "\$1" is an overloaded operator. It can be used for string concatenation or addition. *Syntax:* ` expression + expression`. **\$1 operator** | Left operand | Right operand | Output | | --- | --- | --- | | String | Any value | Converts the right operand to a string and concatenates it to the end of the left operand. | | Any value | String | Converts the left operand to a string and concatenates the right operand to the end of the converted left operand. | | Int | Int | Int value. Adds operands together. | | Int/Decimal | Int/Decimal | Decimal value. Adds operands together. | | Other value | Other value | Undefined. | ## - operator Subtracts the right operand from the left operand. *Syntax:* ` expression - expression`. **- operator** | Left operand | Right operand | Output | | --- | --- | --- | | Int | Int | Int value. Subtracts right operand from left operand. | | Int/Decimal | Int/Decimal | Decimal value. Subtracts right operand from left operand. | | String/Int/Decimal | String/Int/Decimal | If all strings convert to decimals correctly, a Decimal value is returned. Subtracts right operand from left operand. Otherwise, returns Undefined. | | Other value | Other value | Undefined. | | Other value | Other value | Undefined. | ## \$1 operator Multiplies the left operand by the right operand. *Syntax:* ` expression * expression`. **\$1 operator** | Left operand | Right operand | Output | | --- | --- | --- | | Int | Int | Int value. Multiplies the left operand by the right operand. | | Int/Decimal | Int/Decimal | Decimal value. Multiplies the left operand by the right operand. | | String/Int/Decimal | String/Int/Decimal | If all strings convert to decimals correctly, a Decimal value is returned. Multiplies the left operand by the right operand. Otherwise, returns Undefined. | | Other value | Other value | Undefined. | ## / operator Divides the left operand by the right operand. *Syntax:* ` expression / expression`. **/ operator** | Left operand | Right operand | Output | | --- | --- | --- | | Int | Int | Int value. Divides the left operand by the right operand. | | Int/Decimal | Int/Decimal | Decimal value. Divides the left operand by the right operand. | | String/Int/Decimal | String/Int/Decimal | If all strings convert to decimals correctly, a Decimal value is returned. Divides the left operand by the right operand. Otherwise, returns Undefined. | | Other value | Other value | Undefined. | ## % operator Returns the remainder from dividing the left operand by the right operand. *Syntax:* ` expression % expression`. **% operator** | Left operand | Right operand | Output | | --- | --- | --- | | Int | Int | Int value. Returns the remainder from dividing the left operand by the right operand. | | String/Int/Decimal | String/Int/Decimal | If all strings convert to decimals correctly, a Decimal value is returned. Returns the remainder from dividing the left operand by the right operand. Otherwise, Undefined. | | Other value | Other value | Undefined. | # Functions You can use the following built-in functions in the SELECT or WHERE clauses of your SQL expressions. The following external functions are billed equivalent to that of a rule action: [https://docs.aws.amazon.com//iot/latest/developerguide/iot-sql-functions.html#iot-func-aws-lambda](https://docs.aws.amazon.com//iot/latest/developerguide/iot-sql-functions.html#iot-func-aws-lambda), [https://docs.aws.amazon.com//iot/latest/developerguide/iot-sql-functions.html#iot-sql-function-get-dynamodb](https://docs.aws.amazon.com//iot/latest/developerguide/iot-sql-functions.html#iot-sql-function-get-dynamodb), [https://docs.aws.amazon.com//iot/latest/developerguide/iot-sql-functions.html#iot-sql-function-get-registry_data](https://docs.aws.amazon.com//iot/latest/developerguide/iot-sql-functions.html#iot-sql-function-get-registry_data), and [https://docs.aws.amazon.com//iot/latest/developerguide/iot-sql-functions.html#iot-sql-function-get-thing-shadow](https://docs.aws.amazon.com//iot/latest/developerguide/iot-sql-functions.html#iot-sql-function-get-thing-shadow). You also get billed for the [https://docs.aws.amazon.com//iot/latest/developerguide/iot-sql-functions.html#iot-sql-decode-base64](https://docs.aws.amazon.com//iot/latest/developerguide/iot-sql-functions.html#iot-sql-decode-base64) function only when you are [decoding a Protobuf message to JSON](https://docs.aws.amazon.com//iot/latest/developerguide/binary-payloads.html#binary-payloads-protobuf). For more details, refer to the [AWS IoT Core pricing page](https://aws.amazon.com/iot-core/pricing/). ## abs(Decimal) Returns the absolute value of a number. Supported by SQL version 2015-10-08 and later. Example: `abs(-5)` returns 5. **** | Argument type | Result | | --- | --- | | Int | Int, the absolute value of the argument. | | Decimal | Decimal, the absolute value of the argument. | | Boolean | Undefined. | | String | Decimal. The result is the absolute value of the argument. If the string cannot be converted, the result is Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## accountid() Returns the ID of the account that owns this rule as a `String`. Supported by SQL version 2015-10-08 and later. Example: `accountid() ` = "123456789012" ## acos(Decimal) Returns the inverse cosine of a number in radians. `Decimal` arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `acos(0)` = 1.5707963267948966 **** | Argument type | Result | | --- | --- | | Int | Decimal (with double precision), the inverse cosine of the argument. Imaginary results are returned as Undefined. | | Decimal | Decimal (with double precision), the inverse cosine of the argument. Imaginary results are returned as Undefined. | | Boolean | Undefined. | | String | Decimal, the inverse cosine of the argument. If the string cannot be converted, the result is Undefined. Imaginary results are returned as Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## asin(Decimal) Returns the inverse sine of a number in radians. `Decimal` arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `asin(0)` = 0.0 **** | Argument type | Result | | --- | --- | | Int | Decimal (with double precision), the inverse sine of the argument. Imaginary results are returned as Undefined. | | Decimal | Decimal (with double precision), the inverse sine of the argument. Imaginary results are returned as Undefined. | | Boolean | Undefined. | | String | Decimal (with double precision), the inverse sine of the argument. If the string cannot be converted, the result is Undefined. Imaginary results are returned as Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## atan(Decimal) Returns the inverse tangent of a number in radians. `Decimal` arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `atan(0)` = 0.0 **** | Argument type | Result | | --- | --- | | Int | Decimal (with double precision), the inverse tangent of the argument. Imaginary results are returned as Undefined. | | Decimal | Decimal (with double precision), the inverse tangent of the argument. Imaginary results are returned as Undefined. | | Boolean | Undefined. | | String | Decimal, the inverse tangent of the argument. If the string cannot be converted, the result is Undefined. Imaginary results are returned as Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## atan2(Decimal, Decimal) Returns the angle, in radians, between the positive x-axis and the (x, y) point defined in the two arguments.  The angle is positive for counter-clockwise angles (upper half-plane, y > 0), and negative for clockwise angles (lower half-plane, y < 0). `Decimal` arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `atan2(1, 0)` = 1.5707963267948966 **** | Argument type | Argument type | Result | | --- | --- | --- | | Int/Decimal | Int/Decimal | Decimal (with double precision), the angle between the x-axis and the specified (x,y) point. | | Int/Decimal/String | Int/Decimal/String | Decimal, the inverse tangent of the point described. If a string cannot be converted, the result is Undefined. | | Other value | Other value | Undefined. | ## aws\$1lambda(functionArn, inputJson) Calls the specified Lambda function passing `inputJson` to the Lambda function and returns the JSON generated by the Lambda function. **Arguments** | Argument | Description | | --- | --- | | functionArn | The ARN of the Lambda function to call. The Lambda function must return JSON data. | | inputJson | The JSON input passed to the Lambda function. To pass nested object queries and literals, you must use SQL version 2016-03-23. | You must grant AWS IoT `lambda:InvokeFunction` permissions to invoke the specified Lambda function. The following example shows how to grant the `lambda:InvokeFunction` permission using the AWS CLI: ``` aws lambda add-permission --function-name "function_name" --region "region" --principal iot.amazonaws.com --source-arn arn:aws:iot:us-east-1:account_id:rule/rule_name --source-account "account_id" --statement-id "unique_id" --action "lambda:InvokeFunction" ``` The following are the arguments for the **add-permission** command: --function-name Name of the Lambda function. You add a new permission to update the function's resource policy. --region The AWS Region of your account. --principal The principal who is getting the permission. This should be `iot.amazonaws.com` to allow AWS IoT permission to call a Lambda function. --source-arn The ARN of the rule. You can use the **get-topic-rule** AWS CLI command to get the ARN of a rule. --source-account The AWS account where the rule is defined. --statement-id A unique statement identifier. --action The Lambda action that you want to allow in this statement. To allow AWS IoT to invoke a Lambda function, specify `lambda:InvokeFunction`. **Important** If you add a permission for an AWS IoT principal without providing the `source-arn` or `source-account`, any AWS account that creates a rule with your Lambda action can trigger rules to invoke your Lambda function from AWS IoT. For more information, see [Lambda Permission Model](https://docs.aws.amazon.com/lambda/latest/dg/intro-permission-model.html). Given a JSON message payload like: ``` { "attribute1": 21, "attribute2": "value" } ``` The `aws_lambda` function can be used to call Lambda function as follows. ``` SELECT aws_lambda("arn:aws:lambda:us-east-1:account_id:function:lambda_function", {"payload":attribute1}) as output FROM 'topic-filter' ``` If you want to pass the full MQTT message payload, you can specify the JSON payload using '\$1', such as the following example. ``` SELECT aws_lambda("arn:aws:lambda:us-east-1:account_id:function:lambda_function", *) as output FROM 'topic-filter' ``` `payload.inner.element` selects data from messages published on topic 'topic/subtopic'. `some.value` selects data from the output that's generated by the Lambda function. **Note** The rules engine limits the execution duration of Lambda functions. Lambda function calls from rules should be completed within 2000 milliseconds. ## bitand(Int, Int) Performs a bitwise AND on the bit representations of the two `Int`(-converted) arguments. Supported by SQL version 2015-10-08 and later. Example: `bitand(13, 5)` = 5 **** | Argument type | Argument type | Result | | --- | --- | --- | | Int | Int | Int, a bitwise AND of the two arguments. | | Int/Decimal | Int/Decimal | Int, a bitwise AND of the two arguments. All non-Int numbers are rounded down to the nearest Int. If any of the arguments cannot be converted to an Int, the result is Undefined. | | Int/Decimal/String | Int/Decimal/String | Int, a bitwise AND of the two arguments. All strings are converted to decimals and are rounded down to the nearest Int. If the conversion fails, the result is Undefined. | | Other value | Other value | Undefined. | ## bitor(Int, Int) Performs a bitwise OR of the bit representations of the two arguments. Supported by SQL version 2015-10-08 and later. Example: `bitor(8, 5)` = 13 **** | Argument type | Argument type | Result | | --- | --- | --- | | Int | Int | Int, the bitwise OR of the two arguments. | | Int/Decimal | Int/Decimal | Int, the bitwise OR of the two arguments. All non-Int numbers are rounded down to the nearest Int. If the conversion fails, the result is Undefined. | | Int/Decimal/String | Int/Decimal/String | Int, the bitwise OR on the two arguments. All strings are converted to decimals and rounded down to the nearest Int. If the conversion fails, the result is Undefined. | | Other value | Other value | Undefined. | ## bitxor(Int, Int) Performs a bitwise XOR on the bit representations of the two `Int`(-converted) arguments. Supported by SQL version 2015-10-08 and later. Example:`bitor(13, 5)` = 8 **** | Argument type | Argument type | Result | | --- | --- | --- | | Int | Int | Int, a bitwise XOR on the two arguments. | | Int/Decimal | Int/Decimal | Int, a bitwise XOR on the two arguments. Non-Int numbers are rounded down to the nearest Int. | | Int/Decimal/String | Int/Decimal/String | Int, a bitwise XOR on the two arguments. strings are converted to decimals and rounded down to the nearest Int. If any conversion fails, the result is Undefined. | | Other value | Other value | Undefined. | ## bitnot(Int) Performs a bitwise NOT on the bit representations of the `Int`(-converted) argument. Supported by SQL version 2015-10-08 and later. Example: `bitnot(13)` = 2 **** | Argument type | Result | | --- | --- | | Int | Int, a bitwise NOT of the argument. | | Decimal | Int, a bitwise NOT of the argument. The Decimal value is rounded down to the nearest Int. | | String | Int, a bitwise NOT of the argument. Strings are converted to decimals and rounded down to the nearest Int. If any conversion fails, the result is Undefined. | | Other value | Other value. | ## cast() Converts a value from one data type to another. Cast behaves mostly like the standard conversions, with the addition of the ability to cast numbers to or from Booleans. If AWS IoT cannot determine how to cast one type to another, the result is `Undefined`. Supported by SQL version 2015-10-08 and later. Format: cast(*value* as *type*). Example: `cast(true as Int) ` = 1 The following keywords might appear after "as" when calling `cast`: **For SQL version 2015-10-08 and 2016-03-23** | Keyword | Result | | --- | --- | | String | Casts value to String. | | Nvarchar | Casts value to String. | | Text | Casts value to String. | | Ntext | Casts value to String. | | varchar | Casts value to String. | | Int | Casts value to Int. | | Integer | Casts value to Int. | | Double | Casts value to Decimal (with double precision). | **Additionally, for SQL version 2016-03-23** | Keyword | Result | | --- | --- | | Decimal | Casts value to Decimal. | | Bool | Casts value to Boolean. | | Boolean | Casts value to Boolean. | Casting rules: **Cast to decimal** | Argument type | Result | | --- | --- | | Int | A Decimal with no decimal point. | | Decimal | The source value. With SQL V2 (2016-03-23), numeric values that are whole numbers, such as `10.0`, return an `Int` value (`10`) instead of the expected `Decimal` value (`10.0`). To reliably cast whole number numeric values as `Decimal` values, use SQL V1 (2015-10-08) for the rule query statement. | | Boolean | true = 1.0, false = 0.0. | | String | Tries to parse the string as a Decimal. AWS IoT attempts to parse strings matching the regex: ^-?\$1d\$1(\$1.\$1d\$1)?((?i)E-?\$1d\$1)?\$1. "0", "-1.2", "5E-12" are all examples of strings that are converted automatically to decimals. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | **Cast to int** | Argument type | Result | | --- | --- | | Int | The source value. | | Decimal | The source value, rounded down to the nearest Int. | | Boolean | true = 1.0, false = 0.0. | | String | Tries to parse the string as a Decimal. AWS IoT attempts to parse strings matching the regex: ^-?\$1d\$1(\$1.\$1d\$1)?((?i)E-?\$1d\$1)?\$1. "0", "-1.2", "5E-12" are all examples of strings that are converted automatically to decimals. AWS IoT attempts to convert the string to a Decimal and round down to the nearest Int. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | **Cast to `Boolean`** | Argument type | Result | | --- | --- | | Int | 0 = False, any\$1nonzero\$1value = True. | | Decimal | 0 = False, any\$1nonzero\$1value = True. | | Boolean | The source value. | | String | "true" = True and "false" = False (case insensitive). Other string values = Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | **Cast to string** | Argument type | Result | | --- | --- | | Int | A string representation of the Int, in standard notation. | | Decimal | A string representing the Decimal value, possibly in scientific notation. | | Boolean | "true" or "false", all lowercase. | | String | The source value. | | Array | The array serialized to JSON. The result string is a comma-separated list enclosed in square brackets. String is quoted. Decimal, Int, and Boolean are not. | | Object | The object serialized to JSON. The JSON string is a comma-separated list of key-value pairs and begins and ends with curly braces. String is quoted. Decimal, Int, Boolean, and Null are not. | | Null | Undefined. | | Undefined | Undefined. | ## ceil(Decimal) Rounds the given `Decimal` up to the nearest `Int`. Supported by SQL version 2015-10-08 and later. Examples: `ceil(1.2)` = 2 `ceil(-1.2)` = -1 **** | Argument type | Result | | --- | --- | | Int | Int, the argument value. | | Decimal | Int, the Decimal value rounded up to the nearest Int. | | String | Int. The string is converted to Decimal and rounded up to the nearest Int. If the string cannot be converted to a Decimal, the result is Undefined. | | Other value | Undefined. | ## chr(String) Returns the ASCII character that corresponds to the given `Int` argument. Supported by SQL version 2015-10-08 and later. Examples: `chr(65)` = "A". `chr(49)` = "1". **** | Argument type | Result | | --- | --- | | Int | The character corresponding to the specified ASCII value. If the argument is not a valid ASCII value, the result is Undefined. | | Decimal | The character corresponding to the specified ASCII value. The Decimal argument is rounded down to the nearest Int. If the argument is not a valid ASCII value, the result is Undefined. | | Boolean | Undefined. | | String | If the String can be converted to a Decimal, it is rounded down to the nearest Int. If the argument is not a valid ASCII value, the result is Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Other value | Undefined. | ## clientid() Returns the ID of the MQTT client sending the message, or `n/a` if the message wasn't sent over MQTT. Supported by SQL version 2015-10-08 and later. Example: `clientid() ` = "123456789012" ## concat() Concatenates arrays or strings. This function accepts any number of arguments and returns a `String` or an `Array`. Supported by SQL version 2015-10-08 and later. Examples: `concat() ` = `Undefined`. `concat(1) ` = "1". `concat([1, 2, 3], 4)` = [1, 2, 3, 4]. `concat([1, 2, 3], "hello")` = [1, 2, 3, "hello"] `concat("con", "cat")` = "concat" `concat(1, "hello")` = "1hello" `concat("he","is","man")` = "heisman" `concat([1, 2, 3], "hello", [4, 5, 6])` = [1, 2, 3, "hello", 4, 5, 6] **** | Number of arguments | Result | | --- | --- | | 0 | Undefined. | | 1 | The argument is returned unmodified. | | 2\$1 | If any argument is an `Array`, the result is a single array containing all of the arguments. If no arguments are arrays, and at least one argument is a `String`, the result is the concatenation of the `String` representations of all the arguments. Arguments are converted to strings using the standard conversions previously listed. | ## cos(Decimal) Returns the cosine of a number in radians. `Decimal` arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `cos(0)` = 1. **** | Argument type | Result | | --- | --- | | Int | Decimal (with double precision), the cosine of the argument. Imaginary results are returned as Undefined. | | Decimal | Decimal (with double precision), the cosine of the argument. Imaginary results are returned as Undefined. | | Boolean | Undefined. | | String | Decimal (with double precision), the cosine of the argument. If the string cannot be converted to a Decimal, the result is Undefined. Imaginary results are returned as Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## cosh(Decimal) Returns the hyperbolic cosine of a number in radians. `Decimal` arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `cosh(2.3)` = 5.037220649268761. **** | Argument type | Result | | --- | --- | | Int | Decimal (with double precision), the hyperbolic cosine of the argument. Imaginary results are returned as Undefined. | | Decimal | Decimal (with double precision), the hyperbolic cosine of the argument. Imaginary results are returned as Undefined. | | Boolean | Undefined. | | String | Decimal (with double precision), the hyperbolic cosine of the argument. If the string cannot be converted to a Decimal, the result is Undefined. Imaginary results are returned as Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## decode(value, decodingScheme) Use the `decode` function to decode an encoded value. If the decoded string is a JSON document, an addressable object is returned. Otherwise, the decoded string is returned as a string. The function returns NULL if the string cannot be decoded. This function supports decoding base64-encoded strings and Protocol Buffer (protobuf) message format. Supported by SQL version 2016-03-23 and later. value A string value or any of the valid expressions, as defined in [AWS IoT SQL reference](iot-sql-reference.md), that return a string. decodingScheme A literal string representing the scheme used to decode the value. Currently, only `'base64'` and `'proto'` are supported. ### Decoding base64-encoded strings In this example, the message payload includes an encoded value. ``` { encoded_temp: "eyAidGVtcGVyYXR1cmUiOiAzMyB9Cg==" } ``` The `decode` function in this SQL statement decodes the value in the message payload. ``` SELECT decode(encoded_temp,"base64").temperature AS temp from 'topic/subtopic' ``` Decoding the `encoded_temp` value results in the following valid JSON document, which allows the SELECT statement to read the temperature value. ``` { "temperature": 33 } ``` The result of the SELECT statement in this example is shown here. ``` { "temp": 33 } ``` If the decoded value was not a valid JSON document, the decoded value would be returned as a string. ### Decoding protobuf message payload You can use the decode SQL function to configure a Rule that can decode your protobuf message payload. For more information, see [Decoding protobuf message payloads](binary-payloads.md#binary-payloads-protobuf). **Important** If you omit the `source‐arn` or `source‐account` when setting permissions for an AWS IoT principal, any AWS account can invoke your Decode function through other AWS IoT rules. To secure your function, see [Bucket policies](https://docs.aws.amazon.com/AmazonS3/latest/userguide/bucket-policies.html) in the *Amazon Simple Storage Service User Guide*. The function signature looks like the following: ``` decode(, 'proto', '', '', '', '') ``` `ENCODED DATA` Specifies the protobuf-encoded data to be decoded. If the entire message sent to the Rule is protobuf-encoded data, you can reference the raw binary incoming payload using `*`. Otherwise, this field must be a base-64 encoded JSON string and a reference to the string can be passed in directly. 1) To decode a raw binary protobuf incoming payload: ``` decode(*, 'proto', ...) ``` 2) To decode a protobuf-encoded message represented by a base64-encoded string 'a.b': ``` decode(a.b, 'proto', ...) ``` `proto` Specifies the data to be decoded in a protobuf message format. If you specify `base64` instead of `proto`, this function will decode base64-encoded strings as JSON. `S3 BUCKET NAME` The name of the Amazon S3 bucket where you’ve uploaded your `FileDescriptorSet` file. `S3 OBJECT KEY` The object key that specifies the `FileDescriptorSet` file within the Amazon S3 bucket. `PROTO NAME` The name of the `.proto` file (excluding the extension) from which the `FileDescriptorSet` file was generated. `MESSAGE TYPE` The name of the protobuf message structure within the `FileDescriptorSet` file, to which the data to be decoded should conform. An example SQL expression using the decode SQL function can look like the following: ``` SELECT VALUE decode(*, 'proto', 's3-bucket', 'messageformat.desc', 'myproto', 'messagetype') FROM 'some/topic' ``` + `*` Represents a binary incoming payload, which conforms to the protobuf message type called `mymessagetype`. + `messageformat.desc` The `FileDescriptorSet` file stored in an Amazon S3 bucket named `s3-bucket`. + `myproto` The original `.proto` file used to generate the `FileDescriptorSet` file named `myproto.proto`. + `messagetype` The message type called `messagetype` (along with any imported dependencies) as defined in `myproto.proto`. ## encode(value, encodingScheme) Use the `encode` function to encode the payload, which potentially might be non-JSON data, into its string representation based on the encoding scheme. Supported by SQL version 2016-03-23 and later. value Any of the valid expressions, as defined in [AWS IoT SQL reference](iot-sql-reference.md). You can specify \$1 to encode the entire payload, regardless of whether it's in JSON format. If you supply an expression, the result of the evaluation is converted to a string before it is encoded. encodingScheme A literal string representing the encoding scheme you want to use. Currently, only `'base64'` is supported. ## endswith(String, String) Returns a `Boolean` indicating whether the first `String` argument ends with the second `String` argument. If either argument is `Null` or `Undefined`, the result is `Undefined`. Supported by SQL version 2015-10-08 and later. Example: `endswith("cat","at")` = true. **** | Argument type 1 | Argument type 2 | Result | | --- | --- | --- | | String | String | True if the first argument ends in the second argument. Otherwise, false. | | Other value | Other value | Both arguments are converted to strings using the standard conversion rules. True if the first argument ends in the second argument. Otherwise, false. If either argument is Null or Undefined, the result is Undefined. | ## exp(Decimal) Returns e raised to the `Decimal` argument. `Decimal` arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `exp(1)` = e. **** | Argument type | Result | | --- | --- | | Int | Decimal (with double precision), e ^ argument. | | Decimal | Decimal (with double precision), e ^ argument. | | String | Decimal (with double precision), e ^ argument. If the String cannot be converted to a Decimal, the result is Undefined. | | Other value | Undefined. | ## floor(Decimal) Rounds the given `Decimal` down to the nearest `Int`. Supported by SQL version 2015-10-08 and later. Examples: `floor(1.2)` = 1 `floor(-1.2)` = -2 **** | Argument type | Result | | --- | --- | | Int | Int, the argument value. | | Decimal | Int, the Decimal value rounded down to the nearest Int. | | String | Int. The string is converted to Decimal and rounded down to the nearest Int. If the string cannot be converted to a Decimal, the result is Undefined. | | Other value | Undefined. | ## get Extracts a value from a collection-like type (Array, String, Object). No conversion is applied to the first argument. Conversion applies as documented in the table to the second argument. Supported by SQL version 2015-10-08 and later. Examples: `get(["a", "b", "c"], 1) ` = "b" `get({"a":"b"}, "a")` = "b" `get("abc", 0)` = "a" **** | Argument type 1 | Argument type 2 | Result | | --- | --- | --- | | Array | Any Type (converted to Int) | The item at the 0-based index of the Array provided by the second argument (converted to Int). If the conversion is unsuccessful, the result is Undefined. If the index is outside the bounds of the Array (negative or >= array.length), the result is Undefined. | | String | Any Type (converted to Int) | The character at the 0-based index of the string provided by the second argument (converted to Int). If the conversion is unsuccessful, the result is Undefined. If the index is outside the bounds of the string (negative or >= string.length), the result is Undefined. | | Object | String (no conversion is applied) | The value stored in the first argument object corresponding to the string key provided as the second argument. | | Other value | Any value | Undefined. | ## get\$1dynamodb(tableName, partitionKeyName, partitionKeyValue, sortKeyName, sortKeyValue, roleArn) Retrieves data from a DynamoDB table. `get_dynamodb()` allows you to query a DynamoDB table while a rule is evaluated. You can filter or augment message payloads using data retrieved from DynamoDB. Supported by SQL version 2016-03-23 and later. `get_dynamodb()` takes the following parameters: tableName The name of the DynamoDB table to query. partitionKeyName The name of the partition key. For more information, see [ DynamoDB Keys](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.CoreComponents.html#HowItWorks.CoreComponents.PrimaryKey). partitionKeyValue The value of the partition key used to identify a record. For more information, see [ DynamoDB Keys](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.CoreComponents.html#HowItWorks.CoreComponents.PrimaryKey). sortKeyName (Optional) The name of the sort key. This parameter is required only if the DynamoDB table queried uses a composite key. For more information, see [ DynamoDB Keys](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.CoreComponents.html#HowItWorks.CoreComponents.PrimaryKey). sortKeyValue (Optional) The value of the sort key. This parameter is required only if the DynamoDB table queried uses a composite key. For more information, see [ DynamoDB Keys](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.CoreComponents.html#HowItWorks.CoreComponents.PrimaryKey). roleArn The ARN of an IAM role that grants access to the DynamoDB table. The rules engine assumes this role to access the DynamoDB table on your behalf. Avoid using an overly permissive role. Grant the role only those permissions required by the rule. The following is an example policy that grants access to one DynamoDB table. **** ``` { "Version":"2012-10-17", "Statement": [ { "Effect": "Allow", "Action": "dynamodb:GetItem", "Resource": "arn:aws:dynamodb:us-east-1:123456789012:table/table-name" } ] } ``` As an example of how to use `get_dynamodb()`, say you have a DynamoDB table that contains device ID and location information for all of your devices connected to AWS IoT. The following SELECT statement uses the `get_dynamodb()` function to retrieve the location for the specified device ID: `SELECT *, get_dynamodb("InServiceDevices", "deviceId", id, "arn:aws:iam::12345678910:role/getdynamo").location AS location FROM 'some/topic' ` **Note** You can call `get_dynamodb()` a maximum of one time per SQL statement. Calling `get_dynamodb()` multiple times in a single SQL statement causes the rule to terminate without invoking any actions. ## get\$1mqtt\$1property(name) References any of the following MQTT5 headers: `contentType`, `payLoadFormatIndicator`, `responseTopic`, and `correlationData`. This function takes any of the following literal strings as an argument: `content_type`, `format_indicator`, `response_topic`, and `correlation_data`. For more information, see the following **Function arguments** table. contentType String: A UTF-8 encoded string that describes the content of the publishing message. payLoadFormatIndicator String: An Enum string value that indicates whether the payload is formatted as UTF-8. Valid values are `UNSPECIFIED_BYTES` and `UTF8_DATA`. responseTopic String: A UTF-8 encoded string that's used as the topic name for a response message. The response topic is used to describe the topic that the receiver should publish to as part of the request-response flow. The topic must not contain wildcard characters. correlationData String: The base64-encoded binary data used by the sender of the Request Message to identify which request the Response Message is for when it's received. The following table shows the acceptable function arguments and their associated return types for the `get_mqtt_property` function: **Function arguments** | SQL | Returned data type (if present) | Returned data type (if not present) | | --- | --- | --- | | get\$1mqtt\$1property("format\$1indicator") | String (UNSPECIFIED\$1BYTES or UTF8\$1DATA) | String (UNSPECIFIED\$1BYTES) | | get\$1mqtt\$1property("content\$1type") | String | Undefined | | get\$1mqtt\$1property("response\$1topic") | String | Undefined | | get\$1mqtt\$1property("correlation\$1data") | base64 encoded String | Undefined | | get\$1mqtt\$1property("some\$1invalid\$1name") | Undefined | Undefined | The following example Rules SQL references any of the following MQTT5 headers: `contentType`, `payLoadFormatIndicator`, `responseTopic`, and `correlationData`. ``` SELECT *, get_mqtt_property('content_type') as contentType, get_mqtt_property('format_indicator') as payloadFormatIndicator, get_mqtt_property('response_topic') as responseTopic, get_mqtt_property('correlation_data') as correlationData FROM 'some/topic' ``` ## get\$1or\$1default(expression, defaultValue) Returns the default value in the second parameter if specified or else returns undefined, when the expression in the first parameter returns null, undefined, or fails. Supported by SQL version 2016-03-23 and later. **Important** `get_or_default` does not support non-JSON payloads directly as is. If you are using a non-JSON payload, use the `encode` or `decode` functions. `get_or_default()` takes the following parameters: expression Any valid expression containing [Data types](iot-sql-data-types.md), [Functions](#iot-sql-functions), [Literals](iot-sql-literals.md), [Variables](iot-sql-set.md#iot-sql-set-usage), [Nested object queries](iot-sql-nested-queries.md), or [JSON extensions](iot-sql-json.md). defaultValue (Optional) Any valid expression containing [Data types](iot-sql-data-types.md), [Functions](#iot-sql-functions), [Literals](iot-sql-literals.md), [Variables](iot-sql-set.md#iot-sql-set-usage), [Nested object queries](iot-sql-nested-queries.md), or [JSON extensions](iot-sql-json.md). This is the value to be returned whenever the first argument returns null, undefined, or fails. Functions that fetch data from customer owned resources, such as get\$1secret, get\$1dynamodb, aws\$1lambda, get\$1thing\$1shadow, decode-protobuf, and machinelearning\$1predict, are not allowed for the defaultValue parameter. The following table shows acceptable function arguments for each argument and their associated outputs: | First argument | Second argument | Output | | --- | --- | --- | | Successful evaluation | Any value or not specified | The first argument value. | | Undefined, Null, or failure | Any value including Undefined or Null | The second argument value. | | Undefined, Null, or failure | not specified | Undefined | **Examples:** Example 1: The following example provides a defaultValue value if a DynamoDB table or query fails: ``` SELECT device_id, get_or_default( get_dynamodb("DeviceConfig", "deviceId", nonExistentId, "arn:aws:iam::123456789012:role/ROLE_NAME"), {"mode": "standard", "timeout": 30, "enabled": true } ) as config FROM 'device/telemetry' ``` Example 2: The following example provides a safe default value "UNKNOWN" if status is undefined: ``` SELECT get_or_default( CASE status WHEN 'active' THEN 'GOOD' WHEN 'inactive' THEN 'BAD'/ ELSE 'UNKNOWN' END, 'UNKNOWN') as status_category FROM 'topic/subtopic' ``` Example 3: The following example shows how you can also use get\$1or\$1default with a single parameter. This is useful in scenarios where you may not have a clear default value, but you do not want your rule execution to fail. ``` SELECT get_dynamodb("DeviceConfig", "deviceId", nonExistentId, "arn:aws:iam::123456789012:role/ROLE_NAME") as config FROM 'device/telemetry' ``` If the DynamoDB lookup fails, the rule execution will fail, and no actions will be executed. If the following SQL is used instead: ``` SELECT get_or_default(get_dynamodb("DeviceConfig", "deviceId", nonExistentId, "arn:aws:iam::123456789012:role/ROLE_NAME")) as config FROM 'device/telemetry' ``` The get\$1or\$1default statement will evaluate to `Undefined`,so in this example the SELECT statement overall will evaluate to `{}` and any rule actions will be attempted. **Important** We recommend following these best practices to maintain security when using this function: Avoid using hardcoded secrets in rule definitions including default values Use AWS Secrets Manager for managing sensitive information ## get\$1registry\$1data(registryAPI, thingName, roleArn) Retrieves AWS IoT thing registry data in an AWS IoT rule. You can read registry data (such as attributes, thing type, and thing groups a device belongs to) and use this information to filter, enrich, or dynamically route messages. Supported by SQL version 2016-03-23 and later. `get_registry_data()` takes the following parameters: registryAPI The registry API being called. Valid values are `DescribeThing` and `ListThingGroupsForThing`. These values must be constant strings. thingName String: The name of the thing whose registry data you want to retrieve. roleArn String: A role ARN with `iot:DescribeThing` permission and/or `iot:ListThingGroupsForThing` permission based on the API being called. The response format of the `get_registry_data` function is the same as the registry API called. For more information, see the [DescribeThing](https://docs.aws.amazon.com//iot/latest/apireference/API_DescribeThing.html) and [ListThingGroupsForThing](https://docs.aws.amazon.com//iot/latest/apireference/API_ListThingGroupsForThing.html) APIs. Example: You can retrieve thing type information to allow filtering the AWS IoT Core lifecycle event messages for things (with the thing name matching the MQTT client id) where thing type is `testenv`. ``` SELECT * FROM '$aws/events/lifecycle/+' WHERE get_registry_data("DescribeThing",clientId,[roleArn]).thingTypeName='testenv' ``` Example: You can retrieve thing attributes for a device with thing name `sensor1` for all messages sent by its gateway device `gateway1`. ``` SELECT *, get_registry_data("DescribeThing","sensor1",[roleArn]).attributes.temperature_threhold AS device1_tempthreshold FROM home1/gateway1/sensor1/# ``` **Note** You can call `get_registry_data()` a maximum of one time per SQL statement and substitution templates for actions and error actions. ## get\$1secret(secretId, secretType, key, roleArn) Retrieves the value of the encrypted `SecretString` or `SecretBinary` field of the current version of a secret in [AWS Secrets Manager](https://docs.aws.amazon.com/secretsmanager/latest/userguide/). For more information about creating and maintaining secrets, see [CreateSecret](https://docs.aws.amazon.com/secretsmanager/latest/apireference/API_CreateSecret.html), [UpdateSecret](https://docs.aws.amazon.com/secretsmanager/latest/apireference/API_UpdateSecret.html), and [PutSecretValue](https://docs.aws.amazon.com/secretsmanager/latest/apireference/API_PutSecretValue.html). `get_secret()` takes the following parameters: secretId String: The Amazon Resource Name (ARN) or the friendly name of the secret to retrieve. secretType String: The secret type. Valid values: `SecretString` \$1 `SecretBinary`. SecretString + For secrets that you create as JSON objects by using the APIs, the AWS CLI, or the AWS Secrets Manager console: + If you specify a value for the `key` parameter, this function returns the value of the specified key. + If you don't specify a value for the `key` parameter, this function returns the entire JSON object. + For secrets that you create as non-JSON objects by using the APIs or the AWS CLI: + If you specify a value for the `key` parameter, this function fails with an exception. + If you don't specify a value for the `key` parameter, this function returns the contents of the secret. SecretBinary + If you specify a value for the `key` parameter, this function fails with an exception. + If you don't specify a value for the `key` parameter, this function returns the secret value as a base64-encoded UTF-8 string. key (Optional) String: The key name inside a JSON object stored in the `SecretString` field of a secret. Use this value when you want to retrieve only the value of a key stored in a secret instead of the entire JSON object. If you specify a value for this parameter and the secret doesn't contain a JSON object inside its `SecretString` field, this function fails with an exception. roleArn String: A role ARN with `secretsmanager:GetSecretValue` and `secretsmanager:DescribeSecret` permissions. **Note** This function always returns the current version of the secret (the version with the `AWSCURRENT` tag). The AWS IoT rules engine caches each secret for up to 15 minutes. As a result, the rules engine can take up to 15 minutes to update a secret. This means that if you retrieve a secret up to 15 minutes after an update with AWS Secrets Manager, this function might return the previous version. This function is not metered, but AWS Secrets Manager charges apply. Because of the secret caching mechanism, the rules engine occasionally calls AWS Secrets Manager. Because the rules engine is a fully distributed service, you might see multiple Secrets Manager API calls from the rules engine during the 15-minute caching window. Examples: You can use the `get_secret` function in an authentication header in an HTTPS rule action, as in the following API key authentication example. ``` "API_KEY": "${get_secret('API_KEY', 'SecretString', 'API_KEY_VALUE', 'arn:aws:iam::12345678910:role/getsecret')}" ``` For more information about the HTTPS rule action, see [HTTP](https-rule-action.md). ## get\$1thing\$1shadow(thingName, shadowName, roleArn) Returns the specified shadow of the specified thing. Supported by SQL version 2016-03-23 and later. thingName String: The name of the thing whose shadow you want to retrieve. shadowName (Optional) String: The name of the shadow. This parameter is required only when referencing named shadows. roleArn String: A role ARN with `iot:GetThingShadow` permission. Examples: When used with a named shadow, provide the `shadowName` parameter. ``` SELECT * from 'topic/subtopic' WHERE get_thing_shadow("MyThing","MyThingShadow","arn:aws:iam::123456789012:role/AllowsThingShadowAccess") .state.reported.alarm = 'ON' ``` When used with an unnamed shadow, omit the `shadowName` parameter. ``` SELECT * from 'topic/subtopic' WHERE get_thing_shadow("MyThing","arn:aws:iam::123456789012:role/AllowsThingShadowAccess") .state.reported.alarm = 'ON' ``` ## get\$1user\$1properties(userPropertyKey) References User Properties, which is one type of property headers supported in MQTT5. userProperty String: A user property is a key-value pair. This function takes the key as an argument and returns an array of all values that match the associated key. **Function arguments** For the following User Properties in the message headers: | Key | Value | | --- | --- | | some key | some value | | a different key | a different value | | some key | value with duplicate key | The following table shows the expected SQL behavior: | SQL | Returned data type | Returned data value | | --- | --- | --- | | get\$1user\$1properties('some key') | Array of String | ['some value', 'value with duplicate key'] | | get\$1user\$1properties('other key') | Array of String | ['a different value'] | | get\$1user\$1properties( ) | Array of key-value pair Objects | [\$1'"some key": "some value"'\$1, \$1"other key": "a different value"\$1, \$1"some key": "value with duplicate key"\$1] | | get\$1user\$1properties('non-existent key') | Undefined | | The following example Rules SQL references User Properties (a type of MQTT5 property header) into the payload: ``` SELECT *, get_user_properties('user defined property key') as userProperty FROM 'some/topic' ``` ## Hashing functions AWS IoT provides the following hashing functions: + md2 + md5 + sha1 + sha224 + sha256 + sha384 + sha512 All hash functions expect one string argument. The result is the hashed value of that string. Standard string conversions apply to non-string arguments. All hash functions are supported by SQL version 2015-10-08 and later. Examples: `md2("hello")` = "a9046c73e00331af68917d3804f70655" `md5("hello")` = "5d41402abc4b2a76b9719d911017c592" ## indexof(String, String) Returns the first index (0-based) of the second argument as a substring in the first argument. Both arguments are expected as strings. Arguments that are not strings are subjected to standard string conversion rules. This function does not apply to arrays, only to strings. Supported by SQL version 2016-03-23 and later. Examples: `indexof("abcd", "bc") ` = 1 ## isNull() Returns true if the argument is the `Null` value. Supported by SQL version 2016-03-23 and later. Examples: `isNull(5) ` = false. `isNull(Null) ` = true. **** | Argument type | Result | | --- | --- | | Int | false | | Decimal | false | | Boolean | false | | String | false | | Array | false | | Object | false | | Null | true | | Undefined | false | ## isUndefined() Returns true if the argument is `Undefined`. Supported by SQL version 2016-03-23 and later. Examples: `isUndefined(5) ` = false. `isUndefined(floor([1,2,3]))) ` = true. **** | Argument type | Result | | --- | --- | | Int | false | | Decimal | false | | Boolean | false | | String | false | | Array | false | | Object | false | | Null | false | | Undefined | true | ## length(String) Returns the number of characters in the provided string. Standard conversion rules apply to non-`String` arguments. Supported by SQL version 2016-03-23 and later. Examples: `length("hi")` = 2 `length(false)` = 5 ## ln(Decimal) Returns the natural logarithm of the argument. `Decimal` arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `ln(e)` = 1. **** | Argument type | Result | | --- | --- | | Int | Decimal (with double precision), the natural log of the argument. | | Decimal | Decimal (with double precision), the natural log of the argument. | | Boolean | Undefined. | | String | Decimal (with double precision), the natural log of the argument. If the string cannot be converted to a Decimal, the result is Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## log(Decimal) Returns the base 10 logarithm of the argument. `Decimal` arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `log(100)` = 2.0. **** | Argument type | Result | | --- | --- | | Int | Decimal (with double precision), the base 10 log of the argument. | | Decimal | Decimal (with double precision), the base 10 log of the argument. | | Boolean | Undefined. | | String | Decimal (with double precision), the base 10 log of the argument. If the String cannot be converted to a Decimal, the result is Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## lower(String) Returns the lowercase version of the given `String`. Non-string arguments are converted to strings using the standard conversion rules. Supported by SQL version 2015-10-08 and later. Examples: `lower("HELLO")` = "hello". `lower(["HELLO"])` = "[\$1"hello\$1"]". ## lpad(String, Int) Returns the `String` argument, padded on the left side with the number of spaces specified by the second argument. The `Int` argument must be between 0 and 1000. If the provided value is outside of this valid range, the argument is set to the nearest valid value (0 or 1000). Supported by SQL version 2015-10-08 and later. Examples: `lpad("hello", 2)` = "`  hello`". `lpad(1, 3)` = "`   1`" **** | Argument type 1 | Argument type 2 | Result | | --- | --- | --- | | String | Int | String, the provided String padded on the left side with a number of spaces equal to the provided Int. | | String | Decimal | The Decimal argument is rounded down to the nearest Int and the String is padded on the left with the specified number of spaces. | | String | String | The second argument is converted to a Decimal, which is rounded down to the nearest Int, and the String is padded with the specified number spaces on the left. If the second argument cannot be converted to an Int, the result is Undefined. | | Other value | Int/Decimal/String | The first value is converted to a String using the standard conversions, and then the LPAD function is applied on that String. If it cannot be converted, the result is Undefined. | | Any value | Other value | Undefined. | ## ltrim(String) Removes all leading white space (tabs and spaces) from the provided `String`. Supported by SQL version 2015-10-08 and later. Example: `Ltrim(" h i ")` = "hi ". **** | Argument type | Result | | --- | --- | | Int | The String representation of the Int with all leading white space removed. | | Decimal | The String representation of the Decimal with all leading white space removed. | | Boolean | The String representation of the Boolean ("true" or "false") with all leading white space removed. | | String | The argument with all leading white space removed. | | Array | The String representation of the Array (using standard conversion rules) with all leading white space removed. | | Object | The String representation of the Object (using standard conversion rules) with all leading white space removed. | | Null | Undefined. | | Undefined | Undefined. | ## machinelearning\$1predict(modelId, roleArn, record) Use the `machinelearning_predict` function to make predictions using the data from an MQTT message based on an Amazon SageMaker AI model. Supported by SQL version 2015-10-08 and later. The arguments for the `machinelearning_predict` function are: modelId The ID of the model against which to run the prediction. The real-time endpoint of the model must be enabled. roleArn The IAM role that has a policy with `machinelearning:Predict` and `machinelearning:GetMLModel` permissions and allows access to the model against which the prediction is run. record The data to be passed into the SageMaker AI Predict API. This should be represented as a single layer JSON object. If the record is a multi-level JSON object, the record is flattened by serializing its values. For example, the following JSON: ``` { "key1": {"innerKey1": "value1"}, "key2": 0} ``` would become: ``` { "key1": "{\"innerKey1\": \"value1\"}", "key2": 0} ``` The function returns a JSON object with the following fields: predictedLabel The classification of the input based on the model. details Contains the following attributes: PredictiveModelType The model type. Valid values are REGRESSION, BINARY, MULTICLASS. Algorithm The algorithm used by SageMaker AI to make predictions. The value must be SGD. predictedScores Contains the raw classification score corresponding to each label. predictedValue The value predicted by SageMaker AI. ## mod(Decimal, Decimal) Returns the remainder of the division of the first argument by the second argument. Equivalent to [remainder(Decimal, Decimal)](#iot-func-remainder). You can also use "%" as an infix operator for the same modulo functionality. Supported by SQL version 2015-10-08 and later. Example: `mod(8, 3)` = 2. **** | Left operand | Right operand | Output | | --- | --- | --- | | Int | Int | Int, the first argument modulo the second argument. | | Int/Decimal | Int/Decimal | Decimal, the first argument modulo the second operand. | | String/Int/Decimal | String/Int/Decimal | If all strings convert to decimals, the result is the first argument modulo the second argument. Otherwise, Undefined. | | Other value | Other value | Undefined. | ## nanvl(AnyValue, AnyValue) Returns the first argument if it is a valid `Decimal`. Otherwise, the second argument is returned. Supported by SQL version 2015-10-08 and later. Example: `Nanvl(8, 3)` = 8. **** | Argument type 1 | Argument type 2 | Output | | --- | --- | --- | | Undefined | Any value | The second argument. | | Null | Any value | The second argument. | | Decimal (NaN) | Any value | The second argument. | | Decimal (not NaN) | Any value | The first argument. | | Other value | Any value | The first argument. | ## newuuid() Returns a random 16-byte UUID. Supported by SQL version 2015-10-08 and later. Example: `newuuid()` = `123a4567-b89c-12d3-e456-789012345000` ## numbytes(String) Returns the number of bytes in the UTF-8 encoding of the provided string. Standard conversion rules apply to non-`String` arguments. Supported by SQL version 2016-03-23 and later. Examples: `numbytes("hi")` = 2 `numbytes("€") ` = 3 ## parse\$1time(String, Long[, String]) Use the `parse_time` function to format a timestamp into a human-readable date/time format. Supported by SQL version 2016-03-23 and later. To convert a timestamp string into milliseconds, see [time\$1to\$1epoch(String, String)](#iot-sql-function-time-to-epoch). The `parse_time` function expects the following arguments: pattern (String) A date/time pattern that follows [Joda-Time formats](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html). timestamp (Long) The time to be formatted in milliseconds since Unix epoch. See function [timestamp()](#iot-function-timestamp). timezone (String) The time zone of the formatted date/time. The default is "UTC". The function supports [Joda-Time time zones](http://joda-time.sourceforge.net/timezones.html). This argument is optional. Examples: When this message is published to the topic 'A/B', the payload `{"ts": "1970.01.01 AD at 21:46:40 CST"}` is sent to the S3 bucket: ``` { "ruleArn": "arn:aws:iot:us-east-2:ACCOUNT_ID:rule/RULE_NAME", "topicRulePayload": { "sql": "SELECT parse_time(\"yyyy.MM.dd G 'at' HH:mm:ss z\", 100000000, 'America/Belize' ) as ts FROM 'A/B'", "ruleDisabled": false, "awsIotSqlVersion": "2016-03-23", "actions": [ { "s3": { "roleArn": "arn:aws:iam::ACCOUNT_ID:rule:role/ROLE_NAME", "bucketName": "BUCKET_NAME", "key": "KEY_NAME" } } ], "ruleName": "RULE_NAME" } } ``` When this message is published to the topic 'A/B', a payload similar to `{"ts": "2017.06.09 AD at 17:19:46 UTC"}` (but with the current date/time) is sent to the S3 bucket: ``` { "ruleArn": "arn:aws:iot:us-east-2:ACCOUNT_ID:rule/RULE_NAME", "topicRulePayload": { "sql": "SELECT parse_time(\"yyyy.MM.dd G 'at' HH:mm:ss z\", timestamp() ) as ts FROM 'A/B'", "awsIotSqlVersion": "2016-03-23", "ruleDisabled": false, "actions": [ { "s3": { "roleArn": "arn:aws:iam::ACCOUNT_ID:rule:role/ROLE_NAME", "bucketName": "BUCKET_NAME", "key": "KEY_NAME" } } ], "ruleName": "RULE_NAME" } } ``` `parse_time()` can also be used as a substitution template. For example, when this message is published to the topic 'A/B', the payload is sent to the S3 bucket with key = "2017": ``` { "ruleArn": "arn:aws:iot:us-east-2:ACCOUNT_ID:rule/RULE_NAME", "topicRulePayload": { "sql": "SELECT * FROM 'A/B'", "awsIotSqlVersion": "2016-03-23", "ruleDisabled": false, "actions": [{ "s3": { "roleArn": "arn:aws:iam::ACCOUNT_ID:rule:role/ROLE_NAME", "bucketName": "BUCKET_NAME", "key": "${parse_time('yyyy', timestamp(), 'UTC')}" } }], "ruleName": "RULE_NAME" } } ``` ## power(Decimal, Decimal) Returns the first argument raised to the second argument. `Decimal` arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Supported by SQL version 2015-10-08 and later. Example: `power(2, 5)` = 32.0. **** | Argument type 1 | Argument type 2 | Output | | --- | --- | --- | | Int/Decimal | Int/Decimal | A Decimal (with double precision), the first argument raised to the second argument's power. | | Int/Decimal/String | Int/Decimal/String | A Decimal (with double precision), the first argument raised to the second argument's power. Any strings are converted to decimals. If any String fails to be converted to Decimal, the result is Undefined. | | Other value | Other value | Undefined. | ## principal() Returns the principal that the device uses for authentication, based on how the triggering message was published. The following table describes the principal returned for each publishing method and protocol. **** | How the message is published | Protocol | Credential type | Principal | | --- | --- | --- | --- | | MQTT client | MQTT | X.509 device certificate | X.509 certificate thumbprint | | AWS IoT console MQTT client | MQTT | IAM user or role | iam-role-id:session-name | | AWS CLI | HTTP | IAM user or role | userid | | AWS IoT Device SDK | MQTT | X.509 device certificate | X.509 certificate thumbprint | | AWS IoT Device SDK | MQTT over WebSocket | IAM user or role | userid | The following examples show the different types of values that `principal()` can return: + X.509 certificate thumbprint: `ba67293af50bf2506f5f93469686da660c7c844e7b3950bfb16813e0d31e9373` + IAM role ID and session name: `ABCD1EFG3HIJK2LMNOP5:my-session-name` + Returns a user ID: `ABCD1EFG3HIJK2LMNOP5` ## rand() Returns a pseudorandom, uniformly distributed double between 0.0 and 1.0. Supported by SQL version 2015-10-08 and later. Example: `rand()` = 0.8231909191640703 ## regexp\$1matches(String, String) Returns true if the string (first argument) contains a match for the regular expression (second argument). If you use `|` in the regular expression, use it with `()`. Examples: `regexp_matches("aaaa", "a{2,}") ` = true. `regexp_matches("aaaa", "b")` = false. `regexp_matches("aaa", "(aaa|bbb)") ` = true. `regexp_matches("bbb", "(aaa|bbb)") ` = true. `regexp_matches("ccc", "(aaa|bbb)") ` = false. **First argument:** | Argument type | Result | | --- | --- | | Int | The String representation of the Int. | | Decimal | The String representation of the Decimal. | | Boolean | The String representation of the Boolean ("true" or "false"). | | String | The String. | | Array | The String representation of the Array (using standard conversion rules). | | Object | The String representation of the Object (using standard conversion rules). | | Null | Undefined. | | Undefined | Undefined. | *Second argument:* Must be a valid regex expression. Non-string types are converted to `String` using the standard conversion rules. Depending on the type, the resultant string might not be a valid regular expression. If the (converted) argument is not valid regex, the result is `Undefined`. ## regexp\$1replace(String, String, String) Replaces all occurrences of the second argument (regular expression) in the first argument with the third argument. Reference capture groups with "\$1". Supported by SQL version 2015-10-08 and later. Example: `regexp_replace("abcd", "bc", "x")` = "axd". `regexp_replace("abcd", "b(.*)d", "$1")` = "ac". **First argument:** | Argument type | Result | | --- | --- | | Int | The String representation of the Int. | | Decimal | The String representation of the Decimal. | | Boolean | The String representation of the Boolean ("true" or "false"). | | String | The source value. | | Array | The String representation of the Array (using standard conversion rules). | | Object | The String representation of the Object (using standard conversion rules). | | Null | Undefined. | | Undefined | Undefined. | *Second argument:* Must be a valid regex expression. Non-string types are converted to `String` using the standard conversion rules. Depending on the type, the resultant string might not be a valid regular expression. If the (converted) argument is not a valid regex expression, the result is `Undefined`. *Third argument:* Must be a valid regex replacement string. (Can reference capture groups.) Non-string types are converted to `String` using the standard conversion rules. If the (converted) argument is not a valid regex replacement string, the result is `Undefined`. ## regexp\$1substr(String, String) Finds the first match of the second parameter (regex) in the first parameter. Reference capture groups with "\$1". Supported by SQL version 2015-10-08 and later. Example: `regexp_substr("hihihello", "hi")` = "hi" `regexp_substr("hihihello", "(hi)*")` = "hihi" **First argument:** | Argument type | Result | | --- | --- | | Int | The String representation of the Int. | | Decimal | The String representation of the Decimal. | | Boolean | The String representation of the Boolean ("true" or "false"). | | String | The String argument. | | Array | The String representation of the Array (using standard conversion rules). | | Object | The String representation of the Object (using standard conversion rules). | | Null | Undefined. | | Undefined | Undefined. | *Second argument:* Must be a valid regex expression. Non-string types are converted to `String` using the standard conversion rules. Depending on the type, the resultant string might not be a valid regular expression. If the (converted) argument is not a valid regex expression, the result is `Undefined`. ## remainder(Decimal, Decimal) Returns the remainder of the division of the first argument by the second argument. Equivalent to [mod(Decimal, Decimal)](#iot-func-mod). You can also use "%" as an infix operator for the same modulo functionality. Supported by SQL version 2015-10-08 and later. Example: `remainder(8, 3)` = 2. **** | Left operand | Right operand | Output | | --- | --- | --- | | Int | Int | Int, the first argument modulo the second argument. | | Int/Decimal | Int/Decimal | Decimal, the first argument modulo the second operand. | | String/Int/Decimal | String/Int/Decimal | If all strings convert to decimals, the result is the first argument modulo the second argument. Otherwise, Undefined. | | Other value | Other value | Undefined. | ## replace(String, String, String) Replaces all occurrences of the second argument in the first argument with the third argument. Supported by SQL version 2015-10-08 and later. Example: `replace("abcd", "bc", "x")` = `"axd"`. `replace("abcdabcd", "b", "x")` = `"axcdaxcd"`. **All arguments** | Argument type | Result | | --- | --- | | Int | The String representation of the Int. | | Decimal | The String representation of the Decimal. | | Boolean | The String representation of the Boolean ("true" or "false"). | | String | The source value. | | Array | The String representation of the Array (using standard conversion rules). | | Object | The String representation of the Object (using standard conversion rules). | | Null | Undefined. | | Undefined | Undefined. | ## rpad(String, Int) Returns the string argument, padded on the right side with the number of spaces specified in the second argument. The `Int` argument must be between 0 and 1000. If the provided value is outside of this valid range, the argument is set to the nearest valid value (0 or 1000). Supported by SQL version 2015-10-08 and later. Examples: `rpad("hello", 2)` = "`hello  `". `rpad(1, 3)` = "`1   `". **** | Argument type 1 | Argument type 2 | Result | | --- | --- | --- | | String | Int | The String is padded on the right side with a number of spaces equal to the provided Int. | | String | Decimal | The Decimal argument is rounded down to the nearest Int and the string is padded on the right side with a number of spaces equal to the provided Int. | | String | String | The second argument is converted to a Decimal, which is rounded down to the nearest Int. The String is padded on the right side with a number of spaces equal to the Int value. | | Other value | Int/Decimal/String | The first value is converted to a String using the standard conversions, and the rpad function is applied on that String. If it cannot be converted, the result is Undefined. | | Any value | Other value | Undefined. | ## round(Decimal) Rounds the given `Decimal` to the nearest `Int`. If the `Decimal` is equidistant from two `Int` values (for example, 0.5), the `Decimal` is rounded up. Supported by SQL version 2015-10-08 and later. Example: `Round(1.2)` = 1. `Round(1.5)` = 2. `Round(1.7)` = 2. `Round(-1.1)` = -1. `Round(-1.5)` = -2. **** | Argument type | Result | | --- | --- | | Int | The argument. | | Decimal | Decimal is rounded down to the nearest Int. | | String | Decimal is rounded down to the nearest Int. If the string cannot be converted to a Decimal, the result is Undefined. | | Other value | Undefined. | ## rtrim(String) Removes all trailing white space (tabs and spaces) from the provided `String`. Supported by SQL version 2015-10-08 and later. Examples: `rtrim(" h i ")` = " h i" **** | Argument type | Result | | --- | --- | | Int | The String representation of the Int. | | Decimal | The String representation of the Decimal. | | Boolean | The String representation of the Boolean ("true" or "false"). | | Array | The String representation of the Array (using standard conversion rules). | | Object | The String representation of the Object (using standard conversion rules). | | Null | Undefined. | | Undefined | Undefined | ## sign(Decimal) Returns the sign of the given number. When the sign of the argument is positive, 1 is returned. When the sign of the argument is negative, -1 is returned. If the argument is 0, 0 is returned. Supported by SQL version 2015-10-08 and later. Examples: `sign(-7)` = -1. `sign(0)` = 0. `sign(13)` = 1. **** | Argument type | Result | | --- | --- | | Int | Int, the sign of the Int value. | | Decimal | Int, the sign of the Decimal value. | | String | Int, the sign of the Decimal value. The string is converted to a Decimal value, and the sign of the Decimal value is returned. If the String cannot be converted to a Decimal, the result is Undefined. Supported by SQL version 2015-10-08 and later. | | Other value | Undefined. | ## sin(Decimal) Returns the sine of a number in radians. `Decimal` arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `sin(0)` = 0.0 **** | Argument type | Result | | --- | --- | | Int | Decimal (with double precision), the sine of the argument. | | Decimal | Decimal (with double precision), the sine of the argument. | | Boolean | Undefined. | | String | Decimal (with double precision), the sine of the argument. If the string cannot be converted to a Decimal, the result is Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## sinh(Decimal) Returns the hyperbolic sine of a number. `Decimal` values are rounded to double precision before function application. The result is a `Decimal` value of double precision. Supported by SQL version 2015-10-08 and later. Example: `sinh(2.3)` = 4.936961805545957 **** | Argument type | Result | | --- | --- | | Int | Decimal (with double precision), the hyperbolic sine of the argument. | | Decimal | Decimal (with double precision), the hyperbolic sine of the argument. | | Boolean | Undefined. | | String | Decimal (with double precision), the hyperbolic sine of the argument. If the string cannot be converted to a Decimal, the result is Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## sourceip() Retrieves the IP address of a device or the router that connects to it. If your device is connected to the internet directly, the function will return the source IP address of the device. If your device is connected to a router that connects to the internet, the function will return the source IP address of the router. Supported by SQL version 2016-03-23. `sourceip()` doesn't take any parameters. **Important** A device's public source IP address is often the IP address of the last Network Address Translation (NAT) Gateway such as your internet service provider's router or cable modem. Examples: `sourceip()="192.158.1.38"` `sourceip()="1.102.103.104"` `sourceip()="2001:db8:ff00::12ab:34cd"` SQL example: `SELECT *, sourceip() as deviceIp FROM 'some/topic'` Examples of how to use the sourceip() function in AWS IoT Core rule actions: **Example 1** The following example shows how to call the () function as a [substitution template](https://docs.aws.amazon.com//iot/latest/developerguide/iot-substitution-templates.html) in a [DynamoDB action](https://docs.aws.amazon.com//iot/latest/developerguide/dynamodb-rule-action.html). ``` { "topicRulePayload": { "sql": "SELECT * AS message FROM 'some/topic'", "ruleDisabled": false, "awsIotSqlVersion": "2016-03-23", "actions": [ { "dynamoDB": { "tableName": "my_ddb_table", "hashKeyField": "key", "hashKeyValue": "${sourceip()}", "rangeKeyField": "timestamp", "rangeKeyValue": "${timestamp()}", "roleArn": "arn:aws:iam::123456789012:role/aws_iot_dynamoDB" } } ] } } ``` **Example 2** The following example shows how to add the sourceip() function as an MQTT user property using [substitution templates](https://docs.aws.amazon.com//iot/latest/developerguide/iot-substitution-templates.html). ``` { "topicRulePayload": { "sql": "SELECT * FROM 'some/topic'", "ruleDisabled": false, "awsIotSqlVersion": "2016-03-23", "actions": [ { "republish": { "topic": "${topic()}/republish", "roleArn": "arn:aws:iam::123456789012:role/aws_iot_republish", "headers": { "payloadFormatIndicator": "UTF8_DATA", "contentType": "rule/contentType", "correlationData": "cnVsZSBjb3JyZWxhdGlvbiBkYXRh", "userProperties": [ { "key": "ruleKey1", "value": "ruleValue1" }, { "key": "sourceip", "value": "${sourceip()}" } ] } } } ] } } ``` You can retrieve the source IP address from messages passing to AWS IoT Core rules from both Message Broker and [Basic Ingest](https://docs.aws.amazon.com//iot/latest/developerguide/iot-basic-ingest.html) pathways. You can also retrieve the source IP for both IPv4 and IPv6 messages. The source IP will be displayed like the following: IPv6: `yyyy:yyyy:yyyy::yyyy:yyyy` IPv4: `xxx.xxx.xxx.xxx` **Note** The original source IP won't be passed though [Republish action](republish-rule-action.md). ## substring(String, Int[, Int]) Expects a `String` followed by one or two `Int` values. For a `String` and a single `Int` argument, this function returns the substring of the provided `String` from the provided `Int` index (0-based, inclusive) to the end of the `String`. For a `String` and two `Int` arguments, this function returns the substring of the provided `String` from the first `Int` index argument (0-based, inclusive) to the second `Int` index argument (0-based, exclusive). Indices that are less than zero are set to zero. Indices that are greater than the `String` length are set to the `String` length. For the three argument version, if the first index is greater than (or equal to) the second index, the result is the empty `String`.  If the arguments provided are not (*String*, *Int*), or (*String*, *Int*, *Int*), the standard conversions are applied to the arguments to attempt to convert them into the correct types. If the types cannot be converted, the result of the function is `Undefined`. Supported by SQL version 2015-10-08 and later. Examples: `substring("012345", 0)` = "012345". `substring("012345", 2)` = "2345". `substring("012345", 2.745)` = "2345". `substring(123, 2)` = "3". `substring("012345", -1)` = "012345". `substring(true, 1.2)` = "rue". `substring(false, -2.411E247)` = "false". `substring("012345", 1, 3)` = "12". `substring("012345", -50, 50)` = "012345". `substring("012345", 3, 1)` = "". ## sql\$1version() Returns the SQL version specified in this rule. Supported by SQL version 2015-10-08 and later. Example: `sql_version()` = "2016-03-23" ## sqrt(Decimal) Returns the square root of a number. `Decimal` arguments are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `sqrt(9)` = 3.0. **** | Argument type | Result | | --- | --- | | Int | The square root of the argument. | | Decimal | The square root of the argument. | | Boolean | Undefined. | | String | The square root of the argument. If the string cannot be converted to a Decimal, the result is Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## startswith(String, String) Returns `Boolean`, whether the first string argument starts with the second string argument. If either argument is `Null` or `Undefined`, the result is `Undefined`. Supported by SQL version 2015-10-08 and later. Example: `startswith("ranger","ran")` = true **** | Argument type 1 | Argument type 2 | Result | | --- | --- | --- | | String | String | Whether the first string starts with the second string. | | Other value | Other value | Both arguments are converted to strings using the standard conversion rules. Returns true if the first string starts with the second string. If either argument is Null or Undefined, the result is Undefined. | ## tan(Decimal) Returns the tangent of a number in radians. `Decimal` values are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `tan(3)` = -0.1425465430742778 **** | Argument type | Result | | --- | --- | | Int | Decimal (with double precision), the tangent of the argument. | | Decimal | Decimal (with double precision), the tangent of the argument. | | Boolean | Undefined. | | String | Decimal (with double precision), the tangent of the argument. If the string cannot be converted to a Decimal, the result is Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## tanh(Decimal) Returns the hyperbolic tangent of a number in radians. `Decimal` values are rounded to double precision before function application. Supported by SQL version 2015-10-08 and later. Example: `tanh(2.3)` = 0.9800963962661914 **** | Argument type | Result | | --- | --- | | Int | Decimal (with double precision), the hyperbolic tangent of the argument. | | Decimal | Decimal (with double precision), the hyperbolic tangent of the argument. | | Boolean | Undefined. | | String | Decimal (with double precision), the hyperbolic tangent of the argument. If the string cannot be converted to a Decimal, the result is Undefined. | | Array | Undefined. | | Object | Undefined. | | Null | Undefined. | | Undefined | Undefined. | ## time\$1to\$1epoch(String, String) Use the `time_to_epoch` function to convert a timestamp string into a number of milliseconds in Unix epoch time. Supported by SQL version 2016-03-23 and later. To convert milliseconds to a formatted timestamp string, see [parse\$1time(String, Long[, String])](#iot-sql-function-parse-time). The `time_to_epoch` function expects the following arguments: timestamp (String) The timestamp string to be converted to milliseconds since Unix epoch. If the timestamp string doesn't specify a timezone, the function uses the UTC timezone. pattern (String) A date/time pattern that follows [JDK11 Time Formats](http://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/time/format/DateTimeFormatter.html). Examples: `time_to_epoch("2020-04-03 09:45:18 UTC+01:00", "yyyy-MM-dd HH:mm:ss VV")` = 1585903518000 `time_to_epoch("18 December 2015", "dd MMMM yyyy")` = 1450396800000 `time_to_epoch("2007-12-03 10:15:30.592 America/Los_Angeles", "yyyy-MM-dd HH:mm:ss.SSS z")` = 1196705730592 ## timestamp() Returns the current timestamp in milliseconds from 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, as observed by the AWS IoT rules engine. Supported by SQL version 2015-10-08 and later. Example: `timestamp()` = `1481825251155` ## topic(Decimal) Returns the topic to which the message that triggered the rule was sent. If no parameter is specified, the entire topic is returned. The `Decimal` parameter is used to specify a specific topic segment, with 1 designating the first segment. For the topic `foo/bar/baz`, topic(1) returns `foo`, topic(2) returns `bar`, and so on. Supported by SQL version 2015-10-08 and later. Examples: `topic()` = "things/myThings/thingOne" `topic(1)` = "things" When [Basic Ingest](iot-basic-ingest.md) is used, the initial prefix of the topic (`$aws/rules/rule-name`) is not available to the topic() function. For example, given the topic: `$aws/rules/BuildingManager/Buildings/Building5/Floor2/Room201/Lights` `topic()` = "Buildings/Building5/Floor2/Room201/Lights" `topic(3)` = "Floor2" ## traceid() Returns the trace ID (UUID) of the MQTT message, or `Undefined` if the message wasn't sent over MQTT. Supported by SQL version 2015-10-08 and later. Example: `traceid() ` = "12345678-1234-1234-1234-123456789012" ## transform(String, Object, Array) Returns an array of objects that contains the result of the specified transformation of the `Object` parameter on the `Array` parameter. Supported by SQL version 2016-03-23 and later. String The transformation mode to use. Refer to the following table for the supported transformation modes and how they create the `Result` from the `Object` and `Array` parameters. Object An object that contains the attributes to apply to each element of the `Array`. Array An array of objects into which the attributes of `Object` are applied. Each object in this Array corresponds to an object in the function's response. Each object in the function's response contains the attributes present in the original object and the attributes provided by `Object` as determined by the transformation mode specified in `String`. | `String` parameter | `Object` parameter | `Array` parameter | Result | | --- | --- | --- | --- | | `enrichArray` | Object | Array of objects | An Array of objects in which each object contains the attributes of an element from the `Array` parameter and the attributes of the `Object` parameter. | | Any other value | Any value | Any value | Undefined | **Note** The array returned by this function is limited to 128 KiB. ### Transform function example 1 This example shows how the **transform()** function produces a single array of objects from a data object and an array. In this example, the following message is published to the MQTT topic `A/B`. ``` { "attributes": { "data1": 1, "data2": 2 }, "values": [ { "a": 3 }, { "b": 4 }, { "c": 5 } ] } ``` This SQL statement for a topic rule action uses the **transform()** function with a `String` value of `enrichArray`. In this example, `Object` is the `attributes` property from the message payload and `Array` is the `values` array, which contains three objects. ``` select value transform("enrichArray", attributes, values) from 'A/B' ``` Upon receiving the message payload, the SQL statement evaluates to the following response. ``` [ { "a": 3, "data1": 1, "data2": 2 }, { "b": 4, "data1": 1, "data2": 2 }, { "c": 5, "data1": 1, "data2": 2 } ] ``` ### Transform function example 2 This example shows how the **transform()** function can use literal values to include and rename individual attributes from the message payload. In this example, the following message is published to the MQTT topic `A/B`. This is the same message that was used in [Transform function example 1](#iot-func-transform-example1). ``` { "attributes": { "data1": 1, "data2": 2 }, "values": [ { "a": 3 }, { "b": 4 }, { "c": 5 } ] } ``` This SQL statement for a topic rule action uses the **transform()** function with a `String` value of `enrichArray`. The `Object` in the **transform()** function has a single attribute named `key` with the value of `attributes.data1` in the message payload and `Array` is the `values` array, which contains the same three objects used in the previous example. ``` select value transform("enrichArray", {"key": attributes.data1}, values) from 'A/B' ``` Upon receiving the message payload, this SQL statement evaluates to the following response. Notice how the `data1` property is named `key` in the response. ``` [ { "a": 3, "key": 1 }, { "b": 4, "key": 1 }, { "c": 5, "key": 1 } ] ``` ### Transform function example 3 This example shows how the **transform()** function can be used in nested SELECT clauses to select multiple attributes and create new objects for subsequent processing. In this example, the following message is published to the MQTT topic `A/B`. ``` { "data1": "example", "data2": { "a": "first attribute", "b": "second attribute", "c": [ { "x": { "someInt": 5, "someString": "hello" }, "y": true }, { "x": { "someInt": 10, "someString": "world" }, "y": false } ] } } ``` The `Object` for this transform function is the object returned by the SELECT statement, which contains the `a` and `b` elements of the message's `data2` object. The `Array` parameter consists of the two objects from the `data2.c` array in the original message. ``` select value transform('enrichArray', (select a, b from data2), (select value c from data2)) from 'A/B' ``` With the preceding message, the SQL statement evaluates to the following response. ``` [ { "x": { "someInt": 5, "someString": "hello" }, "y": true, "a": "first attribute", "b": "second attribute" }, { "x": { "someInt": 10, "someString": "world" }, "y": false, "a": "first attribute", "b": "second attribute" } ] ``` The array returned in this response could be used with topic rule actions that support `batchMode`. ## trim(String) Removes all leading and trailing white space from the provided `String`. Supported by SQL version 2015-10-08 and later. Example: `Trim(" hi ") ` = "hi" **** | Argument type | Result | | --- | --- | | Int | The String representation of the Int with all leading and trailing white space removed. | | Decimal | The String representation of the Decimal with all leading and trailing white space removed. | | Boolean | The String representation of the Boolean ("true" or "false") with all leading and trailing white space removed. | | String | The String with all leading and trailing white space removed. | | Array | The String representation of the Array using standard conversion rules. | | Object | The String representation of the Object using standard conversion rules. | | Null | Undefined. | | Undefined | Undefined. | ## trunc(Decimal, Int) Truncates the first argument to the number of `Decimal` places specified by the second argument. If the second argument is less than zero, it is set to zero. If the second argument is greater than 34, it is set to 34. Trailing zeroes are stripped from the result. Supported by SQL version 2015-10-08 and later. Examples: `trunc(2.3, 0)` = 2. `trunc(2.3123, 2)` = 2.31. `trunc(2.888, 2)` = 2.88. `trunc(2.00, 5)` = 2. **** | Argument type 1 | Argument type 2 | Result | | --- | --- | --- | | Int | Int | The source value. | | Int/Decimal | Int/Decimal | The first argument is truncated to the length described by the second argument. The second argument, if not an Int, is rounded down to the nearest Int. | | Int/Decimal/String | Int/Decimal | The first argument is truncated to the length described by the second argument. The second argument, if not an Int, is rounded down to the nearest Int. A String is converted to a Decimal value. If the string conversion fails, the result is Undefined. | | Other value | | Undefined. | ## upper(String) Returns the uppercase version of the given `String`. Non-`String` arguments are converted to `String` using the standard conversion rules. Supported by SQL version 2015-10-08 and later. Examples: `upper("hello")` = "HELLO" `upper(["hello"])` = "[\$1"HELLO\$1"]" # Literals You can directly specify literal objects in the SELECT and WHERE clauses of your rule SQL, which can be useful for passing information. **Note** Literals are available only when using SQL version 2016-03-23 or later. JSON object syntax is used (key-value pairs, comma-separated, where keys are strings and values are JSON values, wrapped in curly brackets \$1\$1). For example: Incoming payload published on topic `topic/subtopic`: `{"lat_long": [47.606,-122.332]}` SQL statement: `SELECT {'latitude': get(lat_long, 0),'longitude':get(lat_long, 1)} as lat_long FROM 'topic/subtopic'` The resulting outgoing payload would be: `{"lat_long":{"latitude":47.606,"longitude":-122.332}}`. You can also directly specify arrays in the SELECT and WHERE clauses of your rule SQL, which allows you to group information. JSON syntax is used (wrap comma-separated items in square brackets [] to create an array literal). For example: Incoming payload published on topic `topic/subtopic`: `{"lat": 47.696, "long": -122.332}` SQL statement: `SELECT [lat,long] as lat_long FROM 'topic/subtopic'` The resulting output payload would be: `{"lat_long": [47.606,-122.332]}`. # Case statements Case statements can be used for branching execution, like a switch statement. Syntax: ``` CASE v WHEN t[1] THEN r[1] WHEN t[2] THEN r[2] ... WHEN t[n] THEN r[n] ELSE r[e] END ``` The expression *`v`* is evaluated and matched for equality against the *`t[i]`* value of each `WHEN` clause. If a match is found, the corresponding *`r[i]`* expression becomes the result of the `CASE` statement. The `WHEN` clauses are evaluated in order so that if there's more than one matching clause, the result of the first matching clause becomes the result of the `CASE` statement. If there are no matches, *`r[e]`* of the `ELSE` clause is the result. If there's no match and no `ELSE` clause, the result is `Undefined`. `CASE` statements require at least one `WHEN` clause. An `ELSE` clause is optional. For example: Incoming payload published on topic `topic/subtopic`: ``` { "color":"yellow" } ``` SQL statement: ``` SELECT CASE color WHEN 'green' THEN 'go' WHEN 'yellow' THEN 'caution' WHEN 'red' THEN 'stop' ELSE 'you are not at a stop light' END as instructions FROM 'topic/subtopic' ``` The resulting output payload would be: ``` { "instructions":"caution" } ``` **Note** If *`v`* is `Undefined`, the result of the case statement is `Undefined`. # JSON extensions You can use the following extensions to ANSI SQL syntax to facilitate work with nested JSON objects. "." Operator This operator accesses members in embedded JSON objects and functions identically to ANSI SQL and JavaScript. For example: ``` SELECT foo.bar AS bar.baz FROM 'topic/subtopic' ``` selects the value of the `bar` property in the `foo` object from the following message payload sent to the `topic/subtopic` topic. ``` { "foo": { "bar": "RED", "bar1": "GREEN", "bar2": "BLUE" } } ``` If a JSON property name includes a hyphen character or numeric characters, the 'dot' notation will not work. Instead, you must use the [get function](iot-sql-functions.md#iot-sql-function-get) to extract the property's value. In this example, the following message is sent to the `iot/rules` topic. ``` { "mydata": { "item2": { "0": { "my-key": "myValue" } } } } ``` Normally, the value of `my-key` would be identified as in this query. ``` SELECT * from iot/rules WHERE mydata.item2.0.my-key= "myValue" ``` However, because the property name `my-key` contains a hyphen and `item2` contains a numeric character, the [get function](iot-sql-functions.md#iot-sql-function-get) must be used as the following query shows. ``` SELECT * from 'iot/rules' WHERE get(get(get(mydata,"item2"),"0"),"my-key") = "myValue" ``` `*` Operator This functions in the same way as the `*` wildcard in ANSI SQL. It's used in the SELECT clause only and creates a new JSON object containing the message data. If the message payload is not in JSON format, `*` returns the entire message payload as raw bytes. For example: ``` SELECT * FROM 'topic/subtopic' ``` **Applying a Function to an Attribute Value** The following is an example JSON payload that might be published by a device: ``` { "deviceid" : "iot123", "temp" : 54.98, "humidity" : 32.43, "coords" : { "latitude" : 47.615694, "longitude" : -122.3359976 } } ``` The following example applies a function to an attribute value in a JSON payload: ``` SELECT temp, md5(deviceid) AS hashed_id FROM topic/# ``` The result of this query is the following JSON object: ``` { "temp": 54.98, "hashed_id": "e37f81fb397e595c4aeb5645b8cbbbd1" } ``` # Substitution templates You can use a substitution template to augment the JSON data returned when a rule is triggered and AWS IoT performs an action. The syntax for a substitution template is `${`*expression*`}`, where *expression* can be any expression supported by AWS IoT in SELECT clauses, WHERE clauses, and [AWS IoT rule actions](iot-rule-actions.md). This expression can be plugged into an action field on a rule, allowing you to dynamically configure an action. In effect, this feature substitutes a piece of information in an action. This includes functions, operators, and information present in the original message payload. **Important** Because an expression in a substitution template is evaluated separately from the "SELECT ..." statement, you can't reference an alias created using the AS clause. You can only reference information present in the original payload, [functions](iot-sql-functions.md), and [operators](iot-sql-operators.md). For more information about supported expressions, see [AWS IoT SQL reference](iot-sql-reference.md). The following rule actions support substitution templates. Each action supports different fields that can be substituted. + [Apache Kafka](apache-kafka-rule-action.md) + [CloudWatch alarms](cloudwatch-alarms-rule-action.md) + [CloudWatch Logs](cloudwatch-logs-rule-action.md) + [CloudWatch metrics](cloudwatch-metrics-rule-action.md) + [DynamoDB](dynamodb-rule-action.md) + [DynamoDBv2](dynamodb-v2-rule-action.md) + [Elasticsearch](elasticsearch-rule-action.md) + [HTTP](https-rule-action.md) + [AWS IoT Events](iotevents-rule-action.md) + [AWS IoT SiteWise](iotsitewise-rule-action.md) + [Kinesis Data Streams](kinesis-rule-action.md) + [Firehose](kinesis-firehose-rule-action.md) + [Lambda](lambda-rule-action.md) + [Location](location-rule-action.md) + [OpenSearch](opensearch-rule-action.md) + [Republish](republish-rule-action.md) + [S3](s3-rule-action.md) + [SNS](sns-rule-action.md) + [SQS](sqs-rule-action.md) + [Step Functions](stepfunctions-rule-action.md) + [Timestream](timestream-rule-action.md) Substitution templates appear in the action parameters within a rule: ``` { "sql": "SELECT *, timestamp() AS timestamp FROM 'my/iot/topic'", "ruleDisabled": false, "actions": [{ "republish": { "topic": "${topic()}/republish", "roleArn": "arn:aws:iam::123456789012:role/my-iot-role" } }] } ``` If this rule is triggered by the following JSON published to `my/iot/topic`: ``` { "deviceid": "iot123", "temp": 54.98, "humidity": 32.43, "coords": { "latitude": 47.615694, "longitude": -122.3359976 } } ``` Then this rule publishes the following JSON to `my/iot/topic/republish`, which AWS IoT substitutes from `${topic()}/republish`: ``` { "deviceid": "iot123", "temp": 54.98, "humidity": 32.43, "coords": { "latitude": 47.615694, "longitude": -122.3359976 }, "timestamp": 1579637878451 } ``` # Nested object queries You can use nested SELECT clauses to query for attributes within arrays and inner JSON objects. Supported by SQL version 2016-03-23 and later. Consider the following MQTT message: ``` { "e": [ { "n": "temperature", "u": "Cel", "t": 1234, "v": 22.5 }, { "n": "light", "u": "lm", "t": 1235, "v": 135 }, { "n": "acidity", "u": "pH", "t": 1235, "v": 7 } ] } ``` **Example** You can convert values to a new array with the following rule. ``` SELECT (SELECT VALUE n FROM e) as sensors FROM 'my/topic' ``` The rule generates the following output. ``` { "sensors": [ "temperature", "light", "acidity" ] } ``` **Example** Using the same MQTT message, you can also query a specific value within a nested object with the following rule. ``` SELECT (SELECT v FROM e WHERE n = 'temperature') as temperature FROM 'my/topic' ``` The rule generates the following output. ``` { "temperature": [ { "v": 22.5 } ] } ``` **Example** You can also flatten the output with a more complicated rule. ``` SELECT get((SELECT v FROM e WHERE n = 'temperature'), 0).v as temperature FROM 'topic' ``` The rule generates the following output. ``` { "temperature": 22.5 } ``` # Working with binary payloads To handle your message payload as raw binary data (rather than a JSON object), you can use the \$1 operator to refer to it in a SELECT clause. **Topics** + [ ## Binary payload examples ](#binary-payloads-examples) + [ ## Decoding protobuf message payloads ](#binary-payloads-protobuf) ## Binary payload examples When you use \$1 to refer to the message payload as raw binary data, you can add data to the rule. If you have an empty or a JSON payload, the resulting payload can have data added using the rule. The following shows examples of supported `SELECT` clauses. + You can use the following `SELECT` clauses with only a \$1 for binary payloads. + ``` SELECT * FROM 'topic/subtopic' ``` + ``` SELECT * FROM 'topic/subtopic' WHERE timestamp() % 12 = 0 ``` + You can also add data and use the following `SELECT` clauses. + ``` SELECT *, principal() as principal, timestamp() as time FROM 'topic/subtopic' ``` + ``` SELECT encode(*, 'base64') AS data, timestamp() AS ts FROM 'topic/subtopic' ``` + You can also use these `SELECT` clauses with binary payloads. + The following refers to `device_type` in the WHERE clause. ``` SELECT * FROM 'topic/subtopic' WHERE device_type = 'thermostat' ``` + The following is also supported. ``` { "sql": "SELECT * FROM 'topic/subtopic'", "actions": [ { "republish": { "topic": "device/${device_id}" } } ] } ``` The following rule actions don't support binary payloads so you must decode them. + Some rule actions don't support binary payload input, such as a [Lambda action](https://docs.aws.amazon.com/iot/latest/developerguide/iot-rule-actions.html#lambda-rule), so you must decode binary payloads. The Lambda rule action can receive binary data, if it's base64 encoded and in a JSON payload. You can do this by changing the rule to the following. ``` SELECT encode(*, 'base64') AS data FROM 'my_topic' ``` + The SQL statement doesn't support string as input. To convert a string input to JSON, you can run the following command. ``` SELECT decode(encode(*, 'base64'), 'base64') AS payload FROM 'topic' ``` ## Decoding protobuf message payloads [Protocol Buffers (protobuf)](https://developers.google.com/protocol-buffers) is an open-source data format used to serialize structured data in a compact, binary form. It's used for transmitting data over networks or storing it in files. Protobuf allows you to send data in small packet sizes and at a faster rate than other messaging formats. AWS IoT Core Rules support protobuf by providing the [decode(value, decodingScheme)](iot-sql-functions.md#iot-sql-decode-base64) SQL function, which allows you to decode protobuf-encoded message payloads to JSON format and route them to downstream services. This section details the step-by-step process to configure protobuf decoding in AWS IoT Core Rules. **Topics** + [ ### Prerequisites ](#binary-payloads-protobuf-prerequisites) + [ ### Create descriptor files ](#binary-payloads-protobuf-descriptor-steps) + [ ### Upload descriptor files to S3 bucket ](#binary-payloads-protobuf-s3-steps) + [ ### Configure protobuf decoding in Rules ](#binary-payloads-protobuf-steps) + [ ### Limitations ](#binary-payloads-protobuf-limitations) + [ ### Best practices ](#binary-payloads-protobuf-bestpractices) ### Prerequisites + A basic understanding of [Protocol Buffers (protobuf)](https://developers.google.com/protocol-buffers) + The [`.proto` files](https://developers.google.com/protocol-buffers/docs/proto3) that define message types and related dependencies + Installing [Protobuf Compiler (protoc)](https://github.com/protocolbuffers/protobuf/releases) on your system ### Create descriptor files If you already have your descriptor files, you can skip this step. A descriptor file (`.desc`) is a compiled version of a `.proto` file, which is a text file that defines the data structures and message types to be used in a protobuf serialization. To generate a descriptor file, you must define a `.proto` file and use the [protoc](https://github.com/protocolbuffers/protobuf/releases) compiler to compile it. 1. Create `.proto` files that define the message types. An example `.proto` file can look like the following: ``` syntax = "proto3"; message Person { optional string name = 1; optional int32 id = 2; optional string email = 3; } ``` In this example `.proto` file, you use proto3 syntax and define message type `Person`. The `Person` message definition specifies three fields (name, id, and email). For more information about `.proto` file message formats, see [Language Guide (proto3)](https://developers.google.com/protocol-buffers/docs/proto3). 1. Use the [protoc](https://github.com/protocolbuffers/protobuf/releases) compiler to compile the `.proto` files and generate a descriptor file. An example command to create a descriptor (`.desc`) file can be the following: ``` protoc --descriptor_set_out=.desc \ --proto_path= \ --include_imports \ .proto ``` This example command generates a descriptor file `.desc`, which AWS IoT Core Rules can use to decode protobuf payloads that conform to the data structure defined in `.proto`. + `--descriptor_set_out` Specifies the name of the descriptor file (`.desc` ) that should be generated. + `--proto_path` Specifies the locations of any imported `.proto` files that are referenced by the file being compiled. You can specify the flag multiple times if you have multiple imported `.proto` files with different locations. + `--include_imports` Specifies that any imported `.proto` files should also be compiled and included in the `.desc` descriptor file. + `.proto` Specifies the name of the `.proto` file that you want to compile. For more information about the protoc reference, see [API Reference](https://developers.google.com/protocol-buffers/docs/reference/overview). ### Upload descriptor files to S3 bucket After you create your descriptor files `.desc`, upload the descriptor files `.desc` to an Amazon S3 bucket, using the AWS API, AWS SDK, or the AWS Management Console. **Important considerations** + Make sure that you upload the descriptor files to an Amazon S3 bucket in your AWS account in the same AWS Region where you intend to configure your Rules. + Make sure that you grant AWS IoT Core access to read the `FileDescriptorSet` from S3. If your S3 bucket has server-side encryption (SSE) disabled or if your S3 bucket is encrypted using Amazon S3-managed keys (SSE-S3), no additional policy configurations are required. This can be accomplished with the example bucket policy: **** ``` { "Version":"2012-10-17", "Statement": [ { "Sid": "Statement1", "Effect": "Allow", "Principal": { "Service": "iot.amazonaws.com" }, "Action": "s3:Get*", "Resource": "arn:aws:s3:::/.desc" } ] } ``` + If your S3 bucket is encrypted using an AWS Key Management Service key (SSE-KMS), make sure that you grant AWS IoT Core permission to use the key when accessing your S3 bucket. You can do this by adding this statement to your key policy: ``` { "Sid": "Statement1", "Effect": "Allow", "Principal": { "Service": "iot.amazonaws.com" }, "Action": [ "kms:Decrypt", "kms:GenerateDataKey*", "kms:DescribeKey" ], "Resource": "arn:aws:kms:us-west-2:111122223333:key/1234abcd-12ab-34cd-56ef-1234567890ab" } ``` ### Configure protobuf decoding in Rules After you upload the descriptor files to your Amazon S3 bucket, configure a [Rule](https://docs.aws.amazon.com//iot/latest/developerguide/iot-create-rule.html) that can decode your protobuf message payload format using the [decode(value, decodingScheme)](iot-sql-functions.md#iot-sql-decode-base64) SQL function. A detailed function signature and example can be found in the [decode(value, decodingScheme)](iot-sql-functions.md#iot-sql-decode-base64) SQL function of the *AWS IoT SQL reference*. The following is an example SQL expression using the [decode(value, decodingScheme)](iot-sql-functions.md#iot-sql-decode-base64) function: ``` SELECT VALUE decode(*, 'proto', '', '.desc', '', '') FROM '' ``` In this example expression: + You use the [decode(value, decodingScheme)](iot-sql-functions.md#iot-sql-decode-base64) SQL function to decode the binary message payload referenced by `*`. This can be a binary protobuf-encoded payload or a JSON string that represents a base64-encoded protobuf payload. + The message payload provided is encoded using the `Person` message type defined in `PROTO_FILENAME.proto`. + The Amazon S3 bucket named `BUCKET NAME` contains the `FILENAME.desc` generated from `PROTO_FILENAME.proto`. After you complete the configuration, publish a message to AWS IoT Core on the topic to which the Rule is subscribed. ### Limitations AWS IoT Core Rules support protobuf with the following limitations: + Decoding protobuf message payloads within [substitution templates](https://docs.aws.amazon.com//iot/latest/developerguide/iot-substitution-templates.html) is not supported. + When decoding protobuf message payloads, you can use the [decode SQL function](iot-sql-functions.md#iot-sql-decode-base64) within a single SQL expression up to two times. + The maximum inbound payload size is 128 KiB (1KiB =1024 bytes), the maximum outbound payload size is 128 KiB, and the maximum size for a `FileDescriptorSet` object stored in an Amazon S3 bucket is 32 KiB. + Amazon S3 buckets encrypted with SSE-C encryption are not supported. ### Best practices Here are some best practices and troubleshooting tips. + Back up your proto files in the Amazon S3 bucket. It's a good practice to back up your proto files in case something goes wrong. For example, if you incorrectly modify the proto files without backups when running protoc, this can cause issues in your production stack. There are multiple ways to back up your files in an Amazon S3 bucket. For example, you can [use versioning in S3 buckets](https://docs.aws.amazon.com//AmazonS3/latest/userguide/Versioning.html). For more information about how to back up files in Amazon S3 buckets, refer to the *[Amazon S3 Developer Guide](https://docs.aws.amazon.com//aws-backup/latest/devguide/recovery-points.html)*. + Configure AWS IoT logging to view log entries. It's a good practice to configure AWS IoT logging so that you can check AWS IoT logs for your account in CloudWatch. When a rule's SQL query calls an external function, AWS IoT Core Rules generates a log entry with an `eventType` of `FunctionExecution`, which contains the reason field that will help you troubleshoot failures. Possible errors include an Amazon S3 object not found, or invalid protobuf file descriptor. For more information about how to configure AWS IoT logging and see the log entries, see [Configure AWS IoT logging](https://docs.aws.amazon.com//iot/latest/developerguide/configure-logging.html) and [Rules engine log entries](https://docs.aws.amazon.com//iot/latest/developerguide/cwl-format.html#log-rules-fn-exec). + Update `FileDescriptorSet` using a new object key and update the object key in your Rule. You can update `FileDescriptorSet` by uploading an updated descriptor file to your Amazon S3 bucket. Your updates to `FileDescriptorSet` can take up to 15 minutes to be reflected. To avoid this delay, it's a good practice to upload your updated `FileDescriptorSet` using a new object key, and update the object key in your Rule. # SQL versions The AWS IoT rules engine uses an SQL-like syntax to select data from MQTT messages. The SQL statements are interpreted based on an SQL version specified with the `awsIotSqlVersion` property in a JSON document that describes the rule. For more information about the structure of JSON rule documents, see [Creating a Rule](iot-create-rule.md). The `awsIotSqlVersion` property lets you specify which version of the AWS IoT SQL rules engine that you want to use. When a new version is deployed, you can continue to use an earlier version or change your rule to use the new version. Your current rules continue to use the version with which they were created. The following JSON example shows you how to specify the SQL version using the `awsIotSqlVersion` property. ``` { "sql": "expression", "ruleDisabled": false, "awsIotSqlVersion": "2016-03-23", "actions": [{ "republish": { "topic": "my-mqtt-topic", "roleArn": "arn:aws:iam::123456789012:role/my-iot-role" } }] } ``` AWS IoT currently supports the following SQL versions: + `2016-03-23` – The SQL version built on 2016-03-23 (recommended). + `2015-10-08` – The original SQL version built on 2015-10-08. + `beta` – The most recent beta SQL version. This version could introduce breaking changes to your rules. ## What's new in the 2016-03-23 SQL rules engine version + Fixes for selecting nested JSON objects. + Fixes for array queries. + Intra-object query support. For more information, see [Nested object queries](iot-sql-nested-queries.md). + Support to output an array as a top-level object. + Addition of the `encode(value, encodingScheme)` function, which can be applied on JSON and non-JSON format data. For more information, see the [encode function](iot-sql-functions.md#iot-sql-encode-payload). ### Output an `Array` as a top-level object This feature allows a rule to return an array as a top-level object. For example, given the following MQTT message: ``` { "a": {"b":"c"}, "arr":[1,2,3,4] } ``` And the following rule: ``` SELECT VALUE arr FROM 'topic' ``` The rule generates the following output. ``` [1,2,3,4] ```