Functions
You can use the following builtin functions in the SELECT or WHERE clauses of your SQL expressions.
abs(Decimal)
Returns the absolute value of a number. Supported by SQL version 2015108 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 2015108 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 2015108 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 2015108 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 2015108 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 xaxis and the (x, y)
point defined in the two arguments. The angle is positive for counterclockwise
angles (upper halfplane, y > 0), and negative for clockwise angles (lower
halfplane, y < 0). Decimal
arguments are rounded to double
precision before function application. Supported by SQL version 2015108 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 xaxis 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:
aws lambda addpermission functionname "function_name" region "
region
" principal iot.amazonaws.com sourcearn arn:aws:iot:useast1
:account_id
:rule/rule_name
sourceaccount "account_id
" statementid "unique_id
" action "lambda:InvokeFunction"
The following are the arguments for the addpermission command:
 functionname

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

The ARN of the rule. You can use the gettopicrule CLI command to get the ARN of a rule.
 sourceaccount

The AWS account where the rule is defined.
 statementid

A unique statement identifier.
 action

The Lambda action you want to allow in this statement. To allow AWS IoT to invoke a Lambda function, specify
lambda:InvokeFunction
.
Note
If you add a permission for an 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.
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:
useast1
:account_id
:function:lambda_function
", {"payload":attribute1) as output FROM 'a/b
'
If you want to pass the full MQTT message payload, you can specify the JSON payload using '*'. For example:
SELECT aws_lambda("arn:aws:lambda:
useast1
:account_id
:function:lambda_function
", *) 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
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 2015108 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
nonInt 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 2015108 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
nonInt 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 2015108 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.
NonInt 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 2015108 and
later.
Example: bitnot(13)
= 2
Argument Type  Result 

Int 
Int , a bitwise NOT of the argument.

Decimal 
Int , a bitwise NOT of the argument. The
Decimal value is rounded down to the nearest
Int .

String 
Int , a bitwise NOT of the argument. Strings are
converted to decimals and rounded down to the nearest
Int . If any conversion fails, the result is
Undefined .

Other Value  Other value. 
cast()
Converts a value from one data type to another. Cast behaves mostly like the
standard conversions, with the addition of the ability to cast numbers to or
from Booleans. If AWS IoT cannot determine how to cast one type to another, the
result is Undefined
. Supported by SQL version 2015108 and later.
Format: cast(value
as
type
).
Example:
cast(true as Decimal)
= 1.0
The following keywords might appear after "as" when calling
cast
:
For SQL version 2015108 and 20160323
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 .

Integer  Casts value to Int .

Additionally, for SQL version 20160323
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. 
Boolean 
true = 1.0, false = 0.0. 
String 
Tries to parse the string as a Decimal . AWS IoT
attempts to parse strings matching the regex:
^?\d+(\.\d+)?((?i)E?\d+)?$. "0", "1.2", "5E12" are all
examples of strings that are converted automatically to
decimals.

Array  Undefined .

Object  Undefined .

Null  Undefined .

Undefined  Undefined .

Cast to Int
Argument Type  Result 

Int 
The source value. 
Decimal 
The source value, rounded down to the nearest
Int .

Boolean 
true = 1.0, false = 0.0. 
String 
Tries to parse the string as a Decimal . AWS IoT
attempts to parse strings matching the regex:
^?\d+(\.\d+)?((?i)E?\d+)?$. "0", "1.2", "5E12" are all
examples of strings that are converted automatically to
decimals. AWS IoT attempts to convert the string to a
Decimal and round down to the nearest
Int .

Array  Undefined .

Object  Undefined .

Null  Undefined .

Undefined  Undefined .

Cast to Boolean
Argument Type  Result 

Int 
0 = False, any_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 (caseinsensitive). Other
string values = Undefined .

Array  The array serialized to JSON. The result string is a
commaseparated 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
commaseparated list of keyvalue 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 2015108 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 2015108 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 2015108 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 2015108 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 
cos(Decimal)
Returns the cosine of a number in radians. Decimal
arguments are
rounded to double precision before function application. Supported by SQL
version 2015108 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 2015108 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 nonJSON data, into its string representation based on the encoding
scheme. Supported by SQL version 20160323 and later.
 value

Any of the valid expressions, as defined in AWS 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 2015108 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 2015108 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 collectionlike 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 2015108 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 0based 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 0based 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 20160323 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
AWS IoT provides the following hashing functions:

md2

md5

sha1

sha224

sha256

sha384

sha512
All hash functions expect one string argument. The result is the hashed value of that string. Standard string conversions apply to nonstring arguments. All hash functions are supported by SQL version 2015108 and later.
Examples:
md2("hello")
= "a9046c73e00331af68917d3804f70655"
md5("hello")
= "5d41402abc4b2a76b9719d911017c592"
indexof(String, String)
Returns the first index (0based) 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 2015108 and later.
Examples:
indexof("abcd", "bc")
= 1
isNull()
Returns true if the argument is the Null
value. Supported by SQL
version 2015108 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 20160323 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 nonString
arguments. Supported by SQL version
2015108 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 2015108 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 2015108 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
. Nonstring
arguments are converted to strings using the standard conversion
rules. Supported by SQL version 2015108 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 2015108 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 whitespace (tabs and spaces) from the provided
String
. Supported by SQL version 2015108 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 2015108 and later. The arguments for the
machinelearning_predict
function are:
 modelId

The ID of the model against which to run the prediction. The realtime endpoint of the model must be enabled.
 roleArn

The IAM role that has a policy with
machinelearning:Predict
andmachinelearning: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 multilevel 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 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 2015108 and later. You can also use "%" as an infix operator for the same modulo functionality. Supported by SQL version 2015108 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 2015108 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 16byte UUID. Supported by SQL version 2015108 and later.
Example: newuuid()
=
123a4567b89c12d3e456789012345000
numbytes(String)
Returns the number of bytes in the UTF8 encoding of the provided string.
Standard conversion rules apply to nonString
arguments. Supported
by SQL version 2015108 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 2015108 and later.
Example:
principal()
=
"ba67293af50bf2506f5f93469686da660c7c844e7b3950bfb16813e0d31e9373"
parse_time(String, Long, [String])
Use the parse_time
function to format a timestamp into a
humanreadable date/time format. Supported by SQL version 20160323 and later.
The arguments for the parse_time
function are:
 pattern

(String) A date/time pattern that conforms to the ISO 8601 standard format. (Specifically, the function supports JodaTime 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 JodaTime time zones.
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:useast2: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": "20160323", "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:useast2: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": "20160323", "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:useast2:ACCOUNT_ID:rule/RULE_NAME", "topicRulePayload": { "sql": "SELECT * FROM 'A/B'", "awsIotSqlVersion": "20160323", "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 2015108 and later. Supported by SQL version 2015108 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 2015108 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).
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. Nonstring 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
.
Third argument:
Must be a valid regex replacement string. (Can reference capture groups.)
Nonstring 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_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 2015108 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. Nonstring types are converted to
String
s 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.)
Nonstring types will be converted to String
s 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 2015108 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. Nonstring types are converted to
String
s 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.)
Nonstring types are 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 is set to the nearest valid value (0 or 1000). Supported by SQL version
2015108 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
2015108 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 2015108 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 2015108 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 2015108 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 2015108 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
2015108 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 (0based, 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 (0based,
inclusive) to the second Int
index argument (0based, 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 2015108 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 2015108 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 2015108 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 2015108 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 2015108 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 2015108 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 2015108 and later.
Examples:
topic()
= "things/myThings/thingOne"
topic(1)
= "things"
When Basic Ingest is
used, the initial prefix of the topic
($aws/rules/
) is not
available to the topic() function. For example, given the topic:
rulename
$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 2015108 and
later.
Example:
traceid()
= "12345678123412341234123456789012"
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 2015108
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 .

trim(String)
Removes all leading and trailing whitespace from the provided
String
. Supported by SQL version 2015108 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
.
NonString
arguments are converted to String
using
the standard conversion rules. Supported by SQL version 2015108 and
later.
Examples:
upper("hello")
= "HELLO"
upper(["hello"])
= "[\"HELLO\"]"