minQL Function Reference

Overview

This page describes the regular (i.e., non-method) functions available in minQL in the scope of property expressions.

minQL supports scalar (i.e., not table, aggregate, or system) functions from Snowflake, which are similar to the functions in other database systems, as well as a few special-purpose functions not available in SQL.

Each of the functions from Snowflake are converted to lowerCamelCase, so, for example, REGEXP_SUBSTR would be regexSubstr in minQL.

Additionally, certain database expressions that don’t use a functional syntax in SQL are implemented with a functional syntax in minQL. For example, in SQL, you would say value IS NOT NULL, whereas the equivalent minQL is isNotNull(value).

Warning: minQL does not currently validate argument types or counts for most functions, so you can write a minQL expression that shows up as valid in the user interface and end up getting a server error.

Dot Syntax

In minQL, all functions can be called with a dot syntax to pass the expression on the left side of the dot as the first argument to the function. For example add(1, 2) can be written as (1).add(2). All of the functions are documented here with the regular non-dot syntax

Special Non-Snowflake Functions

The following functions are not available in Snowflake, or have different semantics than the corresponding Snowflake function.

  • flatten(<list>) - This function splits a list and returns each value from the list in a separate row. Note that this will duplicate rows or omit rows without items in the list, which may affect total results. For example, looking at dev days by flattened ticket labels will count dev days multiple times for each label, and omit dev days not associated with a ticket having at least one label.
  • reportStartTime() - Retrieves the starting timestamp of the reporting period. This will be midnight UTC on the report start date.
  • reportEndTime() - Retrieves the ending timestamp of the reporting period. This will be midnight UTC on the day following the report end date, which may be a time in the future if the report includes the current day.

List Iteration Functions

minQL supports several list iteration functions that are not available in Snowflake. These functions have the same behavior as JavaScript Array methods.

All of these functions return null if the list itself is null, but have different behavior for empty lists.

  • every(<list>, <expression>) - Returns true if the list is empty or if the expression is true for every item in the list.
  • some(<list>, <expression>) - Returns true if the expression is true for at least one item in the list, and false if the list is empty.
  • map(<list>, <expression>) - Replaces each value in the list with the result of the expression.
  • filterList(<list>, <expression>) - Returns a new list only having items for which the expression is true. This function is equivalent to the JavaScript filter function, but has a different name to avoid confusion with the minQL filter method.
  • find(<list>, <expression>) - Returns the first item in the list where the expression is true, or null if the expression is never true or the list is empty.
  • findLast(<list>, <expression>) - Returns the last item in the list where the expression is true, or null if the expression is never true or the list is empty.
  • findIndex(<list>, <expression>) - Returns the 0-based index of the first item in the list where the expression is true, or -1 if the expression is never true or the list is empty.
  • findLastIndex(<list>, <expression>) - Returns the 0-based index of the last item in the list where the expression is true, or -1 if the expression is never true or the list is empty.

The <expression> argument to each list function contains the following additional scope variables that you can use in list iteration:

  • currentValue - The current item in the list
  • currentIndex - The index of the current item in the list
  • currentList - The whole list object you are iterating over

Basic Operators

minQL supports basic operators in expressions, which correspond to functions listed below as follows:

  • + - add
  • - - sub
  • * - mult
  • / - div
  • % - mod
  • == - eq
  • != - neq
  • === - isNotDistinctFrom
  • !== - isDistinctFrom
  • > - gt
  • >= - gte
  • < - lt
  • <= - lte
  • && - and
  • || - or

minQL also supports unary operators:

  • + - Equivalent to 0 + <arg>
  • - - Equivalent to 0 - <arg>
  • ! - Equivalent to not function

minQL also allows array construction:

  • [<arg1>, ..., <argN>] - Equivalent to arrayConstruct(<arg1>, ..., <argN>)

Arithmetic Operators

The following arithmetic operators are available as functions. See Snowflake's Arithmetic Operator Documenation for more information.

