Functions - Amazon IoT Core
abs(Decimal)accountid()acos(Decimal)asin(Decimal)atan(Decimal)atan2(Decimal, Decimal)aws_lambda(functionArn, inputJson)bitand(Int, Int)bitor(Int, Int)bitxor(Int, Int)bitnot(Int)cast()ceil(Decimal)chr(String)clientid()concat()cos(Decimal)cosh(Decimal)decode(value, decodingScheme)encode(value, encodingScheme)endswith(String, String)exp(Decimal)floor(Decimal)getget_dynamodb(tableName, partitionKeyName, partitionKeyValue, sortKeyName, sortKeyValue, roleArn)get_mqtt_property(name)get_secret(secretId, secretType, key, roleArn)get_thing_shadow(thingName, shadowName, roleARN)get_user_properties(userPropertyKey)Hashing functionsindexof(String, String)isNull()isUndefined()length(String)ln(Decimal)log(Decimal)lower(String)lpad(String, Int)ltrim(String)machinelearning_predict(modelId, roleArn, record)mod(Decimal, Decimal)nanvl(AnyValue, AnyValue)newuuid()numbytes(String)parse_time(String, Long[, String])power(Decimal, Decimal)principal()rand()regexp_matches(String, String)regexp_replace(String, String, String)regexp_substr(String, String)remainder(Decimal, Decimal)replace(String, String, String)rpad(String, Int)round(Decimal)rtrim(String)sign(Decimal)sin(Decimal)sinh(Decimal)sourceip()substring(String, Int[, Int])sql_version()sqrt(Decimal)startswith(String, String)tan(Decimal)tanh(Decimal)time_to_epoch(String, String)timestamp()topic(Decimal)traceid()transform(String, Object, Array)trim(String)trunc(Decimal, Int)upper(String)
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Functions

You can use the following built-in functions in the SELECT or WHERE clauses of your SQL expressions.

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_lambda(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 Amazon IoT lambda:InvokeFunction permissions to invoke the specified Lambda function. The following example shows how to grant the lambda:InvokeFunction permission using the Amazon 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 Amazon Web Services Region of your account.

--principal

The principal who is getting the permission. This should be iot.amazonaws.com to allow Amazon IoT permission to call a Lambda function.

--source-arn

The ARN of the rule. You can use the get-topic-rule Amazon CLI command to get the ARN of a rule.

--source-account

The Amazon Web Services 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 Amazon IoT to invoke a Lambda function, specify lambda:InvokeFunction.

Important

If you add a permission for an Amazon IoT principal without providing the source-arn or source-account, any Amazon Web Services account that creates a rule with your Lambda action can trigger rules to invoke your Lambda function from Amazon IoT. For more information, see Lambda Permission Model.

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 '*', 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 Amazon 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.

Note

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. Amazon IoT attempts to parse strings matching the regex: ^-?\d+(\.\d+)?((?i)E-?\d+)?$. "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. Amazon IoT attempts to parse strings matching the regex: ^-?\d+(\.\d+)?((?i)E-?\d+)?$. "0", "-1.2", "5E-12" are all examples of strings that are converted automatically to decimals. Amazon 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_nonzero_value = True.
Decimal 0 = False, any_nonzero_value = 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 "true"=True and "false"=False (case-insensitive). Other string values = Undefined.
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+

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 Amazon IoT SQL reference, 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.

The function signature looks like the following:

decode(<ENCODED DATA>, 'proto', '<S3 BUCKET NAME>', '<S3 OBJECT KEY>', '<PROTO NAME>', '<MESSAGE TYPE>')
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 Amazon IoT SQL reference. You can specify * 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_dynamodb(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.

partitionKeyValue

The value of the partition key used to identify a record. For more information, see DynamoDB Keys.

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.

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.

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:aws-region:account-id: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 Amazon 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.

  • If get_dynamodb() returns more than 8 KB of data, the rule's action may not be invoked.

get_mqtt_property(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_mqtt_property("format_indicator") String (UNSPECIFIED_BYTES or UTF8_DATA) String (UNSPECIFIED_BYTES)
get_mqtt_property("content_type") String Undefined
get_mqtt_property("response_topic") String Undefined
get_mqtt_property("correlation_data") base64 encoded String Undefined
get_mqtt_property("some_invalid_name") 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_secret(secretId, secretType, key, roleArn)

Retrieves the value of the encrypted SecretString or SecretBinary field of the current version of a secret in Amazon Secrets Manager. For more information about creating and maintaining secrets, see CreateSecret, UpdateSecret, and PutSecretValue.

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 | SecretBinary.

SecretString

  • For secrets that you create as JSON objects by using the APIs, the Amazon CLI, or the Amazon 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 Amazon 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 Amazon 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 Amazon Secrets Manager, this function might return the previous version.

This function is not metered, but Amazon Secrets Manager charges apply. Because of the secret caching mechanism, the rules engine occasionally calls Amazon 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.

get_thing_shadow(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_user_properties(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_user_properties('some key') Array of String ['some value', 'value with duplicate key']
get_user_properties('other key') Array of String ['a different value']
get_user_properties( ) Array of key-value pair Objects [{'"some key": "some value"'}, {"other key": "a different value"}, {"some key": "value with duplicate key"}]
get_user_properties('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

Amazon 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 2015-10-08 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"]) = "[\"hello\"]".

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_predict(modelId, roleArn, record)

Use the machinelearning_predict function to make predictions using the data from an MQTT message based on an Amazon SageMaker 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 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 to make predictions. The value must be SGD.

predictedScores

Contains the raw classification score corresponding to each label.

predictedValue

The value predicted by SageMaker.

mod(Decimal, Decimal)

Returns the remainder of the division of the first argument by the second argument. Equivalent to remainder(Decimal, Decimal). 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_time(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_to_epoch(String, String).

The parse_time function expects the following arguments:

pattern

(String) A date/time pattern that follows Joda-Time formats.

timestamp

(Long) The time to be formatted in milliseconds since Unix epoch. See function timestamp().

timezone

(String) The time zone of the formatted date/time. The default is "UTC". The function supports Joda-Time time zones. 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
Amazon IoT console MQTT client MQTT IAM user or role iam-role-id:session-name
Amazon CLI HTTP IAM user or role userid
Amazon IoT Device SDK MQTT X.509 device certificate X.509 certificate thumbprint
Amazon 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_matches(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_replace(String, String, String)

Replaces all occurrences of the second argument (regular expression) in the first argument with the third argument. Reference capture groups with "$". 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_substr(String, String)

Finds the first match of the second parameter (regex) in the first parameter. Reference capture groups with "$". 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). 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 Amazon IoT Core rule actions:

Example 1

The following example shows how to call the () function as a substitution template in a DynamoDB action.

{
	"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.

{
	"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 Amazon IoT Core rules from both Message Broker and Basic Ingest 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.

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_version()

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_to_epoch(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_time(String, Long[, String]).

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.

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 Amazon 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 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.

{
    "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"]) = "[\"HELLO\"]"