A newer version of this documentation is available.

View Latest

Builtin Functions

Numeric Functions

abs

  • Syntax:

    abs(numeric_value)

  • Computes the absolute value of the argument.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • The absolute value of the argument with the same type as the input argument,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": abs(2013), "v2": abs(-4036), "v3": abs(0), "v4": abs(float("-2013.5")), "v5": abs(double("-2013.593823748327284")) };

  • The expected result is:

    { "v1": 2013, "v2": 4036, "v3": 0, "v4": 2013.5, "v5": 2013.5938237483274 }

acos

  • Syntax:

    acos(numeric_value)

  • Computes the arc cosine value of the argument.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • the double arc cosine in radians for the argument, if the argument is in the range of -1 (inclusive) to 1 (inclusive),

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error,

    • "NaN" for other legitimate numeric values.

  • Example:

    { "v1": acos(1), "v2": acos(2), "v3": acos(0), "v4": acos(float("0.5")), "v5": acos(double("-0.5")) };

  • The expected result is:

    { "v1": 0.0, "v2": "NaN", "v3": 1.5707963267948966, "v4": 1.0471975511965979, "v5": 2.0943951023931957 }

asin

  • Syntax:

    asin(numeric_value)

  • Computes the arc sine value of the argument.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • the double arc sin in radians for the argument, if the argument is in the range of -1 (inclusive) to 1 (inclusive),

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error,

    • "NaN" for other legitimate numeric values.

  • Example:

    { "v1": asin(1), "v2": asin(2), "v3": asin(0), "v4": asin(float("0.5")), "v5": asin(double("-0.5")) };

  • The expected result is:

    { "v1": 1.5707963267948966, "v2": "NaN", "v3": 0.0, "v4": 0.5235987755982989, "v5": -0.5235987755982989 }

atan

  • Syntax:

    atan(numeric_value)

  • Computes the arc tangent value of the argument.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • the double arc tangent in radians for the argument,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": atan(1), "v2": atan(2), "v3": atan(0), "v4": atan(float("0.5")), "v5": atan(double("1000")) };

  • The expected result is:

    { "v1": 0.7853981633974483, "v2": 1.1071487177940904, "v3": 0.0, "v4": 0.4636476090008061, "v5": 1.5697963271282298 }

atan2

  • Syntax:

    atan2(numeric_value1, numeric_value2)

  • Computes the arc tangent value of numeric_value2/numeric_value1.

  • Arguments:

    • numeric_value1: a tinyint/smallint/integer/bigint/float/double value,

    • numeric_value2: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • the double arc tangent in radians for numeric_value1 and numeric_value2,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": atan2(1, 2), "v2": atan2(0, 4), "v3": atan2(float("0.5"), double("-0.5")) };

  • The expected result is:

    { "v1": 0.4636476090008061, "v2": 0.0, "v3": 2.356194490192345 }

ceil

  • Syntax:

    ceil(numeric_value)

  • Computes the smallest (closest to negative infinity) number with no fractional part that is not less than the value of the argument. If the argument is already equal to mathematical integer, then the result is the same as the argument.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • The ceiling value for the given number in the same type as the input argument,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    {
  "v1": ceil(2013),
  "v2": ceil(-4036),
  "v3": ceil(0.3),
  "v4": ceil(float("-2013.2")),
  "v5": ceil(double("-2013.893823748327284"))
};

  • The expected result is:

    { "v1": 2013, "v2": -4036, "v3": 1.0, "v4": -2013.0, "v5": -2013.0 }

cos

  • Syntax:

    cos(numeric_value)

  • Computes the cosine value of the argument.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • the double cosine value for the argument,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": cos(1), "v2": cos(2), "v3": cos(0), "v4": cos(float("0.5")), "v5": cos(double("1000")) };

  • The expected result is:

    { "v1": 0.5403023058681398, "v2": -0.4161468365471424, "v3": 1.0, "v4": 0.8775825618903728, "v5": 0.562379076290703 }

exp

  • Syntax:

    exp(numeric_value)

  • Computes enumeric_value.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • enumeric_value,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": exp(1), "v2": exp(2), "v3": exp(0), "v4": exp(float("0.5")), "v5": exp(double("1000")) };

  • The expected result is:

    { "v1": 2.718281828459045, "v2": 7.38905609893065, "v3": 1.0, "v4": 1.6487212707001282, "v5": "Infinity" }

floor

  • Syntax:

    floor(numeric_value)

  • Computes the largest (closest to positive infinity) number with no fractional part that is not greater than the value. If the argument is already equal to mathematical integer, then the result is the same as the argument.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • The floor value for the given number in the same type as the input argument,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    {
  "v1": floor(2013),
  "v2": floor(-4036),
  "v3": floor(0.8),
  "v4": floor(float("-2013.2")),
  "v5": floor(double("-2013.893823748327284"))
};

  • The expected result is:

    { "v1": 2013, "v2": -4036, "v3": 0.0, "v4": -2014.0, "v5": -2014.0 }

ln

  • Syntax:

    ln(numeric_value)

  • Computes logenumeric_value.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • logenumeric_value,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": ln(1), "v2": ln(2), "v3": ln(0), "v4": ln(float("0.5")), "v5": ln(double("1000")) };

  • The expected result is:

    { "v1": 0.0, "v2": 0.6931471805599453, "v3": "-Infinity", "v4": -0.6931471805599453, "v5": 6.907755278982137 }

log

  • Syntax:

    log(numeric_value)

  • Computes log10numeric_value.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • log10numeric_value,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": log(1), "v2": log(2), "v3": log(0), "v4": log(float("0.5")), "v5": log(double("1000")) };

  • The expected result is:

    { "v1": 0.0, "v2": 0.3010299956639812, "v3": "-Infinity", "v4": -0.3010299956639812, "v5": 3.0 }

power

  • Syntax:

    power(numeric_value1, numeric_value2)

  • Computes numeric_value1numeric_value2.

  • Arguments:

    • numeric_value1: a tinyint/smallint/integer/bigint/float/double value,

    • numeric_value2: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • numeric_value1numeric_value2,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": power(1, 2), "v3": power(0, 4), "v4": power(float("0.5"), double("-0.5")) };

  • The expected result is:

    { "v1": 1, "v3": 0, "v4": 1.4142135623730951 }

