Array
ALLITEMSTRUE
✔️ Safe to use in filter queries
Evaluates the expression on all elements of the array as a set and returns true if no element in the array evaluates to false. Otherwise, returns false. An empty array returns true. ALLITEMSTRUE fails with an error if the first argument does not resolve to an array.
Syntax
ALLITEMSTRUE(array, boolean_expression)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of objects or basic values. Can be either a basic values list, object list, or multi-select options list. |
| boolean_expression | mandatory | An expression that evaluates to a boolean (true or false) result. For each item of the array, the expression is evaluated with the array item as the execution context variable of the expression. |
ANYITEMTRUE
✔️ Safe to use in filter queries
Evaluates the expression on all elements of the array as a set and returns true if any element in the array evaluates to true and false otherwise. An empty array returns false. ANYITEMTRUE fails with an error if the first argument does not resolve to an array.
Syntax
ANYITEMTRUE(array, boolean_expression)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of objects or basic values. Can be either a basic values list, object list, or multi-select options list. |
| boolean_expression | mandatory | An expression that evaluates to a boolean (true or false) result. For each item of the array, the expression is evaluated with the array item as the execution context variable of the expression. |
APPEND
Adds the values(s) to the end of the array. The data type of the array (e.g., the data type of the items stored in the array) and the data type of appended value(s) should be the same. APPEND fails with an error if the first argument does not resolve to an array.
✔️ Safe to use in filter queries
Syntax
APPEND(array, value1, value2, ...)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list, object list, or multi-select options list. |
| value1 | mandatory | value1, value2, ... are 1 to 100. The value that will be added to the end of the array. Value can be of any integer, decimal, boolean, text, or date & time value. Auto-number field values are also accepted since they are basically numeric values. |
| value2 | optional | value1, value2, ... are 1 to 100. The value that will be added to the end of the array. Value can be of any integer, decimal, boolean, text, or date & time value. Auto-number field values are also accepted since they are basically numeric values. |
CONCATARRAYS
CONCATARRAYS concatenates arrays to return the concatenated array. The data type of the concatenated arrays (e.g., the data type of the items stored in the array) should be the same.
✔️ Safe to use in filter queries
Syntax
CONCATARRAYS(array1, array2, ...)
| Name | Type | Description |
|---|---|---|
| array1 | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| array2 | mandatory | An array of basic values. Can be either a basic values list or multi-select options list.. |
| array3 | optional | array1, array2, ... are 1 to 100. The array that will be concatenated to the overall array. Can be either a basic values list or multi-select options list. |
DIFFERENCE
Takes two arrays and returns an array containing the elements that only exist in the first array. This function performs set operation on arrays, treating arrays as sets. If an array contains duplicate entries, it ignores the duplicate entries. It also ignores the order of the elements. It filters out duplicates in its result to output an array that contain only unique entries. The order of the elements in the output array is unspecified.
✔️ Safe to use in filter queries
Syntax
DIFFERENCE(array1, array2)
| Name | Type | Description |
|---|---|---|
| array1 | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| array2 | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
EQUAL
Compares two arrays and returns true if they have the same distinct elements and false otherwise. This function performs set operation on arrays, treating arrays as sets. If an array contains duplicate entries, it ignores the duplicate entries. It also ignores the order of the elements.
✔️ Safe to use in filter queries
Syntax
EQUAL(array1, array2)
| Name | Type | Description |
|---|---|---|
| array1 | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| array2 | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
FILTER
✔️ Safe to use in filter queries
Selects a subset of an array to return based on the specified condition. Returns an array with only those elements that match the condition. The returned elements are in the original order. FILTER fails with an error if the first argument does not resolve to an array.
Syntax
FILTER(array, boolean_expression)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| boolean_expression | mandatory | An expression that evaluates to a boolean (true or false) result. For each item of the array, the expression is evaluated with the array item as the execution context variable of the expression. |
Context values
value: "value" refers to the array element being processed. During expression execution "value" will be replaced with the actual array item value.
FIRSTITEM
✔️ Safe to use in filter queries
Returns the first element in an array. FIRSTITEM fails with an error if the first argument does not resolve to an array.
Syntax
FIRSTITEM(array)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
IN
✔️ Safe to use in filter queries
Returns a boolean indicating whether a specified value is in an array. The data type of the array (e.g., the data type of the items stored in the array) and the data type of checked value should be the same. IN fails with an error if the first argument does not resolve to an array.
Syntax
IN(array, value)Type
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| value | mandatory | Value to check whether it is stored in the array or not. Can be any valid expression that resolves to the same data type of the values stored in the array. |
INDEXOFARRAY
✔️ Safe to use in filter queries
Searches an array for an occurrence of a specified value and returns the array index (zero-based) of the first occurrence. If the value is not found, returns -1. The data type of the array (e.g., the data type of the items stored in the array) and the data type of searched value should be the same. INDEXOFARRAY fails with an error if the first argument does not resolve to an array.
Syntax
INDEXOFARRAY(array, value, starting_index)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| value | mandatory | Value to check whether it is stored in the array or not. Can be any valid expression that resolves to the same data type of the values stored in the array. |
| starting_index | optional | An integer, or a number that can be represented as integers, that specifies the starting index position for the search. Can be any valid expression that resolves to a non-negative number. If unspecified, the starting index position for the search is the beginning of the array. |
INTERSECTION
Takes two arrays and returns an array that contains the elements that appear in both input array. This function performs set operation on arrays, treating arrays as sets. If an array contains duplicate entries, it ignores the duplicate entries. It also ignores the order of the elements. It filters out duplicates in its result to output an array that contain only unique entries. The order of the elements in the output array is unspecified. If no intersections are found (i.e. the input arrays contain no common elements), it returns an empty array.
✔️ Safe to use in filter queries
Syntax
INTERSECTION(array1, array2)
| Name | Type | Description |
|---|---|---|
| array1 | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| array2 | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
ISSUBSET
Takes two arrays and returns true when the first array (array1) is a subset of the second (array2), including when the first array equals the second array or the first array is an empty one, and false otherwise. This function performs set operation on arrays, treating arrays as sets. If an array contains duplicate entries, it ignores the duplicate entries. It also ignores the order of the elements.
✔️ Safe to use in filter queries
Syntax
ISSUBSET(array1, array2)
| Name | Type | Description |
|---|---|---|
| array1 | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| array2 | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
ITEMATINDEX
✔️ Safe to use in filter queries
Returns the element at the specified array index. ITEMATINDEX fails with an error if the first argument does not resolve to an array or if the index exceeds the array bounds.
Syntax
ITEMATINDEX(array, index)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| index | mandatory | An integer, or a number that can be represented as integers, that specifies the index position (zero-based) of the array. Can be any valid expression that resolves to a number. If index positive, ITEMATINDEX returns the element at the index position, counting from the start of the array. If index negative, ITEMATINDEX returns the element at the index position, counting from the end of the array. |
LASTITEM
✔️ Safe to use in filter queries
Returns the last element in an array. LASTITEM fails with an error if the first argument does not resolve to an array.
Syntax
LASTITEM(array)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
MAP
✔️ Safe to use in filter queries
Applies an expression to each item in an array and returns an array with the applied results. MAP fails with an error if the first argument does not resolve to an array.
Syntax
MAP(array, expression)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| expression | mandatory | An expression that resolves to a value of type integer, decimal, boolean, text, or date & time value. The expression references each element of the array individually with the variable name specified as "value". |
Context values
value: "value" refers to the array element being processed. During expression execution "value" will be replaced with the actual array item value.
OBJLISTTOBVL
❌ Cannot be used in filter queries
Evaluates the object value expression on all elements of the array and returns a new basic values list with the evaluated object values. In the value expression, prefix the field values that you would like to access with "this" keyword; as an example if you would like to get the _id of the objects, then use "this._id".
Syntax
OBJLISTTOBVL(object_array, value_expression)
| Name | Type | Description |
|---|---|---|
| object_array | mandatory | An array of objects. |
| value_expression | mandatory | An expression that evaluates to a primitive value (e.g., integer, decimal, boolean, or text). For each object of the array, the expression is evaluated with the array object as the execution context variable of the expression. |
PREPEND
✔️ Safe to use in filter queries
Adds the values(s) to the beginning of the array. The data type of the array (e.g., the data type of the items stored in the array) and the data type of prepended value(s) should be the same. APPEND fails with an error if the first argument does not resolve to an array.
Syntax
PREPEND(array, value1, value2, ...)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| value1 | mandatory | value1, value2, ... are 1 to 100. The value that will be added to the beginning of the array. Value can be of any integer, decimal, boolean, text, or date & time value. Auto-number field values are also accepted since they are basically numeric values. |
| value2 | optional | value1, value2, ... are 1 to 100. The value that will be added to the beginning of the array. Value can be of any integer, decimal, boolean, text, or date & time value. Auto-number field values are also accepted since they are basically numeric values. |
REDUCE
✔️ Safe to use in filter queries
Applies an expression to each element in an array and combines them into a single value. REDUCE fails with an error if the first argument does not resolve to an array.
Syntax
REDUCE(array, initial_value, expression)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| initial_value | mandatory | The initial cumulative value set before the expression is applied to the first element of the input array. |
| expression | mandatory | A valid expression that REDUCE applies to each element in the input array in left-to-right order. During the execution of the expression, two variables can be used "cumulative_value" and "value". |
Context values
cumulative_value : Represents the cumulative value of the expression.
value : "value" refers to the array element being processed. During expression execution "value" will be replaced with the actual array item value.
REMOVE
✔️ Safe to use in filter queries
Removes the first occurrence of the value from the array and returns the updated array. If the value is not found, returns the array as it is. The data type of the array (e.g., the data type of the items stored in the array) and the data type of removed value should be the same. REMOVE fails with an error if the first argument does not resolve to an array.
Syntax
REMOVE(array, value)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| value | mandatory | Value to remove the first occurrence from the array. Can be any valid expression that resolves to the same data type of the values stored in the array. |
REMOVEALL
✔️ Safe to use in filter queries
Removes all occurrences of the value from the array and returns the updated array. If the value is not found, returns the array as it is. The data type of the array (e.g., the data type of the items stored in the array) and the data type of removed value should be the same. REMOVEALL fails with an error if the first argument does not resolve to an array.
Syntax
REMOVEALL(array, value)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| value | mandatory | Value to remove the occurrence(s) from the array. Can be any valid expression that resolves to the same data type of the values stored in the array. |
REMOVEFIRST
✔️ Safe to use in filter queries
Removes the first element from the array and returns the updated array. REMOVEFIRST fails with an error if the array argument does not resolve to an array.
Syntax
REMOVEFIRST(array)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
REMOVELAST
✔️ Safe to use in filter queries
Removes the last element from the array and returns the updated array. REMOVELAST fails with an error if the array argument does not resolve to an array.
Syntax
REMOVELAST(array)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
REVERSE
✔️ Safe to use in filter queries
Returns an array with the elements in reverse order. REVERSE fails with an error if the array argument does not resolve to an array.
Syntax
REVERSE(array)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
SIZE
✔️ Safe to use in filter queries
Counts and returns the total number of items in an array. SIZE fails with an error if the array argument does not resolve to an array.
Syntax
SIZE(array)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
SLICE
✔️ Safe to use in filter queries
Returns a subset of an array selected from the start index and up to count number of items. SLICE fails with an error if the first argument does not resolve to an array or if the index exceeds the array bounds.
Syntax
SLICE(array, count, start_index)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| count | mandatory | Any valid expression as long as it resolves to an integer. If the start index is specified, count must resolve to a positive integer. If count is positive, SLICE returns up to the first count elements in the array. If the start index is specified, SLICE returns the first count elements starting from the start index. If count is negative, SLICE returns up to the last count elements in the array. Count cannot resolve to a negative number if start index is specified. |
| start_index | optional | Any valid expression as long as it resolves to an integer that specifies the start index. Defaults to 0 if unspecified. If the start index is positive, SLICE determines the starting index from the start of the array. If the start index is greater than the number of elements in the array, the SLICE returns an empty array. If the start index is negative, SLICE determines the starting index from the end of the array. If the absolute value of the start index is greater than the number of elements, the starting index is the start of the array. |
SORT
❌ Cannot be used in filter queries
Sorts the contents of the array ascending or descending. SORT fails with an error if the first argument does not resolve to an array.
Syntax
SORT(array, sort_direction)
| Name | Type | Description |
|---|---|---|
| array | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| sort_direction | mandatory | "asc" or "desc" sort direction value. Can be also any expression that resolves to "asc" or "desc" text value. |
UNION
Takes two arrays and returns an array containing the elements that appear in any of the two input arrays. This function performs set operation on arrays, treating arrays as sets. If an array contains duplicate entries, it ignores the duplicate entries. It also ignores the order of the elements. It filters out duplicates in its result to output an array that contain only unique entries. The order of the elements in the output array is unspecified.
✔️ Safe to use in filter queries
Syntax
UNION(array1, array2)
| Name | Type | Description |
|---|---|---|
| array1 | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |
| array2 | mandatory | An array of basic values. Can be either a basic values list or multi-select options list. |