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_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 and clean up arrays:

• 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.

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.

1SELECT product_name
2FROM products
3WHERE array_contains(tags, 'sale');

Find products matching any search tag.

1SELECT product_name
2FROM products
3WHERE array_contains_any(tags, array['new', 'featured', 'popular']);

Build a complete list by combining arrays.

1SELECT array_cat(primary_categories, secondary_categories) AS all_categories
2FROM products;

Clean up arrays by removing nulls and duplicates.

1SELECT array_distinct(array_compact(values)) AS clean_values
2FROM data;

Find top five most similar products using embeddings

1SELECT
2    name,
3    dot_product('[0.1, -0.2, 0.7, -0.3]'::REAL[], description_vec) AS score
4FROM products
5ORDER BY score DESC
6LIMIT 5;

• Array Type
• Aggregate Functions