round

  • Syntax:

    round(numeric_value)

  • Computes the number with no fractional part that is closest (and also closest to positive infinity) to the argument.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • The rounded value for the given number in the same type as the input argument,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    {
  "v1": round(2013),
  "v2": round(-4036),
  "v3": round(0.8),
  "v4": round(float("-2013.256")),
  "v5": round(double("-2013.893823748327284"))
};

  • The expected result is:

    { "v1": 2013, "v2": -4036, "v3": 1.0, "v4": -2013.0, "v5": -2014.0 }

sign

  • Syntax:

    sign(numeric_value)

  • Computes the sign of the argument.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • the sign (a tinyint) of the argument, -1 for negative values, 0 for 0, and 1 for positive values,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": sign(1), "v2": sign(2), "v3": sign(0), "v4": sign(float("0.5")), "v5": sign(double("-1000")) };

  • The expected result is:

    { "v1": 1, "v2": 1, "v3": 0, "v4": 1, "v5": -1 }

sin

  • Syntax:

    sin(numeric_value)

  • Computes the sine value of the argument.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • the double sine value for the argument,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": sin(1), "v2": sin(2), "v3": sin(0), "v4": sin(float("0.5")), "v5": sin(double("1000")) };

  • The expected result is:

    { "v1": 0.8414709848078965, "v2": 0.9092974268256817, "v3": 0.0, "v4": 0.479425538604203, "v5": 0.8268795405320025 }

sqrt

  • Syntax:

    sqrt(numeric_value)

  • Computes the square root of the argument.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • the double square root value for the argument,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": sqrt(1), "v2": sqrt(2), "v3": sqrt(0), "v4": sqrt(float("0.5")), "v5": sqrt(double("1000")) };

  • The expected result is:

    { "v1": 1.0, "v2": 1.4142135623730951, "v3": 0.0, "v4": 0.7071067811865476, "v5": 31.622776601683793 }

tan

  • Syntax:

    tan(numeric_value)

  • Computes the tangent value of the argument.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value.

  • Return Value:

    • the double tangent value for the argument,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": tan(1), "v2": tan(2), "v3": tan(0), "v4": tan(float("0.5")), "v5": tan(double("1000")) };

  • The expected result is:

    { "v1": 1.5574077246549023, "v2": -2.185039863261519, "v3": 0.0, "v4": 0.5463024898437905, "v5": 1.4703241557027185 }

trunc

  • Syntax:

    trunc(numeric_value, number_digits)

  • Truncates the number to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

  • Arguments:

    • numeric_value: a tinyint/smallint/integer/bigint/float/double value,

    • number_digits: a tinyint/smallint/integer/bigint value.

  • Return Value:

    • the double tangent value for the argument,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is missing,

    • a type error will be raised if:

      • the first argument is any other non-numeric value,

      • the second argument is any other non-tinyint, non-smallint, non-integer, and non-bigint value.

  • Example:

    { "v1": trunc(1, 1), "v2": trunc(2, -2), "v3": trunc(0.122, 2), "v4": trunc(float("11.52"), -1), "v5": trunc(double("1000.5252"), 3) };

  • The expected result is:

    { "v1": 1, "v2": 2, "v3": 0.12, "v4": 10.0, "v5": 1000.525 }

String Functions

concat

  • Syntax:

    concat(string1, string2, ...)

  • Returns a concatenated string from arguments.

  • Arguments:

    • string1: a string value,

    • string2: a string value,

    • …​.

  • Return Value:

    • a concatenated string from arguments,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-string input value will cause a type error.

  • Example:

    concat("test ", "driven ", "development");

  • The expected result is:

    "test driven development"

contains

  • Syntax:

    contains(string, substring_to_contain)

  • Checks whether the string string contains the string substring_to_contain

  • Arguments:

    • string : a string that might contain the given substring,

    • substring_to_contain : a target string that might be contained.

  • Return Value:

    • a boolean value, true if string contains substring_to_contain,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-string input value will cause a type error,

    • false otherwise.

  • Example:

    { "v1": contains("I like x-phone", "phone"), "v2": contains("one", "phone") };

  • The expected result is:

    { "v1": true, "v2": false }

ends_with

  • Syntax:

    ends_with(string, substring_to_end_with)

  • Checks whether the string string ends with the string substring_to_end_with.

  • Arguments:

    • string : a string that might end with the given string,

    • substring_to_end_with : a string that might be contained as the ending substring.

  • Return Value:

    • a boolean value, true if string contains substring_to_contain,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-string input value will cause a type error,

    • false otherwise.

  • Example:

    {
  "v1": ends_with(" love product-b its shortcut_menu is awesome:)", ":)"),
  "v2": ends_with(" awsome:)", ":-)")
};

  • The expected result is:

    { "v1": true, "v2": false }

initcap (or title)

  • Syntax:

    initcap(string)

  • Converts a given string string so that the first letter of each word is uppercase and every other letter is lowercase. The function has an alias called "title".

  • Arguments:

    • string : a string to be converted.

  • Return Value:

    • a string as the title form of the given string,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-string input value will cause a type error.

  • Example:

    { "v1": initcap("ANALYTICS is here!"), "v2": title("ANALYTICS is here!") };

  • The expected result is:

    { "v1": "Analytics Is Here!", "v2": "Analytics Is Here!" }

length

  • Syntax:

    length(string)

  • Returns the length of the string string.

  • Arguments:

    • string : a string or null that represents the string to be checked.

  • Return Value:

    • an bigint that represents the length of string,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-string input value will cause a type error.

  • Example:

    length("test string");

  • The expected result is:

    11

lower

  • Syntax:

    lower(string)

  • Converts a given string string to its lowercase form.

  • Arguments:

    • string : a string to be converted.

  • Return Value:

    • a string as the lowercase form of the given string,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-string input value will cause a type error.

  • Example:

    lower("ANALYTICS");

  • The expected result is:

    "analytics"

