SQL multi-value string functions

info

Apache Druid supports two query languages: Druid SQL and native queries. This document describes the SQL language.

Druid supports string dimensions containing multiple values. This page describes the operations you can perform on multi-value string dimensions using Druid SQL. See SQL multi-value strings and native Multi-value dimensions for more information.

All array references in the multi-value string function documentation can refer to multi-value string columns or ARRAY types. These functions are largely identical to the array functions, but use VARCHAR types and behavior. Multi-value strings can also be converted to ARRAY types using MV_TO_ARRAY, and ARRAY into multi-value strings via ARRAY_TO_MV. For additional details about ARRAY types, see ARRAY data type documentation.

Function	Description
`MV_FILTER_ONLY(expr, arr)`	Filters multi-value `expr` to include only values contained in array `arr`.
`MV_FILTER_NONE(expr, arr)`	Filters multi-value `expr` to include no values contained in array `arr`.
`MV_LENGTH(arr)`	Returns length of the array expression.
`MV_OFFSET(arr, long)`	Returns the array element at the 0-based index supplied, or null for an out of range index.
`MV_ORDINAL(arr, long)`	Returns the array element at the 1-based index supplied, or null for an out of range index.
`MV_CONTAINS(arr, expr)`	If `expr` is a scalar type, returns 1 if `arr` contains `expr`. If `expr` is an array, returns 1 if `arr` contains all elements of `expr`. Otherwise returns 0.
`MV_OVERLAP(arr1, arr2)`	Returns 1 if `arr1` and `arr2` have any elements in common, else 0.
`MV_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).
`MV_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).
`MV_PREPEND(expr, arr)`	Adds `expr` to the beginning of `arr`, the resulting array type determined by the type `arr`.
`MV_APPEND(arr, expr)`	Appends `expr` to `arr`, the resulting array type determined by the type of `arr`.
`MV_CONCAT(arr1, arr2)`	Concatenates `arr2` to `arr1`. The resulting array type is determined by the type of `arr1`.
`MV_SLICE(arr, start, end)`	Returns the subarray of `arr` from the 0-based index start(inclusive) to end(exclusive), or `null`, if start is less than 0, greater than length of arr or greater than end.
`MV_TO_STRING(arr, str)`	Joins all elements of `arr` by the delimiter specified by `str`.
`STRING_TO_MV(str1, str2)`	Splits `str1` into an array on the delimiter specified by `str2`, which is a regular expression.
`MV_TO_ARRAY(str)`	Converts a multi-value string from a `VARCHAR` to a `VARCHAR ARRAY`.