SQL ARRAY functions
Apache Druid supports two query languages: Druid SQL and native queries. This document describes the SQL language.
This page describes the operations you can perform on arrays using Druid SQL. See ARRAY data type documentation for additional details.
All array references in the array function documentation can refer to multi-value string columns or ARRAY literals.
These functions are largely identical to the multi-value string functions, but
use ARRAY types and behavior. Multi-value string VARCHAR columns can be converted to VARCHAR ARRAY to use with
these functions using MV_TO_ARRAY, and ARRAY types can be converted to multi-value string VARCHAR with
ARRAY_TO_MV.
The following table describes array functions. To learn more about array aggregation functions, see SQL aggregation functions.
| Function | Description |
|---|---|
ARRAY[expr1, expr2, ...] | Constructs a SQL ARRAY literal from the provided expression arguments. All arguments must be of the same type. |
ARRAY_APPEND(arr, expr) | Appends the expression to the array. The source array type determines the resulting array type. |
ARRAY_CONCAT(arr1, arr2) | Concatenates two arrays. The type of arr1 determines the resulting array type. |
ARRAY_CONTAINS(arr, expr) | Checks if the array contains the specified expression. If the specified expression is a scalar value, returns true if the source array contains the value. If the specified expression is an array, returns true if the source array contains all elements of the expression. |
ARRAY_LENGTH(arr) | Returns the length of the array. |
ARRAY_OFFSET(arr, long) | Returns the array element at the specified zero-based index. Returns null if the index is out of bounds. |
ARRAY_OFFSET_OF(arr, expr) | Returns the 0-based index of the first occurrence of expr in the array. If no matching elements exist in the array, returns null. |
ARRAY_ORDINAL(arr, long) | Returns the array element at the specified one-based index. Returns null if the index is out of bounds. |
ARRAY_ORDINAL_OF(arr, expr) | Returns the 1-based index of the first occurrence of expr in the array. If no matching elements exist in the array, returns null. |
ARRAY_OVERLAP(arr1, arr2) | Returns true if two arrays have any elements in common. Treats NULL values as known elements. |
ARRAY_PREPEND(expr, arr) | Prepends the expression to the array. The source array type determines the resulting array type. |
ARRAY_SLICE(arr, start, end) | Returns a subset of the array from the zero-based index start (inclusive) to end (exclusive). Returns null if start is less than 0, greater than the length of the array, or greater than end. |
ARRAY_TO_MV(arr) | Converts an array of any type into a multi-value string. |
ARRAY_TO_STRING(arr, delimiter) | Joins all elements of the array into a string using the specified delimiter. |
SCALAR_IN_ARRAY(expr, arr) | Checks if the scalar value is present in the array. Returns false if the value is non-null, or UNKNOWN if the value is NULL. Returns UNKNOWN if the array is NULL. |
STRING_TO_ARRAY(string, delimiter) | Splits the string into an array of substrings using the specified delimiter. The delimiter must be a valid regular expression. |