ltrim

  • Syntax:

    ltrim(string[, chars]);

  • Returns a new string with all leading characters that appear in chars removed. By default, white space is the character to trim.

  • Arguments:

    • string : a string to be trimmed,

    • chars : a string that contains characters that are used to trim.

  • Return Value:

    • a trimmed, new string,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-string input value will cause a type error.

  • Example:

    ltrim("me like x-phone", "eml");

  • The expected result is:

    " like x-phone"

position

  • Syntax:

    position(string, string_pattern)

  • Returns the first position of string_pattern within string.

  • Arguments:

    • string : a string that might contain the pattern,

    • string_pattern : a pattern string to be matched.

  • Return Value:

    • the first position that string_pattern appears within string (starting at 0), or -1 if it does not appear,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-string input value will cause a type error.

  • Example:

    {
  "v1": position("ppphonepp", "phone"),
  "v2": position("hone", "phone")
};

  • The expected result is:

    { "v1": 3, "v2": -1 }

regexp_contains

  • Syntax:

    regexp_contains(string, string_pattern[, string_flags])

  • Checks whether the strings string contains the regular expression pattern string_pattern (a Java regular expression pattern).

  • Arguments:

    • string : a string that might contain the pattern,

    • string_pattern : a pattern string to be matched,

    • string_flag : (Optional) a string with flags to be used during regular expression matching.

      • The following modes are enabled with these flags: dotall (s), multiline (m), case_insensitive (i), and comments and whitespace (x).

  • Return Value:

    • a boolean, returns true if string contains the pattern string_pattern,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-string input value will cause a type error,

    • false otherwise.

  • Example:

    {
  "v1": regexp_contains("pphonepp", "p*hone"),
  "v2": regexp_contains("hone", "p+hone")
}

  • The expected result is:

    { "v1": true, "v2": false }

regexp_like

  • Syntax:

    regexp_like(string, string_pattern[, string_flags])

  • Checks whether the string string exactly matches the regular expression pattern string_pattern (a Java regular expression pattern).

  • Arguments:

    • string : a string that might contain the pattern,

    • string_pattern : a pattern string that might be contained,

    • string_flag : (Optional) a string with flags to be used during regular expression matching.

      • The following modes are enabled with these flags: dotall (s), multiline (m), case_insensitive (i), and comments and whitespace (x).

  • Return Value:

    • a boolean value, true if string contains the pattern string_pattern,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-string input value will cause a type error,

    • false otherwise.

  • Example:

    {
  "v1": regexp_like(" can't stand acast the network is horrible:(", ".*acast.*"),
  "v2": regexp_like("acast", ".*acst.*")
};

  • The expected result is:

    { "v1": true, "v2": false }

regexp_position

  • Syntax:

    regexp_position(string, string_pattern[, string_flags])

  • Returns first position of the regular expression string_pattern (a Java regular expression pattern) within string.

  • Arguments:

    • string : a string that might contain the pattern,

    • string_pattern : a pattern string to be matched,

    • string_flag : (Optional) a string with flags to be used during regular expression matching.

      • The following modes are enabled with these flags: dotall (s), multiline (m), case_insensitive (i), and comments and whitespace (x).

  • Return Value:

    • the first position that the regular expression string_pattern appears in string (starting at 0), or -1 if it does not appear.

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-string input value will cause a type error.

  • Example:

    {
  "v1": regexp_position("pphonepp", "p*hone"),
  "v2": regexp_position("hone", "p+hone")
};

  • The expected result is:

    { "v1": 1, "v2": -1 }

regexp_replace

  • Syntax:

    regexp_replace(string, string_pattern, string_replacement[, string_flags])

  • Checks whether the string string matches the given regular expression pattern string_pattern (a Java regular expression pattern), and replaces the matched pattern string_pattern with the new pattern string_replacement.

  • Arguments:

    • string : a string that might contain the pattern,

    • string_pattern : a pattern string to be matched,

    • string_replacement : a pattern string to be used as the replacement,

    • string_flag : (Optional) a string with flags to be used during replace.

      • The following modes are enabled with these flags: dotall (s), multiline (m), case_insensitive (i), and comments and whitespace (x).

  • Return Value:

    • Returns a string that is obtained after the replacements,

    • missing if any argument is a missing value,

    • any other non-string input value will cause a type error,

    • null if any argument is a null value but no argument is a missing value.

  • Example:

    regexp_replace(" like x-phone the voicemail_service is awesome", " like x-phone", "like product-a")

  • The expected result is:

    "like product-a the voicemail_service is awesome"

repeat

  • Syntax:

    repeat(string, n)

  • Returns a string formed by repeating the input string n times.

  • Arguments:

    • string : a string to be repeated,

    • n : an tinyint/smallint/integer/bigint value - how many times the string should be repeated.

  • Return Value:

    • a string that repeats the input string n times,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • a type error will be raised if:

      • the first argument is any other non-string value,

      • or, the second argument is not a tinyint, smallint, integer, or bigint.

  • Example:

    repeat("test", 3);

  • The expected result is:

    "testtesttest"

replace

  • Syntax:

    replace(string, search_string, replacement_string[, limit])

  • Finds occurrences of the given substring search_string in the input string string and replaces them with the new substring replacement_string.

  • Arguments:

    • string : an input string,

    • search_string : a string substring to be searched for,

    • replacement_string : a string to be used as the replacement,

    • limit : (Optional) an integer - maximum number of occurrences to be replaced. If not specified then all occurrences will be replaced

  • Return Value:

    • Returns a string that is obtained after the replacements,

    • missing if any argument is a missing value,

    • any other non-string input value or non-integer limit will cause a type error,

    • null if any argument is a null value but no argument is a missing value.

  • Example:

    {
  "v1": replace(" like x-phone the voicemail_service is awesome", " like x-phone", "like product-a"),
  "v2": replace("x-phone and x-phone", "x-phone", "product-a", 1)
};

  • The expected result is:

    {
  "v1": "like product-a the voicemail_service is awesome",
  "v2": "product-a and x-phone"
}

rtrim

  • Syntax:

    rtrim(string[, chars]);

  • Returns a new string with all trailing characters that appear in chars removed. By default, white space is the character to trim.

  • Arguments:

    • string : a string to be trimmed,

    • chars : a string that contains characters that are used to trim.

  • Return Value:

    • a trimmed, new string,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-string input value will cause a type error.

  • Example:

    {
  "v1": rtrim("i like x-phone", "x-phone"),
  "v2": rtrim("i like x-phone", "onexph")
};

  • The expected result is:

    { "v1": "i like ", "v2": "i like " }

