Skip to main content

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)

NameTypeDescription
arraymandatoryAn array of objects or basic values. Can be either a basic values list, object list, or multi-select options list.
boolean_expressionmandatoryAn 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)

NameTypeDescription
arraymandatoryAn array of objects or basic values. Can be either a basic values list, object list, or multi-select options list.
boolean_expressionmandatoryAn 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, ...)

NameTypeDescription
arraymandatoryAn array of basic values. Can be either a basic values list, object list, or multi-select options list.
value1mandatoryvalue1, 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.
value2optionalvalue1, 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, ...)

NameTypeDescription
array1mandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
array2mandatoryAn array of basic values. Can be either a basic values list or multi-select options list..
array3optionalarray1, 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)

NameTypeDescription
array1mandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
array2mandatoryAn 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)

NameTypeDescription
array1mandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
array2mandatoryAn 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)

NameTypeDescription
arraymandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
boolean_expressionmandatoryAn 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)

NameTypeDescription
arraymandatoryAn 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

NameTypeDescription
arraymandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
valuemandatoryValue 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)

NameTypeDescription
arraymandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
valuemandatoryValue 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_indexoptionalAn 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)

NameTypeDescription
array1mandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
array2mandatoryAn 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)

NameTypeDescription
array1mandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
array2mandatoryAn 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)

NameTypeDescription
arraymandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
indexmandatory

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)

NameTypeDescription
arraymandatoryAn 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)

NameTypeDescription
arraymandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
expressionmandatoryAn 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)

NameTypeDescription
object_arraymandatoryAn array of objects.
value_expressionmandatoryAn 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, ...)

NameTypeDescription
arraymandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
value1mandatoryvalue1, 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.
value2optionalvalue1, 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)

NameTypeDescription
arraymandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
initial_valuemandatoryThe initial cumulative value set before the expression is applied to the first element of the input array.
expressionmandatoryA 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)

NameTypeDescription
arraymandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
valuemandatoryValue 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)

NameTypeDescription
arraymandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
valuemandatoryValue 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)

NameTypeDescription
arraymandatoryAn 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)

NameTypeDescription
arraymandatoryAn 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)

NameTypeDescription
arraymandatoryAn 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)

NameTypeDescription
arraymandatoryAn 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)

NameTypeDescription
arraymandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
countmandatory

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_indexoptional

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)

NameTypeDescription
arraymandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
sort_directionmandatory"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)

NameTypeDescription
array1mandatoryAn array of basic values. Can be either a basic values list or multi-select options list.
array2mandatoryAn array of basic values. Can be either a basic values list or multi-select options list.