Skip to main content

Array Functions

Scalar functions for array processing and manipulation that can be used in any expression context.

array_distinct(x)​

Removes duplicate elements from an array.

Parameters​

  • x - Array expression

Description​

The array_distinct() function returns a new array containing only the unique elements from the input array. The order of elements in the result is not guaranteed. If the input array is null, the function returns null.

any(x)​

Tests whether any element in a boolean array is true.

Parameters​

  • x - Array of boolean expressions

Description​

The any() function returns true if at least one element in the boolean array is true, false if all elements are false. If the array is empty, it returns false. If the array contains only null values, it returns null. This function performs logical OR aggregation across array elements.

all(x)​

Tests whether all elements in a boolean array are true.

Parameters​

  • x - Array of boolean expressions

Description​

The all() function returns true if all elements in the boolean array are true, false if at least one element is false. If the array is empty, it returns true. If the array contains only null values, it returns null. This function performs logical AND aggregation across array elements.

max(x)​

Returns the maximum element from an array.

Parameters​

  • x - Array of numeric, string, or timestamp expressions

Description​

The max() function finds and returns the largest element in the array. For numeric arrays, it returns the numerically largest value. For string arrays, it uses lexicographic ordering. For timestamp arrays, it returns the chronologically latest value. If the array is empty or contains only null values, it returns null.

min(x)​

Returns the minimum element from an array.

Parameters​

  • x - Array of numeric, string, or timestamp expressions

Description​

The min() function finds and returns the smallest element in the array. For numeric arrays, it returns the numerically smallest value. For string arrays, it uses lexicographic ordering. For timestamp arrays, it returns the chronologically earliest value. If the array is empty or contains only null values, it returns null.

sum(x)​

Returns the sum of all numeric elements in an array.

Parameters​

  • x - Array of numeric expressions

Description​

The sum() function calculates the sum of all numeric elements in the array. Null values are ignored in the calculation. If the array is empty or contains only null values, it returns null. The result type matches the element type for exact numeric types.

avg(x)​

Returns the average of all numeric elements in an array.

Parameters​

  • x - Array of numeric expressions

Description​

The avg() function calculates the arithmetic mean of all numeric elements in the array. Null values are ignored in the calculation. If the array is empty or contains only null values, it returns null. The result is always a floating-point type regardless of the input element type. This function divides the sum of all non-null elements by the count of non-null elements.

len(x)​

Returns the number of elements in an array.

Parameters​

  • x - Array expression of any element type

Description​

The len() function returns the number of elements in the array as an integer. This includes null elements in the count. If the array itself is null, the function returns null. An empty array returns 0.

filter_null(x)​

Removes null elements from an array.

Parameters​

  • x - Array expression of any element type

Description​

The filter_null() function returns a new array containing only the non-null elements from the input array. The order of remaining elements is preserved. If all elements are null, it returns an empty array. If the input array is null, the function returns null.

slice(array, start, end)​

Extracts a portion of an array between two indices.

Parameters​

  • array - Array expression of any element type
  • start - Integer expression for the starting index (0-based, supports negative indices)
  • end - Integer expression for the ending index (exclusive, supports negative indices)

Description​

The slice() function returns a new array containing elements from the start index up to but not including the end index. Both indices are 0-based and support negative values, where -1 refers to the last element, -2 to the second-last, and so on. If start is greater than or equal to end, an empty array is returned. The function handles out-of-bounds indices gracefully by clamping them to valid array boundaries.

split(string, delimiter)​

Splits a string into an array of substrings.

Parameters​

  • string - String expression to split
  • delimiter - String expression used as the separator

Description​

The split() function divides a string into an array of substrings based on the specified delimiter. The delimiter itself is not included in the resulting array elements. If the delimiter is not found in the string, the function returns an array containing the original string as its only element. An empty delimiter results in an error. Consecutive delimiters produce empty strings in the result.

array_join(array, delimiter) / array_join(array, delimiter, null_replacement)​

Joins array elements into a single string.

Parameters​

  • array - Array of string expressions
  • delimiter - String expression to place between elements
  • null_replacement (optional) - String expression to use for null elements

Description​

The array_join() function concatenates all elements of a string array into a single string, placing the delimiter between each element. By default, null elements are skipped. When you provide a null_replacement parameter, null elements are replaced with that string before joining. This is the inverse of the split() function and is useful for creating delimited strings from arrays.

flatten(x)​

Flattens a nested array by one level.

Parameters​

  • x - Array of arrays expression

Description​

The flatten() function takes an array of arrays and returns a single array containing all elements from the nested arrays. It only flattens one level deep, so arrays nested more deeply remain as array elements. The order of elements is preserved, with elements from earlier arrays appearing before elements from later arrays. If the input contains null arrays, they are skipped. This is useful for combining multiple arrays or processing results from operations that return arrays.

zip(left, right)​

Zips two arrays into an array of tuples.

Parameters​

  • left - First array expression
  • right - Second array expression

Description​

The zip() function pairs elements from two arrays positionally, returning an array of tuple(T1, T2) values (pairs). Access elements with tuple fields f0 and f1 (for example zipped[i].f0 for the left value and zipped[i].f1 for the right). See Tuple for tuple literals, types, and how f0 / f1 work. If the arrays have different lengths, the shorter array is padded with nulls. This is useful for combining parallel arrays into a single array of pairs for iteration or transformation.

transform(array, lambda)​

Applies a lambda function to each element of an array and returns the results.

Parameters​

  • array - Array expression of any element type
  • lambda - Lambda expression with a single parameter (e.g., x -> x * 2)

Description​

The transform() function applies the given lambda to each element of the input array and returns a new array containing the results. The lambda parameter is inferred to have the element type of the input array. The return type is an array whose element type matches the lambda's return type, so transform() can change the element type (e.g., transforming an array of strings into an array of integers via len()).

Examples​

// Double each element
SET doubled = transform([1, 2, 3], x -> x * 2)
// Result: [2, 4, 6]

// Transform strings to their lengths
SET lengths = transform(['a', 'bb', 'ccc'], s -> len(s))
// Result: [1, 2, 3]

// Convert key-value structs into a map using pair syntax
SET arr = [{ key: 'reason', val: 'Unconsolidatable' }, { key: 'note', val: 'Cannot replace' }]
| SET m = map(transform(arr, x -> x.key : x.val))
| SET reason = m['reason']
// reason == 'Unconsolidatable'

filter(array, lambda)​

Returns elements of an array for which a predicate lambda returns true.

Parameters​

  • array - Array expression of any element type
  • lambda - Lambda expression with a single parameter that returns a boolean (e.g., x -> x > 3)

Description​

The filter() function evaluates the given lambda for each element of the input array and returns a new array containing only the elements for which the lambda returned true. The return type is the same array type as the input. If no elements match, an empty array is returned. If the input array is null, the function returns null.

Examples​

// Keep only elements greater than 3
SET result = filter([1, 2, 3, 4, 5], x -> x > 3)
// Result: [4, 5]

// Filter strings by length
SET long_strings = filter(['a', 'bb', 'ccc', 'dd'], s -> len(s) > 1)
// Result: ['bb', 'ccc', 'dd']

// Combine filter and transform
SET active_names = transform(filter(users, u -> u.active == true), u -> u.name)