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 .

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 Decimal s 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 Decimal s 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.
String s are converted to Decimal s
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.
String s are converted to Decimal s
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
Boolean
s. If we 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 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", "5E12" are all
examples of String s that would be converted
automatically to Decimal s.

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", "5E12" are all
examples of String s that would be converted
automatically to Decimal s. 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 (caseinsensitive). 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 will be a
commaseparated list enclosed in square brackets.
String s are quoted. Decimal s,
Int s, Boolean s are not.

Object  The object serialized to JSON. The JSON string will be a
commaseparated list of keyvalue pairs and will begin and end
with curly braces. String s are quoted.
Decimals , Int s,
Boolean s 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. 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 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 String s 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 will be 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
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 nonstring arguments. All hash functions are supported by SQL version 2015108 and later.
Examples:
md2("hello")
= "a9046c73e00331af68917d3804f70655"
md5("hello")
= "5d41402abc4b2a76b9719d911017c592"
hsin(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 .

htan(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 .

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 whether 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 whether the argument is Undefined
. Supported by SQL
version 2015108 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 String
s 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 will be 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 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 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 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 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 Decimal s, 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 which 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 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:useast2:ACCOUNT_ID:rule/RULE_NAME", "rule": { "awsIotSqlVersion": "20160323", "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:useast2:ACCOUNT_ID:rule/RULE_NAME", "rule": { "awsIotSqlVersion": "20160323", "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:useast2:ACCOUNT_ID:rule/RULE_NAME", "rule": { "awsIotSqlVersion": "20160323", "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 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 Decimal s. 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 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. Nonstring 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.)
Nonstring 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 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 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.)
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 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.)
Nonstring 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 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 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
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 .

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 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 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 will be converted to String s
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 .

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, onebased 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 2015108 and later.
Examples:
topic()
= "things/myThings/thingOne"
topic(1)
= "things"
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 .

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
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
2015108 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 . String s 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 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\"]"