SQL multi-value string functions

SQL multi-value string functions

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

Function	Notes
`ARRAY[expr1, expr2, …]`	Constructs a SQL ARRAY literal from the expression arguments, using the type of the first argument as the output array type.
`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 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)`	Returns 1 if the array contains the element specified by `expr`, or contains all elements specified by `expr` if `expr` is an array, else 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, or `-1` or `null` if `druid.generic.useDefaultValueForNull=false` if no matching elements exist in the array.
`MV_ORDINAL_OF(arr, expr)`	Returns the 1 based index of the first occurrence of `expr` in the array, or `-1` or `null` if `druid.generic.useDefaultValueForNull=false` if no matching elements exist in the array.
`MV_PREPEND(expr, arr)`	Adds `expr` to `arr` at the beginning, the resulting array type determined by the type of the array.
`MV_APPEND(arr1, expr)`	Appends `expr` to `arr`, the resulting array type determined by the type of the first array.
`MV_CONCAT(arr1, arr2)`	Concatenates 2 arrays, the resulting array type determined by the type of the first array.
`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 less 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`.

Multi-value string functions

SQL multi-value string functions