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 TypeResult
IntInt, the absolute value of the argument.
DecimalDecimal, the absolute value of the argument.
BooleanUndefined.
StringDecimal. The result is the absolute value of the argument. If the string cannot be converted, the result is Undefined.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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 TypeResult
IntDecimal (with double precision), the inverse cosine of the argument. Imaginary results are returned as Undefined.
DecimalDecimal (with double precision), the inverse cosine of the argument. Imaginary results are returned as Undefined.
BooleanUndefined.
StringDecimal, the inverse cosine of the argument. If the string cannot be converted, the result is Undefined. Imaginary results are returned as Undefined.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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 TypeResult
IntDecimal (with double precision), the inverse sine of the argument. Imaginary results are returned as Undefined.
DecimalDecimal (with double precision), the inverse sine of the argument. Imaginary results are returned as Undefined.
BooleanUndefined.
StringDecimal (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.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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 TypeResult
IntDecimal (with double precision), the inverse tangent of the argument. Imaginary results are returned as Undefined.
DecimalDecimal (with double precision), the inverse tangent of the argument. Imaginary results are returned as Undefined.
BooleanUndefined.
StringDecimal, the inverse tangent of the argument. If the string cannot be converted, the result is Undefined. Imaginary results are returned as Undefined.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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 TypeArgument TypeResult
Int/DecimalInt/DecimalDecimal (with double precision), the angle between the x-axis and the specified (x,y) point.
Int/Decimal/StringInt/Decimal/StringDecimal, the inverse tangent of the point described. If a string cannot be converted, the result is Undefined.
Other ValueOther ValueUndefined.

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 TypeArgument TypeResult
IntIntInt, a bitwise AND of the two arguments.
Int/DecimalInt/DecimalInt, 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/StringInt/Decimal/StringInt, 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 ValueOther ValueUndefined.

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 TypeArgument TypeResult
IntIntInt, the bitwise OR of the two arguments.
Int/DecimalInt/DecimalInt, 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/StringInt/Decimal/StringInt, 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 ValueOther ValueUndefined.

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 TypeArgument TypeResult
IntIntInt, a bitwise XOR on the two arguments.
Int/DecimalInt/DecimalInt, a bitwise XOR on the two arguments. Non-Int numbers are rounded down to the nearest Int.
Int/Decimal/StringInt/Decimal/StringInt, 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 ValueOther ValueUndefined.

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 TypeResult
IntInt, a bitwise NOT of the argument.
DecimalInt, a bitwise NOT of the argument. The Decimal value is rounded down to the nearest Int.
StringInt, 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 ValueOther 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:

KeywordResult
DecimalCasts value to Decimal.
BoolCasts value to Boolean.
BooleanCasts value to Boolean.
StringCasts value to String.
NvarcharCasts value to String.
TextCasts value to String.
NtextCasts value to String.
varcharCasts value to String.
IntCasts value to Int.
IntCasts value to Int.

Casting rules:

Cast to Decimal

Argument TypeResult
IntA Decimal with no decimal point.
DecimalThe source value.
Booleantrue = 1.0, false = 0.0.
StringWill 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.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

Cast to Int

Argument TypeResult
IntThe source value.
DecimalThe source value, rounded down to the nearest Int.
Booleantrue = 1.0, false = 0.0.
StringWill 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.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

Cast to Boolean

Argument TypeResult
Int0 = False, any_nonzero_value = True.
Decimal0 = False, any_nonzero_value = True.
BooleanThe source value.
String"true" = True and "false" = False (case-insensitive). Other string values = Undefined.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

Cast to String

Argument TypeResult
IntA string representation of the Int, in standard notation.
DecimalA 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.
ArrayThe 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.
ObjectThe 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.
NullUndefined.
UndefinedUndefined.

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 TypeResult
IntInt, the argument value.
DecimalInt, the Decimal value rounded up to the nearest Int.
StringInt. 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 ValueUndefined.

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 TypeResult
IntThe character corresponding to the specified ASCII value. If the argument is not a valid ASCII value, the result is Undefined.
DecimalThe 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.
BooleanUndefined.
StringIf 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.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
Other ValueUndefined.

clientid()

Returns the ID of the MQTT client sending the message, or Undefined 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 ArgumentsResult
0Undefined.
1The 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 TypeResult
IntDecimal (with double precision), the cosine of the argument. Imaginary results are returned as Undefined.
DecimalDecimal (with double precision), the cosine of the argument. Imaginary results are returned as Undefined.
BooleanUndefined.
StringDecimal (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.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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 TypeResult
IntDecimal (with double precision), the hyperbolic cosine of the argument. Imaginary results are returned as Undefined.
DecimalDecimal (with double precision), the hyperbolic cosine of the argument. Imaginary results are returned as Undefined.
BooleanUndefined.
StringDecimal (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.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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 1argument Type 2Result
StringStringTrue if the first argument ends in the second argument; otherwise, false.
Other ValueOther ValueBoth 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 TypeResult
IntDecimal (with double precision), e ^ argument.
DecimalDecimal (with double precision), e ^ argument.
StringDecimal (with double precision), e ^ argument. If the String cannot be converted to a Decimal, the result is Undefined.
Other ValueUndefined.

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 1argument Type 2Result
ArrayAny 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.
StringAny 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.
ObjectString (no conversion is applied)The value stored in the first argument object corresponding to the string key provided as the second argument.
Other ValueAny ValueUndefined.

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"

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

Example: sinh(2.3) = 4.936961805545957

Argument TypeResult
IntDecimal (with double precision), the hyperbolic sine of the argument.
DecimalDecimal (with double precision), the hyperbolic sine of the argument.
BooleanUndefined.
StringDecimal (with double precision), the hyperbolic sine of the argument. If the string cannot be converted to a Decimal, the result is Undefined.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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

Example: tanh(2.3) = 0.9800963962661914

Argument TypeResult
IntDecimal (with double precision), the hyperbolic tangent of the argument.
DecimalDecimal (with double precision), the hyperbolic tangent of the argument.
BooleanUndefined.
StringDecimal (with double precision), the hyperbolic tangent of the argument. If the string cannot be converted to a Decimal, the result is Undefined.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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 TypeResult
Intfalse
Decimalfalse
Booleanfalse
Stringfalse
Arrayfalse
Objectfalse
Nulltrue
Undefinedfalse

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 TypeResult
Intfalse
Decimalfalse
Booleanfalse
Stringfalse
Arrayfalse
Objectfalse
Nullfalse
Undefinedtrue

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 TypeResult
IntDecimal (with double precision), the natural log of the argument.
DecimalDecimal (with double precision), the natural log of the argument.
BooleanUndefined.
StringDecimal (with double precision), the natural log of the argument. If the string cannot be converted to a Decimal the result is Undefined.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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 TypeResult
IntDecimal (with double precision), the base 10 log of the argument.
DecimalDecimal (with double precision), the base 10 log of the argument.
BooleanUndefined.
StringDecimal (with double precision), the base 10 log of the argument. If the String cannot be converted to a Decimal, the result is Undefined.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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 1argument Type 2Result
StringIntString, the provided String padded on the left side with a number of spaces equal to the provided Int.
StringDecimalThe Decimal argument will be rounded down to the nearest Int and the String is padded on the left with the specified number of spaces.
StringStringThe 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 ValueInt/Decimal/StringThe 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 ValueOther ValueUndefined.

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 TypeResult
IntThe String representation of the Int with all leading whitespace removed.
DecimalThe String representation of the Decimal with all leading whitespace removed.
BooleanThe String representation of the boolean ("true" or "false") with all leading whitespace removed.
StringThe argument with all leading whitespace removed.
ArrayThe String representation of the Array (using standard conversion rules) with all leading whitespace removed.
ObjectThe String representation of the Object (using standard conversion rules) with all leading whitespace removed.
NullUndefined.
UndefinedUndefined.

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:

{ "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 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 OperandRight OperandOutput
IntIntInt, the first argument modulo the second argument.
Int/DecimalInt/DecimalDecimal, the first argument modulo the second operand.
String/Int/DecimalString/Int/DecimalIf all strings convert to Decimals, the result is the first argument modulo the second argument; otherwise, Undefined.
Other ValueOther ValueUndefined.

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 1argument Type 2Output
UndefinedAny ValueThe second argument.
NullAny ValueThe second argument.
Decimal (NaN)Any ValueThe second argument.
Decimal (not NaN)Any ValueThe first argument.
Other ValueAny ValueThe first argument.

newuuid()

Returns a random 16-byte UUID. Supported by SQL version 2015-10-8 and later.

Example: uuid() = 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") = 4

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"

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 1argument Type 2Output
Int/DecimalInt/DecimalA Decimal (with double precision), the first argument raised to the second argument's power.
Int/Decimal/StringInt/Decimal/StringA 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 ValueOther ValueUndefined.

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 TypeResult
IntThe String representation of the Int.
DecimalThe String representation of the Decimal.
BooleanThe String representation of the boolean ("true" or "false").
StringThe String.
ArrayThe String representation of the Array (using standard conversion rules).
ObjectThe String representation of the Object (using standard conversion rules).
NullUndefined.
UndefinedUndefined.

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 TypeResult
IntThe String representation of the Int.
DecimalThe String representation of the Decimal.
BooleanThe String representation of the boolean ("true" or "false").
StringThe source value.
ArrayThe String representation of the Array (using standard conversion rules).
ObjectThe String representation of the Object (using standard conversion rules).
NullUndefined.
UndefinedUndefined.

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 TypeResult
IntThe String representation of the Int.
DecimalThe String representation of the Decimal.
BooleanThe String representation of the boolean ("true" or "false").
StringThe String argument.
ArrayThe String representation of the Array (using standard conversion rules).
ObjectThe String representation of the Object (using standard conversion rules).
NullUndefined.
UndefinedUndefined.

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 1argument Type 2Result
StringIntThe String is padded on the right side with a number of spaces equal to the provided Int.
StringDecimalThe 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.
StringStringThe 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 ValueInt/Decimal/StringThe 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 ValueOther ValueUndefined.

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 TypeResult
IntThe argument.
DecimalDecimal is rounded down to the nearest Int.
StringDecimal is rounded down to the nearest Int. If the string cannot be converted to a Decimal, the result is Undefined.
Other ValueUndefined.

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 TypeResult
IntThe String representation of the Int.
DecimalThe String representation of the Decimal.
BooleanThe String representation of the boolean ("true" or "false").
ArrayThe String representation of the Array (using standard conversion rules).
ObjectThe String representation of the Object (using standard conversion rules).
NullUndefined.
UndefinedUndefined

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 TypeResult
IntInt, the sign of the Int value.
DecimalInt, the sign of the Decimal value.
StringInt, 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 ValueUndefined.

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 TypeResult
IntDecimal (with double precision), the sine of the argument.
DecimalDecimal (with double precision), the sine of the argument.
BooleanUndefined.
StringDecimal (with double precision), the sine of the argument. If the string cannot be converted to a Decimal, the result is Undefined.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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 TypeResult
IntThe square root of the argument.
DecimalThe square root of the argument.
BooleanUndefined.
StringThe square root of the argument. If the string cannot be converted to a Decimal, the result is Undefined.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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 1argument Type 2Result
StringStringWhether the first string starts with the second string.
Other ValueOther ValueBoth 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.

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"

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 TypeResult
IntDecimal (with double precision), the tangent of the argument.
DecimalDecimal (with double precision), the tangent of the argument.
BooleanUndefined.
StringDecimal (with double precision), the tangent of the argument. If the string cannot be converted to a Decimal, the result is Undefined.
ArrayUndefined.
ObjectUndefined.
NullUndefined.
UndefinedUndefined.

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 1argument Type 2Result
IntIntThe source value.
Int/DecimalInt/DecimalThe 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/StringThe 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 ValueUndefined. 

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 TypeResult
IntThe String representation of the Int with all leading and trailing whitespace removed.
DecimalThe String representation of the Decimal with all leading and trailing whitespace removed.
BooleanThe String representation of the Boolean ("true" or "false") with all leading and trailing whitespace removed.
StringThe String with all leading and trailing whitespace removed.
ArrayThe String representation of the Array using standard conversion rules.
ObjectThe String representation of the Object using standard conversion rules.
NullUndefined.
UndefinedUndefined.

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