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.
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
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.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 listcurrentIndex
- The index of the current item in the listcurrentList
- The whole list object you are iterating overminQL 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
functionminQL also allows array construction:
[<arg1>, ..., <argN>]
- Equivalent to arrayConstruct(<arg1>, ..., <argN>)
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 (%
)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>
(<=
)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
)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 ] BETWEENnotBetween(<expr>, <lower_bound>, <upper_bound>)
- Alias of [ NOT ] BETWEEN. This is the NOT
variant.booland(<expr1>, <expr2>)
- Alias of BOOLANDboolnot(<expr>)
- Alias of BOOLNOTboolor(<expr1>, <expr2>)
- Alias of BOOLORboolxor(<expr1>, <expr2>)
- Alias of BOOLXORcase(<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 COALESCEdecode(<expr>, <search1>, <result1>[, ..., <searchN>, <resultN>][, <default>])
- Alias of DECODEequalNull(<expr1>, <expr2>)
- Alias of EQUAL_NULLgreatest(<expr1>[, ..., <exprN>])
- Alias of GREATESTiff(<condition>, <expr1>, <expr2>)
- Alias of IFFifnull(<expr1>, <expr2>)
- Alias of IFNULLin(<expr1>[, ..., <exprN>])
- Alias of [ NOT ] INnotIn(<expr1>[, ..., <exprN>])
- Alias of [ NOT ] IN. This is the NOT
variant.isDistinctFrom(<expr1>, <expr2>)
- Alias of IS [ NOT ] DISTINCT FROMisNotDistinctFrom(<expr1>, <expr2>)
- Alias of IS [ NOT ] DISTINCT FROM. This is the NOT
variant.isNull(<expr>)
- Alias of IS [ NOT ] NULLisNotNull(<expr>)
- Alias of IS [ NOT ] NULL. This is the NOT
variant.isNullValue(<variant_expr>)
- Alias of IS_NULL_VALUEleast(<expr1>[, ..., <exprN>])
- Alias of LEASTnullif(<expr1>, <expr2>)
- Alias of NULLIFnullifzero(<expr>)
- Alias of NULLIFZEROnvl(<expr1>, <expr2>)
- Alias of NVLnvl2(<expr1>, <expr2>, <expr3>)
- Alias of NVL2regrValx(<x>, <y>)
- Alias of REGR_VALXregrValy(<x>, <y>)
- Alias of REGR_VALYzeroifnull(<expr>)
- Alias of ZEROIFNULLminQL 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.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_CHARtoVarchar(...)
- Alias of TO_VARCHARtryToInt(...)
- Alias of TRY_TO_NUMBERtryToNumber(...)
- Alias of TRY_TO_NUMBERtryToDouble(...)
- Alias of TRY_TO_DOUBLEtryToBoolean(...)
- Alias of TRY_TO_BOOLEANtryToDate(...)
- Alias of TRY_TO_DATEtryToTime(...)
- Alias of TRY_TO_TIMEtryToTimestampNtz(...)
- Alias of TRY_TO_TIMESTAMP_NTZtoArray(...)
- Alias of TO_ARRAYtoObject(...)
- Alias of TO_OBJECTtoVariant(...)
- Alias of TO_VARIANTminQL 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.
dateFromParts(...)
- Alias of DATE_FROM_PARTStimeFromParts(...)
- Alias of TIME_FROM_PARTStimestampFromParts(...)
- Alias of TIMESTAMP_FROM_PARTSdatePart(...)
- Alias of DATE_PARTdayname(...)
- Alias of DAYNAMEextract(...)
- Alias of EXTRACThour(...)
- Alias of HOURminute(...)
- Alias of MINUTEsecond(...)
- Alias of SECONDlastDay(...)
- Alias of LAST_DAYmonthname(...)
- Alias of MONTHNAMEnextDay(...)
- Alias of NEXT_DAYpreviousDay(...)
- Alias of PREVIOUS_DAYyear(...)
- Alias of YEARyearofweek(...)
- Alias of YEAROFWEEKyearofweekiso(...)
- Alias of YEAROFWEEKISOday(...)
- Alias of DAYdayofmonth(...)
- Alias of DAYOFMONTHdayofweek(...)
- Alias of DAYOFWEEKdayofweekiso(...)
- Alias of DAYOFWEEKISOdayofyear(...)
- Alias of DAYOFYEARweek(...)
- Alias of WEEKweekofyear(...)
- Alias of WEEKOFYEARweekiso(...)
- Alias of WEEKISOmonth(...)
- Alias of MONTHquarter(...)
- Alias of QUARTERaddMonths(...)
- Alias of ADD_MONTHSdateadd(...)
- Alias of DATEADDdatediff(...)
- Alias of DATEDIFFmonthsBetween(...)
- Alias of MONTHS_BETWEENtimeadd(...)
- Alias of TIMEADDtimediff(...)
- Alias of TIMEDIFFtimestampadd(...)
- Alias of TIMESTAMPADDtimestampdiff(...)
- Alias of TIMESTAMPDIFFdateTrunc(...)
- Alias of DATE_TRUNCtimeSlice(...)
- Alias of TIME_SLICEtrunc(...)
- Alias of TRUNCminQL supports the following string functions. See the Snowflake String Function Documentation for more details about the arguments and semantics of these functions.
ascii(...)
- Alias of ASCIIbitLength(...)
- Alias of BIT_LENGTHchr(...)
- Alias of CHRconcat(...)
- Alias of CONCAT or ||
operatorconcatWs(...)
- Alias of CONCAT_WSinsert(...)
- Alias of INSERTlength(...)
- Alias of LENGTHlpad(...)
- Alias of LPADltrim(...)
- Alias of LTRIMoctetLength(...)
- Alias of OCTET_LENGTHparseIp(...)
- Alias of PARSE_IPparseUrl(...)
- Alias of PARSE_URLrepeat(...)
- Alias of REPEATreverse(...)
- Alias of REVERSErpad(...)
- Alias of RPADrtrim(...)
- Alias of RTRIMrtrimmedLength(...)
- Alias of RTRIMMED_LENGTHsoundex(...)
- Alias of SOUNDEXsoundexP123(...)
- Alias of SOUNDEX_P123space(...)
- Alias of SPACEsplit(...)
- Alias of SPLITsplitPart(...)
- Alias of SPLIT_PARTstrtok(...)
- Alias of STRTOKstrtokToArray(...)
- Alias of STRTOK_TO_ARRAYtranslate(...)
- Alias of TRANSLATEtrim(...)
- Alias of TRIMunicode(...)
- Alias of UNICODEuuidString(...)
- Alias of UUID_STRINGregexpCount(...)
- Alias of REGEXP_COUNTregexpInstr(...)
- Alias of REGEXP_INSTRregexpReplace(...)
- Alias of REGEXP_REPLACEregexpSubstr(...)
- Alias of REGEXP_SUBSTRregexpSubstrAll(...)
- Alias of REGEXP_SUBSTR_ALLrlike(...)
- Alias of [ NOT ] RLIKEnotRlike(...)
- Alias of [ NOT ] RLIKE. This is the NOT syntax.stringContains(...)
- Alias of CONTAINSeditdistance(...)
- Alias of EDITDISTANCEendswith(...)
- Alias of ENDSWITHilike(...)
- Alias of [ NOT ] ILIKEnotIlike(...)
- Alias of [ NOT ] ILIKE, This is the NOT syntax.ilikeAny(...)
- Alias of ILIKE_ANYilikeAnyEscape(...)
- Alias of ILIKE_ANY. In this version, the last parameter is treated as the optional escape parameter.jarowinklerSimilarity(...)
- Alias of JAROWINKLER_SIMILARITYleft(...)
- Alias of LEFTlike(...)
- Alias of [ NOT ] LIKEnotLike(...)
- Alias of [ NOT ] LIKE. This is the NOT syntax.likeAll(...)
- Alias of LIKE_ALLlikeAllEscape(...)
- Alias of LIKE_ALL. In this version, the last parameter is treated as the optional escape parameter.likeAny(...)
- Alias of LIKE_ANYlikeAnyEscape(...)
- Alias of LIKE_ANY. In this version, the last parameter is treated as the optional escape parameter.position(...)
- Alias of POSITIONreplace(...)
- Alias of REPLACEright(...)
- Alias of RIGHTstartswith(...)
- Alias of STARTSWITHsubstr(...)
- Alias of SUBSTRcompress(...)
- Alias of COMPRESSdecompressBinary(...)
- Alias of DECOMPRESS_BINARYdecompressString(...)
- Alias of DECOMPRESS_STRINGbase64Encode(...)
- Alias of BASE64_ENCODEhexEncode(...)
- Alias of HEX_ENCODEtryBase64DecodeBinary(...)
- Alias of TRY_BASE64_DECODE_BINARYtryBase64DecodeString(...)
- Alias of TRY_BASE64_DECODE_STRINGtryHexDecodeBinary(...)
- Alias of TRY_HEX_DECODE_BINARYtryHexDecodeString(...)
- Alias of TRY_HEX_DECODE_STRINGmd5(...)
- Alias of MD5md5Binary(...)
- Alias of MD5_BINARYmd5NumberLower64(...)
- Alias of MD5_NUMBER_LOWER64md5NumberUpper64(...)
- Alias of MD5_NUMBER_UPPER64sha1(...)
- Alias of SHA1sha1Binary(...)
- Alias of SHA1_BINARYsha2(...)
- Alias of SHA2sha2Binary(...)
- Alias of SHA2_BINARYhash(...)
- Alias of HASHminQL currently supports one array function:
arrayConstruct(<arg1>, ..., <argN>)
- Builds an array of zero or more values. Alias of ARRAY_CONSTRUCT