# 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"]"