Menu
AWS IoT
Developer Guide

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

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:

Copy
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 whose resource policy you are updating by adding a new permission.

--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 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 you want to allow in this statement. In this case, we want to allow AWS IoT to invoke a Lambda function, so we specify lambda:InvokeFunction.

Note

If you add a permission for a AWS IoT principal without providing the source ARN, 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.

The following rule shows how to call the aws_lambda function:

Copy
SELECT aws_lambda("arn:aws:lambda:us-east-1:account_id:function:lambda_function", payload.inner.element).some.value as output FROM 'a/b'

payload.inner.element selects data from message published on topic 'a/b'.

some.value selects data from the output that is generated by the Lambda function.

Note

Rules Engine limits the execution duration of Lambda Functions. Lambda function calls from rules should be completed within 2000ms.

bitand(Int, Int)

Performs a bitwise AND on the bit representations of the two Int(-converted) arguments. Supported by SQL version 2015-10-8 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-8 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-8 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-8 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/from Booleans. If we cannot determine how to cast one type to another, the result is Undefined. Supported by SQL version 2015-10-8 and later. Format: cast(value as type).

Example:

cast(true as Decimal) = 1.0

The following keywords may appear after "as" when calling cast:

Keyword Result
Decimal Casts value to Decimal.
Bool Casts value to Boolean.
Boolean Casts value to Boolean.
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.
Int Casts value to Int.

Casting rules:

Cast to Decimal

Argument Type Result
Int A Decimal with no decimal point.
Decimal The source value.
Boolean true = 1.0, false = 0.0.
String Will try to parse the string as a Decimal. We will attempt to parse strings matching the regex: ^-?\d+(\.\d+)?((?i)E-?\d+)?$. "0", "-1.2", "5E-12" are all examples of Strings that would be 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 Will try to parse the string as a Decimal. We will attempt to parse strings matching the regex: ^-?\d+(\.\d+)?((?i)E-?\d+)?$. "0", "-1.2", "5E-12" are all examples of Strings that would be converted automatically to Decimals. Will attempt 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 will be a comma-separated list enclosed in square brackets. Strings are quoted. Decimals, Ints, Booleans are not.
Object The object serialized to JSON. The JSON string will be a comma-separated list of key-value pairs and will begin and end with curly braces. Strings are quoted. Decimals, Ints, Booleans 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-8 and later.

Examples:

ceil(1.2) = 2

