2023.01

2023.01

  • Imply
  • Ingest
  • Query
  • Visualize
  • Administer
  • Deploy

›Druid SQL Functions

Overview

  • Querying data
  • Datasources

    • Datasources
    • Joins
    • Lookups
    • Query execution

    Query configuration

    • Query caching
    • Using query caching
    • Query context
  • Enhanced IP support
  • Multi-value dimensions
  • Nested columns
  • Multitenancy
  • Troubleshooting

Druid SQL

  • Overview and syntax
  • SQL data types
  • Druid SQL Functions

    • All functions
    • Operators
    • Scalar functions
    • Aggregation functions
    • JSON functions
    • Multi-value string functions
  • SQL metadata tables
  • SQL query context
  • Async SQL download
  • SQL query translation
  • Druid SQL APIs

    • Druid SQL API
    • Async SQL download API
    • JDBC driver API

Native queries

  • Native queries
  • Native query types

    • Timeseries
    • TopN
    • GroupBy
    • Scan
    • Search
    • TimeBoundary
    • SegmentMetadata
    • DatasourceMetadata

    Native query components

    • Filters
    • Granularities
    • Dimensions
    • Aggregations
    • Post-aggregations
    • Expressions
    • Having filters (groupBy)
    • Sorting and limiting (groupBy)
    • Sorting (topN)
    • String comparators
    • Virtual columns
    • Spatial filters

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.

FunctionNotes
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.
Last updated on 11/17/2022
← JSON functionsSQL metadata tables →
2023.01
Key links
Try ImplyApache Druid siteImply GitHub
Get help
Stack OverflowSupportContact us
Learn more
Apache Druid forumsBlog
Copyright © 2023 Imply Data, Inc