split

  • Syntax:

    split(string, sep)

  • Splits the input string into an array of substrings separated by the string sep.

  • Arguments:

    • string : a string to be split.

  • Return Value:

    • an array of substrings by splitting the input string by sep,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-string input value will cause a type error.

  • Example:

    split("test driven development", " ");

  • The expected result is:

    [ "test", "driven", "development" ]

starts_with

  • Syntax:

    starts_with(string, substring_to_start_with)

  • Checks whether the string string starts with the string substring_to_start_with.

  • Arguments:

    • string : a string that might start with the given string.

    • substring_to_start_with : a string that might be contained as the starting substring.

  • Return Value:

    • a boolean, returns true if string starts with the string substring_to_start_with,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-string input value will cause a type error,

    • false otherwise.

  • Example:

    {
  "v1" : starts_with(" like the plan, amazing", " like"),
  "v2" : starts_with("I like the plan, amazing", " like")
};

  • The expected result is:

    { "v1": true, "v2": false }

substr

  • Syntax:

    substr(string, offset[, length])

  • Returns the substring from the given string string based on the given start offset offset with the optional length.

  • Arguments:

    • string : a string to be extracted,

    • offset : an tinyint/smallint/integer/bigint value as the starting offset of the substring in string (starting at 0),

    • length : (Optional) an an tinyint/smallint/integer/bigint value as the length of the substring.

  • Return Value:

    • a string that represents the substring,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • a type error will be raised if:

      • the first argument is any other non-string value,

      • or, the second argument is not a tinyint, smallint, integer, or bigint,

      • or, the third argument is not a tinyint, smallint, integer, or bigint if the argument is present.

  • Example:

    substr("test string", 6, 3);

  • The expected result is:

    "str"

trim

  • Syntax:

    trim(string[, chars]);

  • Returns a new string with all leading characters that appear in chars removed. By default, white space is the character to trim.

  • Arguments:

    • string : a string to be trimmed,

    • chars : a string that contains characters that are used to trim.

  • Return Value:

    • a trimmed, new string,

    • missing if any argument is a missing value,

    • null if any argument is a null value but no argument is a missing value,

    • any other non-string input value will cause a type error.

  • Example:

    trim("i like x-phone", "xphoen");

  • The expected result is:

    " like "

upper

  • Syntax:

    upper(string)

  • Converts a given string string to its uppercase form.

  • Arguments:

    • string : a string to be converted.

  • Return Value:

    • a string as the uppercase form of the given string,

    • missing if the argument is a missing value,

    • null if the argument is a null value,

    • any other non-string input value will cause a type error.

  • Example:

    upper("hello")

  • The expected result is:

    "HELLO"

Aggregate Functions (Array Functions)

This section contains detailed descriptions of each SQL++ aggregate function (i.e., array function). Note that as described in the SQL++ query reference documentation, standard SQL aggregate functions (e.g., MIN, MAX, SUM, COUNT, and AVG) are not real functions in SQL++ but just syntactic sugars over corresponding SQL++ builtin aggregate functions (e.g., ARRAY_MIN, ARRAY_MAX, ARRAY_SUM, ARRAY_COUNT, and ARRAY_AVG).

array_count

  • Syntax:

    array_count(collection)

  • Gets the number of non-null and non-missing items in the given collection.

  • Arguments:

    • collection could be:

      • an array or multiset to be counted,

      • or, a null value,

      • or, a missing value.

  • Return Value:

    • a bigint value representing the number of non-null and non-missing items in the given collection,

    • null is returned if the input is null or missing,

    • any other non-array and non-multiset input value will cause an error.

  • Example:

    array_count( ['hello', 'world', 1, 2, 3, null, missing] );

  • The expected result is:

    5

array_avg

  • Syntax:

    array_avg(num_collection)

  • Gets the average value of the non-null and non-missing numeric items in the given collection.

  • Arguments:

    • num_collection could be:

      • an array or multiset containing numeric values, nulls or missings,

      • or, a null value,

      • or, a missing value.

  • Return Value:

    • a double value representing the average of the non-null and non-missing numbers in the given collection,

    • null is returned if the input is null or missing,

    • null is returned if the given collection does not contain any non-null and non-missing items,

    • any other non-array and non-multiset input value will cause a type error,

    • any other non-numeric value in the input collection will cause a type error.

  • Example:

    array_avg( [1.2, 2.3, 3.4, 0, null] );

  • The expected result is:

    1.725

array_sum

  • Syntax:

    array_sum(num_collection)

  • Gets the sum of non-null and non-missing items in the given collection.

  • Arguments:

    • num_collection could be:

      • an array or multiset containing numeric values, nulls or missings,

      • or, a null value,

      • or, a missing value.

  • Return Value:

    • the sum of the non-null and non-missing numbers in the given collection. The returning type is decided by the item type with the highest order in the numeric type promotion order (tinyint-> smallint->integer->bigint->float->double) among items.

    • null is returned if the input is null or missing,

    • null is returned if the given collection does not contain any non-null and non-missing items,

    • any other non-array and non-multiset input value will cause a type error,

    • any other non-numeric value in the input collection will cause a type error.

  • Example:

    array_sum( [1.2, 2.3, 3.4, 0, null, missing] );

  • The expected result is:

    6.9

array_sql_min

  • Syntax:

    array_min(num_collection)

  • Gets the min value of non-null and non-missing comparable items in the given collection.

  • Arguments:

    • num_collection could be:

      • an array or multiset,

      • or, a null value,

      • or, a missing value.

  • Return Value:

    • the min value of non-null and non-missing values in the given collection. The returning type is decided by the item type with the highest order in the type promotion order (tinyint-> smallint->integer->bigint->float->double) among numeric items.

    • null is returned if the input is null or missing,

    • null is returned if the given collection does not contain any non-null and non-missing items,

    • multiple incomparable items in the input array or multiset will cause a type error,

    • any other non-array and non-multiset input value will cause a type error.

  • Example:

    array_min( [1.2, 2.3, 3.4, 0, null, missing] );

  • The expected result is:

    0.0