ceil(11.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-8 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-8 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-8 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 will be converted to Strings using the standard conversions listed above.

.

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

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. In addition, 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 will first be 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-8 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-8 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.

get

Extracts a value from a collection-like type (Array, String, Object). No conversion will be applied to the first argument. Conversion applies as documented in the table to the second argument. Supported by SQL version 2015-10-8 and later.

Examples:

get(["a", "b", "c"], 1) = "b"

get({"a":"b"}, "a") = "b"

get("abc", 1) = "b"

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_thing_shadow(thingName, roleARN)

Returns the 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.

roleArn

String: A role ARN with iot:GetThingShadow permission.

Example:

SELECT * from 'a/b'

WHERE get_thing_shadow("MyThing","arn:aws:iam::123456789012:role/AllowsThingShadowAccess") .state.reported.alarm = 'ON'

Hashing Functions

We provide 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-8 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 2015-10-8 and later.

Examples:

indexof("abcd", "bc") = 1

isNull()

Returns whether the argument is the Null value. Supported by SQL version 2015-10-8 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 whether the argument is Undefined. Supported by SQL version 2015-10-8 and later.

Examples:

isUndefined(5) = false.

isNull(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 2015-10-8 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-8 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-8 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-8 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 will be set to the nearest valid value (0 or 1000). Supported by SQL version 2015-10-8 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 will be 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 will be 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 will be converted to a String using the standard conversions, and then the LPAD function will be applied on that String. If it cannot be converted, the result is Undefined.
Any Value Other Value Undefined.

ltrim(String)

Removes all leading whitespace (tabs and spaces) from the provided String. Supported by SQL version 2015-10-8 and later.

Example:

Ltrim(" h i ") = "hi ".

Argument Type Result
Int The String representation of the Int with all leading whitespace removed.
Decimal The String representation of the Decimal with all leading whitespace removed.
Boolean The String representation of the boolean ("true" or "false") with all leading whitespace removed.
String The argument with all leading whitespace removed.
Array The String representation of the Array (using standard conversion rules) with all leading whitespace removed.
Object The String representation of the Object (using standard conversion rules) with all leading whitespace removed.
Null Undefined.
Undefined Undefined.

machinelearning_predict(modelId)

Use the machinelearning_predict function to make predictions using the data from an MQTT message based on an Amazon Machine Learning (Amazon ML) model. Supported by SQL version 2015-10-8 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 Amazon ML Predict API. This should be represented as a single layer JSON object. If the record is a multi-level JSON object, the record will be flattened by serializing its values. For example, the following JSON:

Copy
{ "key1": {"innerKey1": "value1"}, "key2": 0}

would become:

Copy
{ "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 Amazon ML to make predictions. The value must be SGD.

predictedScores

Contains the raw classification score corresponding to each label.

predictedValue

The value predicted by Amazon ML.

mod(Decimal, Decimal)

Returns the remainder of the division of the first argument by the second argument. Supported by SQL version 2015-10-8 and later. You can also use "%" as an infix operator for the same modulo functionality. Supported by SQL version 2015-10-8 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-8 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-8 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 2015-10-8 and later.

Examples:

numbytes("hi") = 2

numbytes("€") = 3

principal()

Returns the X.509 certificate fingerprint or thing name, depending on which endpoint, MQTT or HTTP, received the request. Supported by SQL version 2015-10-8 and later.

Example:

principal() = "ba67293af50bf2506f5f93469686da660c7c844e7b3950bfb16813e0d31e9373"

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. The arguments for the parse_time function are:

pattern

(String) A date/time pattern which conforms to the ISO 8601 standard format. (Specifically, the function supports Joda-Time formats.)

timestamp

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

timezone

(String) [Optional] The time zone of the formatted date/time. The default is "UTC". The function supports Joda-Time timezones

Examples:

When this message is published to the topic 'A/B', the payload {"ts": "1970.01.01 AD at 21:46:40 CST"} will be sent to the S3 bucket:

Copy
{ "ruleArn": "arn:aws:iot:us-east-2:ACCOUNT_ID:rule/RULE_NAME", "rule": { "awsIotSqlVersion": "2016-03-23", "sql": "SELECT parse_time("yyyy.MM.dd G 'at' HH:mm:ss z", 100000000, "America/Belize" ) as ts FROM 'A/B'", "ruleDisabled": false, "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) will be sent to the S3 bucket:

Copy
{ "ruleArn": "arn:aws:iot:us-east-2:ACCOUNT_ID:rule/RULE_NAME", "rule": { "awsIotSqlVersion": "2016-03-23", "sql": "SELECT parse_time("yyyy.MM.dd G 'at' HH:mm:ss z", timestamp() ) as ts FROM 'A/B'", "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 will be sent to the S3 bucket with key = "2017":

Copy
{ "ruleArn": "arn:aws:iot:us-east-2:ACCOUNT_ID:rule/RULE_NAME", "rule": { "awsIotSqlVersion": "2016-03-23", "sql": "SELECT * FROM 'A/B'", "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-8 and later. Supported by SQL version 2015-10-8 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.

rand()

Returns a pseudorandom, uniformly distributed double between 0.0 and 1.0. Supported by SQL version 2015-10-8 and later.

Example:

rand() = 0.8231909191640703

regexp_matches(String, String)

Returns whether the first argument contains a match for the second argument (regex).

Example:

Regexp_matches("aaaa", "a{2,}") = true.

Regexp_matches("aaaa", "b") = 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 may or may not be a valid regular expression. If the (converted) argument is not valid regex, the result is Undefined.

Third argument:

Must be a valid regex replacement string. (Can reference capture groups.) Non-string types will be converted to String using the standard conversion rules. If the (converted) argument is not a valid regex replacement string, 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-8 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 Strings using the standard conversion rules. Depending on the type, the resultant string may or may 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 will be converted to Strings 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 2nd parameter (regex) in the first parameter. Reference capture groups with "$". Supported by SQL version 2015-10-8 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 Strings using the standard conversion rules. Depending on the type, the resultant string may or may 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 will be converted to String using the standard conversion rules. If the argument is not a valid regex replacement string, the result is 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 will be set to the nearest valid value (0 or 1000). Supported by SQL version 2015-10-8 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 will be 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 will be 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 will be converted to a String using the standard conversions, and the rpad function will be 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-8 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 whitespace (tabs and spaces) from the provided String. Supported by SQL version 2015-10-8 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-8 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-8 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-8 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-8 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.

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 will be set to zero. Indices that are greater than the String length will be 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 will be 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-8 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) = "".

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-8 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-8 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 will be converted to Strings using the standard conversion rules. Returns whether 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-8 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-8 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.

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-8 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, one-based topic segment. For the topic foo/bar/baz, topic(1) will return foo, topic(2) will return bar, and so on. Supported by SQL version 2015-10-8 and later.

Examples:

topic() = "things/myThings/thingOne"

topic(1) = "things"

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-8 and later.

Example:

traceid() = "12345678-1234-1234-1234-123456789012"

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 will be set to zero. If the second argument is greater than 34, it will be set to 34. Trailing zeroes are stripped from the result. Supported by SQL version 2015-10-8 and later.

Examples:

trunc(2.3, 0) = 2.

trunc(2.3123, 2 = 2.31.

trunc(2.888, 2) = 2.88.

(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, will be rounded down to the nearest Int.
Int/Decimal/String The first argument is truncated to the length described by the second argument. The second argument, if not an Int, will be rounded down to the nearest Int. Strings are converted to Decimal values. If the string conversion fails, the result is Undefined.
Other Value Undefined.

trim(String)

Removes all leading and trailing whitespace from the provided String. Supported by SQL version 2015-10-8 and later.

Example:

Trim(" hi ") = "hi"

Argument Type Result
Int The String representation of the Int with all leading and trailing whitespace removed.
Decimal The String representation of the Decimal with all leading and trailing whitespace removed.
Boolean The String representation of the Boolean ("true" or "false") with all leading and trailing whitespace removed.
String The String with all leading and trailing whitespace 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.

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-8 and later.

Examples:

upper("hello") = "HELLO"

upper(["hello"]) = "[\"HELLO\"]"