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.
Considerations
- 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 and Inspection
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.
Array Building
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.
Array Transformation
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
nelements.
Array Expansion
Expand an array into a set of rows:
unnest- Expand array elements into a set of rows.
Vector and Similarity
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.
Examples
Filtering by Array Content
Find products with specific tags.
SELECT product_name
FROM products
WHERE array_contains(tags, 'sale');
Find products matching any search tag.
SELECT product_name
FROM products
WHERE array_contains_any(tags, array['new', 'featured', 'popular']);
Array Manipulation
Build a complete list by combining arrays.
SELECT array_cat(primary_categories, secondary_categories) AS all_categories
FROM products;
Clean up arrays by removing nulls and duplicates.
SELECT array_distinct(array_compact(values)) AS clean_values
FROM data;
Transforming Array Elements
Double every element using a lambda expression.
SELECT array_transform(array[1, 2, 3, 4], lambda x: x * 2) AS doubled;
-- Returns [2, 4, 6, 8]
Filter to keep only values above a threshold.
SELECT array_filter(array[10, 25, 5, 30, 15], lambda x: x > 20) AS large;
-- Returns [25, 30]
Vector Search
Find top five most similar products using embeddings
SELECT
name,
dot_product('[0.1, -0.2, 0.7, -0.3]'::REAL[], description_vec) AS score
FROM products
ORDER BY score DESC
LIMIT 5;