All functions > ARRAY
All functions in the ARRAY category.
Element Access
| Function | DuckDB | Trino | Serving | Description |
|---|
ELEMENT_AT_POS() | ✓ | ✓ | ✓ | Returns the element at the specified position in an array. |
ELEMENTS_AT_POS() | ✓ | ✓ | ✓ | Returns the elements at multiple specified positions in an array. |
SLICE_LEN() | ✓ | ✓ | ✓ | Returns a slice of an array using start:length:step semantics. |
SLICE_END() | ✓ | ✓ | ✓ | Returns the elements at multiple specified positions in an array. |
Search And Lookup
| Function | DuckDB | Trino | Serving | Description |
|---|
CONTAINS() | ✓ | ✓ | ✓ | Returns TRUE if the array contains the specified element. |
CONTAINS_SEQUENCE() | ✓ | ✓ | ✓ | Returns TRUE if the array contains the specified sequence as a contiguous subsequence. |
ARRAY_POSITION() | ✓ | ✓ | ✓ | Returns the 1-based position of the first occurrence of an element in an array. |
ARRAY_LOOKUP(...) | ✓ | ✓ | ✓ | Returns the value corresponding to a lookup key using parallel key-value arrays. |
Filtering And Transformation
| Function | DuckDB | Trino | Serving | Description |
|---|
ARRAY_SORT() | ✓ | ✓ | ✓ | Returns the sorted version of an array. |
ARRAY_REVERSE() | ✓ | ✓ | ✓ | Returns the reverse of an array. |
ARRAY_ENUMERATE() | ✓ | ✓ | ✓ | Returns an array of rows pairing each 1-based position with the corresponding element. |
ARRAY_ENUMERATE_FLATTEN() | ✓ | ✓ | ✓ | Returns an array of rows pairing each 1-based position with the flattened fields of the... |
ARRAY_ROTATE() | ✓ | ✗ | ✗ | Returns a new array whose elements are rotated circularly; positive amounts shift left, negative... |
NGRAMS() | ✓ | ✓ | ✓ | Returns adjacent n-element sub-sequences (n-grams) sliding across the input array. |
ARRAY_DISTINCT() | ✓ | ✓ | ✓ | Returns an array with distinct elements, in order of first occurrence. |
ARRAY_REMOVE() | ✓ | ✓ | ✓ | Returns an array with all occurrences of the specified element removed. |
FLATTEN() | ✓ | ✓ | ✓ | Flattens a nested array structure into a single-level array. |
Set Operations
| Function | DuckDB | Trino | Serving | Description |
|---|
ARRAY_UNION() | ✓ | ✓ | ✓ | Returns the union of two arrays, keeping the first occurrence of each element. |
ARRAY_INTERSECT() | ✓ | ✓ | ✓ | Returns the intersection of two arrays using set semantics. |
ARRAY_EXCEPT() | ✓ | ✓ | ✓ | Returns the difference of two arrays using set semantics. |
ARRAYS_OVERLAP() | ✓ | ✓ | ✓ | Returns TRUE if any elements are common between the two arrays. |
Aggregation And Statistics
| Function | DuckDB | Trino | Serving | Description |
|---|
ARRAY_COUNT() | ✓ | ✓ | ✓ | Returns the number of elements in an array. |
ARRAY_SUM() | ✓ | ✓ | ✓ | Returns the sum of all numeric elements in an array. |
ARRAY_PRODUCT() | ✓ | ✗ | ✗ | Returns the product of all numeric elements in an array as a DOUBLE. |
ARRAY_MEDIAN() | ✓ | ✗ | ✗ | Returns the statistical median of numeric array elements as a DOUBLE. |
ARRAY_STDDEV_POP() | ✓ | ✗ | ✗ | Returns the population standard deviation (divide by N) of numeric array elements as a DOUBLE. |
ARRAY_STDDEV_SAMP() | ✓ | ✗ | ✗ | Returns the sample standard deviation (divide by N-1) of numeric array elements as a DOUBLE. |
ARRAY_VAR_POP() | ✓ | ✗ | ✗ | Returns the population variance (divide by N) of numeric array elements as a DOUBLE. |
ARRAY_VAR_SAMP() | ✓ | ✗ | ✗ | Returns the sample variance (divide by N-1) of numeric array elements as a DOUBLE. |
ARRAY_AVG() | ✓ | ✓ | ✓ | Returns the arithmetic mean (average) of all numeric elements in an array. |
ARRAY_MIN() | ✓ | ✓ | ✓ | Returns the smallest value in an array using natural ordering. |
ARRAY_MAX() | ✓ | ✓ | ✓ | Returns the largest value in an array using natural ordering. |
ARRAY_HISTOGRAM() | ✓ | ✓ | ✓ | Returns a frequency histogram of values in an array as an array of value-count pairs. |
Boolean Operations
| Function | DuckDB | Trino | Serving | Description |
|---|
ALL(...) | ✓ | ✓ | ✓ | Returns TRUE if all elements in the boolean array are true. |
ANY(...) | ✓ | ✓ | ✓ | Returns TRUE if any element in the boolean array is true. |
NONE(...) | ✓ | ✓ | ✓ | Returns TRUE if no elements in the boolean array are true. |
ARRAY_NOT() | ✓ | ✓ | ✓ | Returns an array with inverted boolean values (logical NOT applied to each element). |
Array Combination
| Function | DuckDB | Trino | Serving | Description |
|---|
ARRAY_CONCAT(...) | ✓ | ✓ | ✓ | Concatenates multiple arrays into a single combined array with optional deduplication. |
COMBINATIONS() | ✓ | ✓ | ✓ | Returns all 2-element sub-groups of the input array as an array of 2-element arrays. |
PERMUTATIONS() | ✓ | ✓ | ✓ | Returns all ordered 2-element selections from the input array as an array of 2-element arrays. |
Array Generation
| Function | DuckDB | Trino | Serving | Description |
|---|
REPEAT() | ✓ | ✓ | ✓ | Creates an array by repeating an element a specified number of times. |
String Conversion
| Function | DuckDB | Trino | Serving | Description |
|---|
ARRAY_JOIN() | ✓ | ✓ | ✓ | Joins the elements of an array into a single string using a specified delimiter. |
Vector Operations
| Function | DuckDB | Trino | Serving | Description |
|---|
COSINE_SIMILARITY() | ✓ | ✓ | ✓ | Calculates the cosine similarity between two arrays. |
DOT_PRODUCT() | ✓ | ✓ | ✓ | Calculates the dot product of two arrays. |
EUCLIDEAN_DISTANCE() | ✓ | ✓ | ✓ | Calculates the Euclidean distance between two arrays. |
NORMALIZE() | ✓ | ✓ | ✓ | Normalizes a numeric array by dividing each element by the sum of all elements. |
Summary
Functions in this category: 46