array_max

  • Syntax:

    array_max(num_collection)

  • Gets the max value of the non-null and non-missing comparable items in the given collection.

  • Arguments:

    • num_collection could be:

      • an array or multiset,

      • or, a null value,

      • or, a missing value.

  • Return Value:

    • the max value of non-null and non-missing numbers in the given collection. The returning type is decided by the item type with the highest order in the type promotion order (tinyint-> smallint->integer->bigint->float->double) among numeric items.

    • null is returned if the input is null or missing,

    • null is returned if the given collection does not contain any non-null and non-missing items,

    • multiple incomparable items in the input array or multiset will cause a type error,

    • any other non-array and non-multiset input value will cause a type error.

  • Example:

    array_max( [1.2, 2.3, 3.4, 0, null, missing] );

  • The expected result is:

    3.4

coll_count

  • Syntax:

    coll_count(collection)

  • Gets the number of items in the given collection.

  • Arguments:

    • collection could be:

      • an array or multiset containing the items to be counted,

      • or a null value,

      • or a missing value.

  • Return Value:

    • a bigint value representing the number of items in the given collection,

    • null is returned if the input is null or missing.

  • Example:

    coll_count( [1, 2, null, missing] );

  • The expected result is:

    4

coll_avg

  • Syntax:

    coll_avg(num_collection)

  • Gets the average value of the numeric items in the given collection.

  • Arguments:

    • num_collection could be:

      • an array or multiset containing numeric values, nulls or missings,

      • or, a null value,

      • or, a missing value.

  • Return Value:

    • a double value representing the average of the numbers in the given collection,

    • null is returned if the input is null or missing,

    • null is returned if there is a null or missing in the input collection,

    • any other non-numeric value in the input collection will cause a type error.

  • Example:

    coll_avg( [100, 200, 300] );

  • The expected result is:

    [ 200.0 ]

coll_sum

  • Syntax:

    coll_sum(num_collection)

  • Gets the sum of the items in the given collection.

  • Arguments:

    • num_collection could be:

      • an array or multiset containing numeric values, nulls or missings,

      • or, a null value,

      • or, a missing value.

  • Return Value:

    • the sum of the numbers in the given collection. The returning type is decided by the item type with the highest order in the numeric type promotion order (tinyint-> smallint->integer->bigint->float->double) among items.

    • null is returned if the input is null or missing,

    • null is returned if there is a null or missing in the input collection,

    • any other non-numeric value in the input collection will cause a type error.

  • Example:

    coll_sum( [100, 200, 300] );

  • The expected result is:

    600

array_min

  • Syntax:

    coll_min(num_collection)

  • Gets the min value of comparable items in the given collection.

  • Arguments:

    • num_collection could be:

      • an array or multiset,

      • or, a null value,

      • or, a missing value.

  • Return Value:

    • the min value of the given collection. The returning type is decided by the item type with the highest order in the type promotion order (tinyint-> smallint->integer->bigint->float->double) among numeric items.

    • null is returned if the input is null or missing,

    • null is returned if there is a null or missing in the input collection,

    • multiple incomparable items in the input array or multiset will cause a type error,

    • any other non-array and non-multiset input value will cause a type error.

  • Example:

    coll_min( [10.2, 100, 5] );

  • The expected result is:

    5.0

array_max

  • Syntax:

    coll_max(num_collection)

  • Gets the max value of numeric items in the given collection.

  • Arguments:

    • num_collection could be:

      • an array or multiset,

      • or, a null value,

      • or, a missing value.

  • Return Value:

    • The max value of the given collection. The returning type is decided by the item type with the highest order in the type promotion order (tinyint-> smallint->integer->bigint->float->double) among numeric items.

    • null is returned if the input is null or missing,

    • null is returned if there is a null or missing in the input collection,

    • multiple incomparable items in the input array or multiset will cause a type error,

    • any other non-array and non-multiset input value will cause a type error.

  • Example:

    coll_max( [10.2, 100, 5] );

  • The expected result is:

    100.0

Comparison Functions

greatest

  • Syntax:

    greatest(numeric_value1, numeric_value2, ...)

  • Computes the greatest value among arguments.

  • Arguments:

    • numeric_value1: a tinyint/smallint/integer/bigint/float/double value,

    • numeric_value2: a tinyint/smallint/integer/bigint/float/double value,

    • …​.

  • Return Value:

    • the greatest values among arguments. The returning type is decided by the item type with the highest order in the numeric type promotion order (tinyint-> smallint->integer->bigint->float->double) among items.

    • null if any argument is a missing value or null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": greatest(1, 2, 3), "v2": greatest(float("0.5"), double("-0.5"), 5000) };

  • The expected result is:

    { "v1": 3, "v2": 5000.0 }

least

  • Syntax:

    least(numeric_value1, numeric_value2, ...)

  • Computes the least value among arguments.

  • Arguments:

    • numeric_value1: a tinyint/smallint/integer/bigint/float/double value,

    • numeric_value2: a tinyint/smallint/integer/bigint/float/double value,

    • …​.

  • Return Value:

    • the least values among arguments. The returning type is decided by the item type with the highest order in the numeric type promotion order (tinyint-> smallint->integer->bigint->float->double) among items.

    • null if any argument is a missing value or null value,

    • any other non-numeric input value will cause a type error.

  • Example:

    { "v1": least(1, 2, 3), "v2": least(float("0.5"), double("-0.5"), 5000) };

  • The expected result is:

    { "v1": 1, "v2": -0.5 }

Type Functions

is_array

  • Syntax:

    is_array(expr)

  • Checks whether the given expression is evaluated to be an array value.

  • Arguments:

    • expr : an expression (any type is allowed).

  • Return Value:

    • a boolean on whether the argument is an array value or not,

    • a missing if the argument is a missing value,

    • a null if the argument is a null value.

  • Example:

    {
  "a": is_array(true),
  "b": is_array(false),
  "c": isarray(null),
  "d": isarray(missing),
  "e": isarray("d"),
  "f": isarray(4.0),
  "g": isarray(5),
  "h": isarray(["1", 2]),
  "i": isarray({"a":1})
};

  • The expected result is:

    { "a": false, "b": false, "c": null, "e": false, "f": false, "g": false, "h": true, "i": false }

