Array Functions

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Functions and operators for inspecting, accessing, and transforming array values. Use array functions to:

Access and inspect array elements by index or search for values.
Measure and analyze arrays by checking length and finding positions.
Modify and transform arrays by adding, removing, or deduplicating elements.
Perform vector operations for similarity searches on embeddings.

1-based indexing: Array indices start at 1, not 0.
Element types: Many functions require matching element types between arrays.
NULL handling: NULL elements are preserved unless explicitly removed with

For more information about supported types, see Array Type.

Access array elements and check for content:

Array Subscript Access - Get the element at the index.
ARRAY_LENGTH - Count the total number of elements.
ARRAY_CONTAINS - Check whether a value exists.
ARRAY_CONTAINS_ANY - Check for the overlap between arrays.
ARRAY_POSITION - Find the first occurrence of the value.
ARRAY_POSITIONS - Find all occurrences of the value.
ARRAY_TO_STRING - Convert an array to a delimited string.

Create and build arrays:

Array Constructor - Create an array from values.
ARRAY_AGG - Collect values from a group into an array.
ARRAY_PREPEND - Insert a value at the front of an array.
ARRAY_APPEND - Insert a value at end of an array.
ARRAY_CAT - Concatenate two arrays.
ARRAY_GENERATE_SERIES - Generate a sequence array.

Sort, filter, and transform arrays:

ARRAY_TRANSFORM - Apply a lambda function to each element.
ARRAY_FILTER - Keep only elements matching a condition.
ARRAY_SORT - Sort elements in ascending or descending order.
ARRAY_DISTINCT - Remove duplicate elements.
ARRAY_REMOVE - Remove all occurrences of a value.
ARRAY_REMOVE_AT - Remove the element at the defined position.
ARRAY_COMPACT - Remove all NULL elements.
TRIM_ARRAY - Remove the last n elements.

Expand an array into a set of rows:

unnest - Expand array elements into a set of rows.

Perform vector operations for similarity searches on embeddings:

DOT_PRODUCT - Compute the scalar product of vectors.
COSINE_SIMILARITY - Measure directional similarity between arrays.
BINARY_QUANTIZE - Compress a vector to binary values.
HAMMING_SIMILARITY - Compare binary vectors.

Find products with specific tags.

Find products matching any search tag.

Build a complete list by combining arrays.

Clean up arrays by removing nulls and duplicates.

Double every element using a lambda expression.

Filter to keep only values above a threshold.

Find top five most similar products using embeddings