All arithmetic numbers require numeric or date arguments and return a numeric or date result, and they have the same semantics regarding NULL values as the Snowflake operators (i.e., the result will be null if any arguments are null).

  • add(<arg1>, <arg2>[, ..., <argN>]) - Adds two or more values together (+)
  • sub(<arg1>, <arg2>) - Subtracts two values (-)
  • mult(<arg1>, <arg2>[, ..., <argN>]) - Multiplies two or more values together (*)
  • div(<arg1>, <arg2>) - Divides two values (/)
  • mod(<arg1>, <arg2>) - Computes the modulus of two values (%)

Comparison Operators

The following comparison operators are available as functions. See Snowflake's Comparison Operator Documenation for more information.

The argument types accepted by the comparison operators are the same as the types allowed by the corresponding Snowflake operator, and they have the same semantics regarding NULL values (i.e., the result will be null and thus falsey if any arguments are null).

  • eq(<arg1>, <arg2>) - Tests if two values are equal (=)
  • neq(<arg1>, <arg2>) - Tests if two values are not equal (!= or <>)
  • gt(<arg1>, <arg2>) - Tests if <arg1> is greater than <arg2> (>)
  • gte(<arg1>, <arg2>) - Tests if <arg1> is greater than or equal to <arg2> (>=)
  • lt(<arg1>, <arg2>) - Tests if <arg1> is less than <arg2> (<)
  • lte(<arg1>, <arg2>) - Tests if <arg1> is less than or equal to <arg2> (<=)

Logical/Boolean Operators

The following logical operators are available as functions. See Snowflake's Logical Operator Documenation for more information.

The argument types accepted by the comparison operators are the same as the types allowed by the corresponding Snowflake operator, and they have the same semantics regarding NULL values.

  • and(<arg1>, <arg2>[, ..., <argN>]) - Tests if all of the arguments are true (and)
  • or(<arg1>, <arg2>[, ..., <argN>]) - Tests if any of the arguments are true (or)
  • not(<arg1>) - Tests if the value is not true (not)

Conditional Expressions

This section describes the conditional expressions that are available as minQL functions. See Snowflake's Conditional Expression Documentation for more information.

  • between(<expr>, <lower_bound>, <upper_bound>) - Alias of [ NOT ] BETWEEN
  • notBetween(<expr>, <lower_bound>, <upper_bound>) - Alias of [ NOT ] BETWEEN. This is the NOT variant.
  • booland(<expr1>, <expr2>) - Alias of BOOLAND
  • boolnot(<expr>) - Alias of BOOLNOT
  • boolor(<expr1>, <expr2>) - Alias of BOOLOR
  • boolxor(<expr1>, <expr2>) - Alias of BOOLXOR
  • case(<condition1>, <result1>[, ..., <conditionN>, <resultN>][, <elseResult>]) - Alias of CASE. This is the CASE WHEN ... variant of the function. The presence of an else value is determined based on even or odd number of arguments.
  • caseValue(<expr>, <value1>, <result1>[, ..., <valueN>, <resultN>][, <elseResult>]) - Alias of CASE. This is the CASE <expr> WHEN ... variant of the function. The presence of an else value is determined based on even or odd number of arguments.
  • coalesce(<expr1>, <expr2>[, ..., <exprN>]) - Alias of COALESCE
  • decode(<expr>, <search1>, <result1>[, ..., <searchN>, <resultN>][, <default>]) - Alias of DECODE
  • equalNull(<expr1>, <expr2>) - Alias of EQUAL_NULL
  • greatest(<expr1>[, ..., <exprN>]) - Alias of GREATEST
  • iff(<condition>, <expr1>, <expr2>) - Alias of IFF
  • ifnull(<expr1>, <expr2>) - Alias of IFNULL
  • in(<expr1>[, ..., <exprN>]) - Alias of [ NOT ] IN
  • notIn(<expr1>[, ..., <exprN>]) - Alias of [ NOT ] IN. This is the NOT variant.
  • isDistinctFrom(<expr1>, <expr2>) - Alias of IS [ NOT ] DISTINCT FROM
  • isNotDistinctFrom(<expr1>, <expr2>) - Alias of IS [ NOT ] DISTINCT FROM. This is the NOT variant.
  • isNull(<expr>) - Alias of IS [ NOT ] NULL
  • isNotNull(<expr>) - Alias of IS [ NOT ] NULL. This is the NOT variant.
  • isNullValue(<variant_expr>) - Alias of IS_NULL_VALUE
  • least(<expr1>[, ..., <exprN>]) - Alias of LEAST
  • nullif(<expr1>, <expr2>) - Alias of NULLIF
  • nullifzero(<expr>) - Alias of NULLIFZERO
  • nvl(<expr1>, <expr2>) - Alias of NVL
  • nvl2(<expr1>, <expr2>, <expr3>) - Alias of NVL2
  • regrValx(<x>, <y>) - Alias of REGR_VALX
  • regrValy(<x>, <y>) - Alias of REGR_VALY
  • zeroifnull(<expr>) - Alias of ZEROIFNULL

