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.

toChar(...) - Alias of TO_CHAR
toVarchar(...) - Alias of TO_VARCHAR
tryToInt(...) - Alias of TRY_TO_NUMBER
tryToNumber(...) - Alias of TRY_TO_NUMBER
tryToDouble(...) - Alias of TRY_TO_DOUBLE
tryToBoolean(...) - Alias of TRY_TO_BOOLEAN
tryToDate(...) - Alias of TRY_TO_DATE
tryToTime(...) - Alias of TRY_TO_TIME
tryToTimestampNtz(...) - Alias of TRY_TO_TIMESTAMP_NTZ
toArray(...) - Alias of TO_ARRAY
toObject(...) - Alias of TO_OBJECT
toVariant(...) - Alias of TO_VARIANT

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

dateFromParts(...) - Alias of DATE_FROM_PARTS
timeFromParts(...) - Alias of TIME_FROM_PARTS
timestampFromParts(...) - Alias of TIMESTAMP_FROM_PARTS

Date & Time Extraction

datePart(...) - Alias of DATE_PART
dayname(...) - Alias of DAYNAME
extract(...) - Alias of EXTRACT
hour(...) - Alias of HOUR
minute(...) - Alias of MINUTE
second(...) - Alias of SECOND
lastDay(...) - Alias of LAST_DAY
monthname(...) - Alias of MONTHNAME
nextDay(...) - Alias of NEXT_DAY
previousDay(...) - Alias of PREVIOUS_DAY
year(...) - Alias of YEAR
yearofweek(...) - Alias of YEAROFWEEK
yearofweekiso(...) - Alias of YEAROFWEEKISO
day(...) - Alias of DAY
dayofmonth(...) - Alias of DAYOFMONTH
dayofweek(...) - Alias of DAYOFWEEK
dayofweekiso(...) - Alias of DAYOFWEEKISO
dayofyear(...) - Alias of DAYOFYEAR
week(...) - Alias of WEEK
weekofyear(...) - Alias of WEEKOFYEAR
weekiso(...) - Alias of WEEKISO
month(...) - Alias of MONTH
quarter(...) - Alias of QUARTER

Date & Time Addition/Subtraction

addMonths(...) - Alias of ADD_MONTHS
dateadd(...) - Alias of DATEADD
datediff(...) - Alias of DATEDIFF
monthsBetween(...) - Alias of MONTHS_BETWEEN
timeadd(...) - Alias of TIMEADD
timediff(...) - Alias of TIMEDIFF
timestampadd(...) - Alias of TIMESTAMPADD
timestampdiff(...) - Alias of TIMESTAMPDIFF

Date & Time Truncation

dateTrunc(...) - Alias of DATE_TRUNC
timeSlice(...) - Alias of TIME_SLICE
trunc(...) - Alias of TRUNC

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

ascii(...) - Alias of ASCII
bitLength(...) - Alias of BIT_LENGTH
chr(...) - Alias of CHR
concat(...) - Alias of CONCAT or || operator
concatWs(...) - Alias of CONCAT_WS
insert(...) - Alias of INSERT
length(...) - Alias of LENGTH
lpad(...) - Alias of LPAD
ltrim(...) - Alias of LTRIM
octetLength(...) - Alias of OCTET_LENGTH
parseIp(...) - Alias of PARSE_IP
parseUrl(...) - Alias of PARSE_URL
repeat(...) - Alias of REPEAT
reverse(...) - Alias of REVERSE
rpad(...) - Alias of RPAD
rtrim(...) - Alias of RTRIM
rtrimmedLength(...) - Alias of RTRIMMED_LENGTH
soundex(...) - Alias of SOUNDEX
soundexP123(...) - Alias of SOUNDEX_P123
space(...) - Alias of SPACE
split(...) - Alias of SPLIT
splitPart(...) - Alias of SPLIT_PART
strtok(...) - Alias of STRTOK
strtokToArray(...) - Alias of STRTOK_TO_ARRAY
translate(...) - Alias of TRANSLATE
trim(...) - Alias of TRIM
unicode(...) - Alias of UNICODE
uuidString(...) - Alias of UUID_STRING

Case Conversion

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

Regular Expression

regexpCount(...) - Alias of REGEXP_COUNT
regexpInstr(...) - Alias of REGEXP_INSTR
regexpReplace(...) - Alias of REGEXP_REPLACE
regexpSubstr(...) - Alias of REGEXP_SUBSTR
regexpSubstrAll(...) - Alias of REGEXP_SUBSTR_ALL
rlike(...) - Alias of [ NOT ] RLIKE
notRlike(...) - Alias of [ NOT ] RLIKE. This is the NOT syntax.

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