SQL ARRAY functions

info

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 expression arguments, using the type of the first argument as the output array type.
`ARRAY_LENGTH(arr)`	Returns length of the array expression.
`ARRAY_OFFSET(arr, long)`	Returns the array element at the 0-based index supplied, or null for an out of range index.
`ARRAY_ORDINAL(arr, long)`	Returns the array element at the 1-based index supplied, or null for an out of range index.
`ARRAY_CONTAINS(arr, expr)`	If `expr` is a scalar type, returns true if `arr` contains `expr`. If `expr` is an array, returns true if `arr` contains all elements of `expr`. Otherwise returns false.
`ARRAY_OVERLAP(arr1, arr2)`	Returns true if `arr1` and `arr2` have any elements in common, else false.
`SCALAR_IN_ARRAY(expr, arr)`	Returns true if the scalar `expr` is present in `arr`. Otherwise, returns false if the scalar `expr` is non-null or `UNKNOWN` if the scalar `expr` is `NULL`.
`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` or `-1` if `druid.generic.useDefaultValueForNull=true` (deprecated legacy mode).
`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` or `-1` if `druid.generic.useDefaultValueForNull=true` (deprecated legacy mode).
`ARRAY_PREPEND(expr, arr)`	Adds `expr` to the beginning of `arr`, the resulting array type determined by the type of `arr`.
`ARRAY_APPEND(arr, expr)`	Appends `expr` to `arr`, the resulting array type determined by the type of `arr`.
`ARRAY_CONCAT(arr1, arr2)`	Concatenates `arr2` to `arr1`. The resulting array type is determined by the type of `arr1`.
`ARRAY_SLICE(arr, start, end)`	Returns the subarray of `arr` from the 0-based index `start` (inclusive) to `end` (exclusive). Returns `null`, if `start` is less than 0, greater than length of `arr`, or greater than `end`.
`ARRAY_TO_STRING(arr, str)`	Joins all elements of `arr` by the delimiter specified by `str`.
`STRING_TO_ARRAY(str1, str2)`	Splits `str1` into an array on the delimiter specified by `str2`, which is a regular expression.
`ARRAY_TO_MV(arr)`	Converts an `ARRAY` of any type into a multi-value string `VARCHAR`.