Object Extraction

minQL supports the following object extraction functions:

  • getArray(<arg>, <index>) - Alias of GET. This function will also extract a string property from an object or map despite its name.

Type Conversion Functions

minQL supports the following type conversion functions. The error-throwing version of Snowflake's type-casting functions are not allowed, and instead these functions will output a NULL if conversion fails (conversion to string will never fail due to conversion issues and only return null if the input is null).

See Snowflake's Conversion Function Documenation for more details on function parameters and semantics. Timestamps including the time zone are also not allowed.

Date & Time Functions

minQL supports the following date and time functions. See the Snowflake Date & Time Function Documentation for more details about the arguments and semantics of these functions.

Date & Time Construction

Date & Time Extraction

Date & Time Addition/Subtraction

Date & Time Truncation

String Functions

minQL supports the following string functions. See the Snowflake String Function Documentation for more details about the arguments and semantics of these functions.

General Manipulation

Case Conversion

  • initcap(...) - Alias of INITCAP
  • lower(...) - Alias of LOWER
  • upper(...) - Alias of UPPER

Regular Expression

Other Matching/Comparison

  • stringContains(...) - Alias of CONTAINS
  • editdistance(...) - Alias of EDITDISTANCE
  • endswith(...) - Alias of ENDSWITH
  • ilike(...) - Alias of [ NOT ] ILIKE
  • notIlike(...) - Alias of [ NOT ] ILIKE, This is the NOT syntax.
  • ilikeAny(...) - Alias of ILIKE_ANY
  • ilikeAnyEscape(...) - Alias of ILIKE_ANY. In this version, the last parameter is treated as the optional escape parameter.
  • jarowinklerSimilarity(...) - Alias of JAROWINKLER_SIMILARITY
  • left(...) - Alias of LEFT
  • like(...) - Alias of [ NOT ] LIKE
  • notLike(...) - Alias of [ NOT ] LIKE. This is the NOT syntax.
  • likeAll(...) - Alias of LIKE_ALL
  • likeAllEscape(...) - Alias of LIKE_ALL. In this version, the last parameter is treated as the optional escape parameter.
  • likeAny(...) - Alias of LIKE_ANY
  • likeAnyEscape(...) - Alias of LIKE_ANY. In this version, the last parameter is treated as the optional escape parameter.
  • position(...) - Alias of POSITION
  • replace(...) - Alias of REPLACE
  • right(...) - Alias of RIGHT
  • startswith(...) - Alias of STARTSWITH
  • substr(...) - Alias of SUBSTR

Compression/Decompression

Encoding/Decoding

Cryptographic/Checksum

Hash

  • hash(...) - Alias of HASH

Collation

Array Functions

minQL currently supports one array function:

  • arrayConstruct(<arg1>, ..., <argN>) - Builds an array of zero or more values. Alias of ARRAY_CONSTRUCT