The function has an alias isarray.

is_atomic (is_atom)

  • Syntax:

    is_atomic(expr)

  • Checks whether the given expression is evaluated to be a value of a primitive type.

  • Arguments:

    • expr : an expression (any type is allowed).

  • Return Value:

    • a boolean on whether the argument is a primitive type or not,

    • a missing if the argument is a missing value,

    • a null if the argument is a null value.

  • Example:

    {
  "a": is_atomic(true),
  "b": is_atomic(false),
  "c": isatomic(null),
  "d": isatomic(missing),
  "e": isatomic("d"),
  "f": isatom(4.0),
  "g": isatom(5),
  "h": isatom(["1", 2]),
  "i": isatom({"a":1})
};

  • The expected result is:

    { "a": true, "b": true, "c": null, "e": true, "f": true, "g": true, "h": false, "i": false }

The function has three aliases: isatomic, is_atom, and isatom.

is_boolean (is_bool)

  • Syntax:

    is_boolean(expr)

  • Checks whether the given expression is evaluated to be a boolean value.

  • Arguments:

    • expr : an expression (any type is allowed).

  • Return Value:

    • a boolean on whether the argument is a boolean value or not,

    • a missing if the argument is a missing value,

    • a null if the argument is a null value.

  • Example:

    {
  "a": isboolean(true),
  "b": isboolean(false),
  "c": is_boolean(null),
  "d": is_boolean(missing),
  "e": isbool("d"),
  "f": isbool(4.0),
  "g": isbool(5),
  "h": isbool(["1", 2]),
  "i": isbool({"a":1})
};

  • The expected result is:

    { "a": true, "b": true, "c": null, "e": false, "f": false, "g": false, "h": false, "i": false }

The function has three aliases: isboolean, is_bool, and isbool.

is_number (is_num)

  • Syntax:

    is_number(expr)

  • Checks whether the given expression is evaluated to be a numeric value.

  • Arguments:

    • expr : an expression (any type is allowed).

  • Return Value:

    • a boolean on whether the argument is a smallint/tinyint/integer/bigint/float/double value or not,

    • a missing if the argument is a missing value,

    • a null if the argument is a null value.

  • Example:

    {
  "a": is_number(true),
  "b": is_number(false),
  "c": isnumber(null),
  "d": isnumber(missing),
  "e": isnumber("d"),
  "f": isnum(4.0),
  "g": isnum(5),
  "h": isnum(["1", 2]),
  "i": isnum({"a":1})
};

  • The expected result is:

    { "a": false, "b": false, "c": null, "e": false, "f": true, "g": true, "h": false, "i": false }

The function has three aliases: isnumber, is_num, and isnum.

is_object (is_obj)

  • Syntax:

    is_object(expr)

  • Checks whether the given expression is evaluated to be a object value.

  • Arguments:

    • expr : an expression (any type is allowed).

  • Return Value:

    • a boolean on whether the argument is a object value or not,

    • a missing if the argument is a missing value,

    • a null if the argument is a null value.

  • Example:

    {
  "a": is_object(true),
  "b": is_object(false),
  "c": isobject(null),
  "d": isobject(missing),
  "e": isobj("d"),
  "f": isobj(4.0),
  "g": isobj(5),
  "h": isobj(["1", 2]),
  "i": isobj({"a":1})
};

  • The expected result is:

    { "a": false, "b": false, "c": null, "e": false, "f": false, "g": false, "h": false, "i": true }

The function has three aliases: isobject, is_obj, and isobj.

is_string (is_str)

  • Syntax:

    is_string(expr)

  • Checks whether the given expression is evaluated to be a string value.

  • Arguments:

    • expr : an expression (any type is allowed).

  • Return Value:

    • a boolean on whether the argument is a string value or not,

    • a missing if the argument is a missing value,

    • a null if the argument is a null value.

  • Example:

    {
  "a": is_string(true),
  "b": isstring(false),
  "c": isstring(null),
  "d": isstr(missing),
  "e": isstr("d"),
  "f": isstr(4.0),
  "g": isstr(5),
  "h": isstr(["1", 2]),
  "i": isstr({"a":1})
};

  • The expected result is:

    { "a": false, "b": false, "c": null, "e": true, "f": false, "g": false, "h": false, "i": false }

The function has three aliases: isstring, is_str, and isstr.

is_null

  • Syntax:

    is_null(expr)

  • Checks whether the given expression is evaluated to be a null value.

  • Arguments:

    • expr : an expression (any type is allowed).

  • Return Value:

    • a boolean on whether the variable is a null or not,

    • a missing if the input is missing.

  • Example:

    { "v1": is_null(null), "v2": is_null(1), "v3": is_null(missing) };

  • The expected result is:

    { "v1": true, "v2": false }

The function has an alias isnull.

is_missing

  • Syntax:

    is_missing(expr)

  • Checks whether the given expression is evaluated to be a missing value.

  • Arguments:

    • expr : an expression (any type is allowed).

  • Return Value:

    • a boolean on whether the variable is a missing or not.

  • Example:

    { "v1": is_missing(null), "v2": is_missing(1), "v3": is_missing(missing) };

  • The expected result is:

    { "v1": false, "v2": false, "v3": true }

The function has an alias ismissing.

