MQL has several built-in functions for capabilities that don't neatly fit into concise syntax. Functions can operate on a variable amount of parameters and always evaluate to a value. The basic syntax for functions is <name>(<arg1>, <arg2>, ... <argN>).

📘

Request a function!

Don't see a function you want? Let us know via email or Slack!

Top level functions

coalesce

coalesce(values: boolean, ...) -> boolean
coalesce(values: number, ...) -> number
coalesce(values: string, ...) -> string

Use coalesce to handle null values, and return the first non-null value. If all arguments are null then the first non-null value is returned.

The coalesce function can be used with any type of scalar values, such as a boolean, number, or a string.

# prefer body.html.display_text if it's not null, but fallback to body.plain.raw
coalesce(body.html.display_text, body.plain.raw)

length (string)

length(input: string) -> integer

Use length to retrieve the number of Unicode code points used to encode a string in UTF-8. Note that this is not equivalent to the number of bytes or glyphs.

length("sublimesecurity.com") -> 19
length("📧") -> 1
length("émaîl") -> 5

length (array)

length(input: [T]) -> integer
Use length to retrieve the length of an array. If the input array is null, the function returns null. If the input array is non-null but empty, the function returns 0.

length(attachments) -> 3
length(filter(attachments, .file_extension == "xls")) -> 2

📘

Unicode and strings

Characters in strings are a complicated matter. Within MQL, all strings are always valid unicode and internally encoded as UTF-8.

Functions within MQL typically operate on unicode strings, and a "character" means a single UTF-8 code point, which can span more than one byte and may be an incomplete glyph.

Array looping functions

Arrays could come from various places: on the MDM, such as recipients.to or attachments; inline with [...] syntax, such as [body.html.raw, body.plain.raw]; or returned from functions, such as file.explode.

To process an array and loop over elements, MQL uses a few builtin functions. Array functions loop over an input array with an expression and return another array or some other value. Array functions typically take two arguments: the source array, and an expression to evaluate over the elements.

Within the looping expression, use the prefix . to access the loop item. To access an attribute of the looping item, use .<attribute>. To access fields on the root level of the MDM, drop the prefix. You can access a loop item from a parent scope with an additional ., such as ...

For example, to check if an array of strings contains the string secret:

any(["super", "secret", "text"], . == "secret")

To check the body.links array that the attribute .mismatched is always false:

all(body.links, .mismatched == false)

To check if either body.plain.raw or body.html.raw to contain any of the strings "urgent", "promptly", or "asap", use an array to avoid repeating logic:

any([body.plain.raw, body.html.raw], ilike(., "*urgent*", "*promptly*", "*asap*"))

{
  "attachments": [
    {
        "file_extension": "xls",
        ...
    },
    {
        "file_extension": "docx",
        ...
    },
    {
        "file_extension": "xls",
        ...
    }
  ],
  ...
}

all

all(input: [T], expression: bool) -> bool
all returns true if all of the values in the original array match an expression, which is provided by the second argument. If the array is empty, then all is vacuously `true.

Example usage

all(attachments, .file_extension == "xls") -> false
all(attachments, .file_extension in ("xls", "docx")) -> true

Tip: Use all if you want to perform an and across the array, and use any to perform an or.

any

any(input: [T], expression: bool) -> bool
any is the counterpart of all. Instead of checking for the expression to evaluate true for all items, any will check if at least one value returns true.

any(attachments, .file_extension == "xls") -> true
any(attachments, .file_extension in ("xls", "docx")) -> true

Tip: Use all if you want to perform an and across the array, and use any to perform an or.

distinct

distinct(array: [T]) -> [T]

distinct(array: [T], key) -> [T]
distinct filters an array, only returning one match per key.

distinct(attachments, .file_extension) -> [{"file_extension": "xls"}, {"file_extension": "docx"}]

One common use case is to combine it with length(array), to get a distinct count an array.

# how many unique file extensions are there?
length(distinct(attachments, .file_extension)) -> 2

distinct can also operate without a key if it's evaluated over an array of scalar values

filter

filter(array: [T], expression: bool) -> [T]
filter is used to generate a new filtered array, only returning items that cause the loop expression to evaluate true.

filter(attachments, .file_extension != "xls") -> [{"file_extension": "docx"}]

map

map(array: [T], expression: U) -> [U]
map is used to generate a new array, by evaluating an expression for each item in the array.

map(attachments, length(.file_extension)) -> [3, 4, 3]

map is particularly useful when querying an MDM:

map(attachments, .file_name) -> ["unpaid invoice.xlsx", "unpaid invoice.rar"]

sum

sum(array: [number]) -> number

sum(array: [T], expression: number) -> number

sum returns an aggregate count over all items in an array.

sum(attachments, .file_size)

sum doesn't need an expression if the array already consists of numbers.

sum([1, 2, 3, 4]) -> 10

ratio

ratio(array: [T], expression: bool) -> number

ratio calculates the ratio between items of the array that cause the loop expression to evaluate to true and the total number of items in the array. If the array is empty, ratio will return null.

ratio(attachments, .file_extension in ("xls", "docx")) -> 0.5
ratio([], . == "xls") -> null

null values are allowed in the input array.

ratio([1, 2, null, null], . > 0) -> 0.5