is_unknown

  • Syntax:

    is_unknown(expr)

  • Checks whether the given variable is a null value or a missing value.

  • Arguments:

    • expr : an expression (any type is allowed).

  • Return Value:

    • a boolean on whether the variable is a null/missing value (true) or not (`false).

  • Example:

    { "v1": is_unknown(null), "v2": is_unknown(1), "v3": is_unknown(missing) };

  • The expected result is:

    { "v1": true, "v2": false, "v3": true }

The function has an alias isunknown.

to_array

  • Syntax:

    to_array(expr)

  • Converts input value to an array value

  • Arguments:

    • expr : an expression

  • Return Value:

    • if the argument is missing then missing is returned

    • if the argument is null then null is returned

    • if the argument is of array type then it is returned as is

    • if the argument is of multiset type then it is returned as an array with elements in an undefined order

    • otherwise an array containing the input expression as its single item is returned

  • Example:

    {
  "v1": to_array("asterix"),
  "v2": to_array(["asterix"]),
};

  • The expected result is:

    { "v1": ["asterix"], "v2": ["asterix"] }

The function has an alias toarray.

to_atomic (to_atom)

  • Syntax:

    to_atomic(expr)

  • Converts input value to a primitive value

  • Arguments:

    • expr : an expression

  • Return Value:

    • if the argument is missing then missing is returned

    • if the argument is null then null is returned

    • if the argument is of primitive type then it is returned as is

    • if the argument is of array or multiset type and has only one element then the result of invoking to_atomic() on that element is returned

    • if the argument is of object type and has only one field then the result of invoking to_atomic() on the value of that field is returned

    • otherwise null is returned

  • Example:

    {
  "v1": to_atomic("asterix"),
  "v2": to_atomic(["asterix"]),
  "v3": to_atomic([0, 1]),
  "v4": to_atomic({"value": "asterix"}),
  "v5": to_number({"x": 1, "y": 2})
};

  • The expected result is:

    { "v1": "asterix", "v2": "asterix", "v3": null, "v4": "asterix", "v5": null }

The function has three aliases: toatomic, to_atom, and toatom.

to_boolean (to_bool)

  • Syntax:

    to_boolean(expr)

  • Converts input value to a boolean value

  • Arguments:

    • expr : an expression

  • Return Value:

    • if the argument is missing then missing is returned

    • if the argument is null then null is returned

    • if the argument is of boolean type then it is returned as is

    • if the argument is of numeric type then false is returned if it is 0 or NaN, otherwise true

    • if the argument is of string type then false is returned if it’s empty, otherwise true

    • if the argument is of array or multiset type then false is returned if it’s size is 0, otherwise true

    • if the argument is of object type then false is returned if it has no fields, otherwise true

    • type error is raised for all other input types

  • Example:

    {
  "v1": to_boolean(0),
  "v2": to_boolean(1),
  "v3": to_boolean(""),
  "v4": to_boolean("asterix")
};

  • The expected result is:

    { "v1": false, "v2": true, "v3": false, "v4": true }

The function has three aliases: toboolean, to_bool, and tobool.

to_bigint

  • Syntax:

    to_bigint(expr)

  • Converts input value to an integer value

  • Arguments:

    • expr : an expression

  • Return Value:

    • if the argument is missing then missing is returned

    • if the argument is null then null is returned

    • if the argument is of boolean type then 1 is returned if it is true, 0 if it is false

    • if the argument is of numeric integer type then it is returned as the same value of bigint type

    • if the argument is of numeric float/double type then it is converted to bigint type

    • if the argument is of string type and can be parsed as integer then that integer value is returned, otherwise null is returned

    • if the argument is of array/multiset/object type then null is returned

    • type error is raised for all other input types

  • Example:

    {
  "v1": to_bigint(false),
  "v2": to_bigint(true),
  "v3": to_bigint(10),
  "v4": to_bigint(float("1e100")),
  "v5": to_bigint(double("1e1000")),
  "v6": to_bigint("20")
};

  • The expected result is:

    { "v1": 0, "v2": 1, "v3": 10, "v4": 9223372036854775807, "v5": 9223372036854775807, "v6": 20 }

The function has an alias tobigint.

to_double

  • Syntax:

    to_double(expr)

  • Converts input value to a double value

  • Arguments:

    • expr : an expression

  • Return Value:

    • if the argument is missing then missing is returned

    • if the argument is null then null is returned

    • if the argument is of boolean type then 1.0 is returned if it is true, 0.0 if it is false

    • if the argument is of numeric type then it is returned as the value of double type

    • if the argument is of string type and can be parsed as double then that double value is returned, otherwise null is returned

    • if the argument is of array/multiset/object type then null is returned

    • type error is raised for all other input types

  • Example:

    {
  "v1": to_double(false),
  "v2": to_double(true),
  "v3": to_double(10),
  "v4": to_double(11.5),
  "v5": to_double("12.5")
};

  • The expected result is:

    { "v1": 0.0, "v2": 1.0, "v3": 10.0, "v4": 11.5, "v5": 12.5 }

The function has an alias todouble.

to_number (to_num)

  • Syntax:

    to_number(expr)

  • Converts input value to a numeric value

  • Arguments:

    • expr : an expression

  • Return Value:

    • if the argument is missing then missing is returned

    • if the argument is null then null is returned

    • if the argument is of numeric type then it is returned as is

    • if the argument is of boolean type then 1 is returned if it is true, 0 if it is false

    • if the argument is of string type and can be parsed as bigint then that bigint value is returned, otherwise if it can be parsed as double then that double value is returned, otherwise null is returned

    • if the argument is of array/multiset/object type then null is returned

    • type error is raised for all other input types

  • Example:

    {
  "v1": to_number(false),
  "v2": to_number(true),
  "v3": to_number(10),
  "v4": to_number(11.5),
  "v5": to_number("12.5")
};

  • The expected result is:

    { "v1": 0, "v2": 1, "v3": 10, "v4": 11.5, "v5": 12.5 }

The function has three aliases: tonumber, to_num, and tonum.

to_object (to_obj)

  • Syntax:

    to_object(expr)

  • Converts input value to an object value

  • Arguments:

    • expr : an expression

  • Return Value:

    • if the argument is missing then missing is returned

    • if the argument is null then null is returned

    • if the argument is of object type then it is returned as is

    • otherwise an empty object is returned

  • Example:

    {
  "v1": to_object({"value": "asterix"}),
  "v2": to_object("asterix")
};

  • The expected result is:

    { "v1": {"value": "asterix"}, "v2": {} }

The function has three aliases: toobject, to_obj, and toobj.

to_string (to_str)

  • Syntax:

    to_string(expr)

  • Converts input value to a string value

  • Arguments:

    • expr : an expression

  • Return Value:

    • if the argument is missing then missing is returned

    • if the argument is null then null is returned

    • if the argument is of boolean type then "true" is returned if it is true, "false" if it is false

    • if the argument is of numeric type then its string representation is returned

    • if the argument is of string type then it is returned as is

    • if the argument is of array/multiset/object type then null is returned

    • type error is raised for all other input types

  • Example:

    {
  "v1": to_string(false),
  "v2": to_string(true),
  "v3": to_string(10),
  "v4": to_string(11.5),
  "v5": to_string("asterix")
};

  • The expected result is:

    { "v1": "false", "v2": "true", "v3": "10", "v4": "11.5", "v5": "asterix" }

The function has three aliases: tostring, to_str, and tostr.

Conditional Functions

if_null (ifnull)

  • Syntax:

    if_null(expression1, expression2, ... expressionN)

  • Finds first argument which value is not null and returns that value

  • Arguments:

    • expressionI : an expression (any type is allowed).

  • Return Value:

    • a null if all arguments evaluate to null or no arguments specified

    • a value of the first non-null argument otherwise

  • Example:

    {
    "a": if_null(),
    "b": if_null(null),
    "c": if_null(null, "analytics"),
    "d": is_missing(if_null(missing))
};

  • The expected result is:

    { "a": null, "b": null, "c": "analytics", "d": true }

The function has an alias ifnull.

if_missing (ifmissing)

  • Syntax:

    if_missing(expression1, expression2, ... expressionN)

  • Finds first argument which value is not missing and returns that value

  • Arguments:

    • expressionI : an expression (any type is allowed).

  • Return Value:

    • a null if all arguments evaluate to missing or no arguments specified

    • a value of the first non-missing argument otherwise

  • Example:

    {
    "a": if_missing(),
    "b": if_missing(missing),
    "c": if_missing(missing, "analytics"),
    "d": if_missing(null, "analytics")
};

  • The expected result is:

    { "a": null, "b": null, "c": "analytics", "d": null }

The function has an alias ifmissing.

if_missing_or_null (ifmissingornull)

  • Syntax:

    if_missing_or_null(expression1, expression2, ... expressionN)

  • Finds first argument which value is not null or missing and returns that value

  • Arguments:

    • expressionI : an expression (any type is allowed).

  • Return Value:

    • a null if all arguments evaluate to either null or missing, or no arguments specified

    • a value of the first non-null, non-missing argument otherwise

  • Example:

    {
    "a": if_missing_or_null(),
    "b": if_missing_or_null(null, missing),
    "c": if_missing_or_null(null, missing, "analytics")
};

  • The expected result is:

    { "a": null, "b": null, "c": "analytics" }

The function has an alias ifmissingornull.

if_inf (ifinf)

  • Syntax:

    if_inf(expression1, expression2, ... expressionN)

  • Finds first argument which is a non-infinite (INF or-INF) number

  • Arguments:

    • expressionI : an expression (any type is allowed).

  • Return Value:

    • a missing if missing argument was encountered before the first non-infinite number argument

    • a null if null argument or any other non-number argument was encountered before the first non-infinite number argument

    • the first non-infinite number argument otherwise

  • Example:

    {
    "a": is_null(if_inf(null)),
    "b": is_missing(if_inf(missing)),
    "c": is_null(if_inf(double("INF"))),
    "d": if_inf(1, null, missing) ],
    "e": is_null(if_inf(null, missing, 1)) ],
    "f": is_missing(if_inf(missing, null, 1)) ],
    "g": if_inf(float("INF"), 1) ],
    "h": to_string(if_inf(float("INF"), double("NaN"), 1)) ]
};

  • The expected result is:

    { "a": true, "b": true, "c": true, "d": 1, "e": true, "f": true, "g": 1, "h": "NaN" }

The function has an alias ifinf.

if_nan (ifnan)

  • Syntax:

    if_nan(expression1, expression2, ... expressionN)

  • Finds first argument which is a non-NaN number

  • Arguments:

    • expressionI : an expression (any type is allowed).

  • Return Value:

    • a missing if missing argument was encountered before the first non-NaN number argument

    • a null if null argument or any other non-number argument was encountered before the first non-NaN number argument

    • the first non-NaN number argument otherwise

  • Example:

    {
    "a": is_null(if_nan(null)),
    "b": is_missing(if_nan(missing)),
    "c": is_null(if_nan(double("NaN"))),
    "d": if_nan(1, null, missing) ],
    "e": is_null(if_nan(null, missing, 1)) ],
    "f": is_missing(if_nan(missing, null, 1)) ],
    "g": if_nan(float("NaN"), 1) ],
    "h": to_string(if_nan(float("NaN"), double("INF"), 1)) ]
};

  • The expected result is:

    { "a": true, "b": true, "c": true, "d": 1, "e": true, "f": true, "g": 1, "h": "INF" }

The function has an alias ifnan.

if_nan_or_inf (ifnanorinf)

  • Syntax:

    if_nan_or_inf(expression1, expression2, ... expressionN)

  • Finds first argument which is a non-infinite (INF or-INF) and non-NaN number

  • Arguments:

    • expressionI : an expression (any type is allowed).

  • Return Value:

    • a missing if missing argument was encountered before the first non-infinite and non-NaN number argument

    • a null if null argument or any other non-number argument was encountered before the first non-infinite and non-NaN number argument

    • the first non-infinite and non-NaN number argument otherwise

  • Example:

    {
    "a": is_null(if_nan_or_inf(null)),
    "b": is_missing(if_nan_or_inf(missing)),
    "c": is_null(if_nan_or_inf(double("NaN"), double("INF"))),
    "d": if_nan_or_inf(1, null, missing) ],
    "e": is_null(if_nan_or_inf(null, missing, 1)) ],
    "f": is_missing(if_nan_or_inf(missing, null, 1)) ],
    "g": if_nan_or_inf(float("NaN"), float("INF"), 1) ],
};

  • The expected result is:

    { "a": true, "b": true, "c": true, "d": 1, "e": true, "f": true, "g": 1 }

The function has an alias ifnanorinf.

Environment and Identifier Functions

meta

  • Syntax:

    meta(expr)
meta()

  • Return a metadata object for a stored document.

  • Arguments:

    • expr : an expression returning a stored document

    • none, if the stored document can be determined from the context

  • Return Value:

    • a metadata object containing fields id, vbid, seq, cas, and flags.

uuid

  • Syntax:

    uuid()

  • Generates a uuid.

  • Arguments:

    • none

  • Return Value:

    • a generated, random uuid.