docs/querying/sql-functions.md

id: sql-functions title: “All Druid SQL functions” sidebar_label: “All functions”

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

This page provides a reference of Apache Druid® SQL functions in alphabetical order. For more details on a function, refer to the following:

Example data

The examples on this page use the following example datasources:

array-example created with SQL-based ingestion
flight-carriers using FlightCarrierOnTime (1 month) included with Druid
kttm using KoalasToTheMax one day included with Druid
mvd-example using SQL-based ingestion
taxi-trips using NYC Taxi cabs (3 files) included with Druid

To load a datasource included with Druid, access the web console and go to Load data > Batch - SQL > Example data. Select Connect data, and parse using the default settings. On the page to configure the schema, select the datasource label and enter the name of the datasource listed above.

Use the following query to create the array-example datasource:

REPLACE INTO "array-example" OVERWRITE ALL
WITH "ext" AS (
  SELECT *
  FROM TABLE(
    EXTERN(
      '{"type":"inline","data":"{\"timestamp\": \"2023-01-01T00:00:00\", \"label\": \"row1\", \"arrayString\": [\"a\", \"b\"],  \"arrayLong\":[1, null,3], \"arrayDouble\":[1.1, 2.2, null]}\n{\"timestamp\": \"2023-01-01T00:00:00\", \"label\": \"row2\", \"arrayString\": [null, \"b\"], \"arrayLong\":null,        \"arrayDouble\":[999, null, 5.5]}\n{\"timestamp\": \"2023-01-01T00:00:00\", \"label\": \"row3\", \"arrayString\": [],          \"arrayLong\":[1, 2, 3],   \"arrayDouble\":[null, 2.2, 1.1]} \n{\"timestamp\": \"2023-01-01T00:00:00\", \"label\": \"row4\", \"arrayString\": [\"a\", \"b\"],  \"arrayLong\":[1, 2, 3],   \"arrayDouble\":[]}\n{\"timestamp\": \"2023-01-01T00:00:00\", \"label\": \"row5\", \"arrayString\": null,        \"arrayLong\":[],          \"arrayDouble\":null}"}',
      '{"type":"json"}'
    )
  ) EXTEND (
    "timestamp" VARCHAR,
    "label" VARCHAR,
    "arrayString" VARCHAR ARRAY,
    "arrayLong" BIGINT ARRAY,
    "arrayDouble" DOUBLE ARRAY
  )
)
SELECT
    TIME_PARSE("timestamp") AS "__time",
    "label",
    "arrayString",
    "arrayLong",
    "arrayDouble"
FROM "ext"
PARTITIONED BY DAY

Use the following query to create the mvd-example datasource:

REPLACE INTO "mvd-example" OVERWRITE ALL
WITH "ext" AS (
  SELECT *
  FROM TABLE(
    EXTERN(
      '{"type":"inline","data":"{\"timestamp\": \"2011-01-12T00:00:00.000Z\", \"label\": \"row1\", \"tags\": [\"t1\",\"t2\",\"t3\"]}\n{\"timestamp\": \"2011-01-13T00:00:00.000Z\", \"label\": \"row2\", \"tags\": [\"t3\",\"t4\",\"t5\"]}\n{\"timestamp\": \"2011-01-14T00:00:00.000Z\", \"label\": \"row3\", \"tags\": [\"t5\",\"t6\",\"t7\"]}\n{\"timestamp\": \"2011-01-14T00:00:00.000Z\", \"label\": \"row4\", \"tags\": []}"}',
      '{"type":"json"}',
      '[{"name":"timestamp", "type":"STRING"},{"name":"label", "type":"STRING"},{"name":"tags", "type":"ARRAY<STRING>"}]'
    )
  )
)
SELECT
  TIME_PARSE("timestamp") AS "__time",
  "label",
  ARRAY_TO_MV("tags") AS "tags"
FROM "ext"
PARTITIONED BY DAY

ABS

Calculates the absolute value of a numeric expression.

Syntax: ABS(<NUMERIC>)
Function type: Scalar, numeric

The following example applies the ABS function to the ArrDelay column from the flight-carriers datasource.

SELECT
  "ArrDelay" AS "arrival_delay",
  ABS("ArrDelay") AS "absolute_arrival_delay"
FROM "flight-carriers"
WHERE "ArrDelay" < 0
LIMIT 1

Returns the following:

`arrival_delay`	`absolute_arrival_delay`
`-27`	`27`

`OriginState`	`OriginStateName`	`AverageFlightTime`
`AK`	`Alaska`	`113.2777967841259`
`AL`	`Alabama`	`92.28766697732215`
`AR`	`Arkansas`	`95.0391382405745`

`Reporting_Airline`	`Origin`
`AA`	`["AL","AR","AZ","CA","CO","CT","FL","GA","HI","IL","IN","KS","KY","LA","MA","MD","MI","MN","MO","NC","NE","NJ","NM","NV","NY","OH","OK","OR","PA","PR","RI","TN","TX","UT","VA","VI","WA"]`
`AS`	`["AK","AZ","CA","CO","FL","ID","IL","MA","NJ","NV","OR","TX","VA","WA"]`
`B6`	`["AZ","CA","CO","FL","LA","MA","NJ","NV","NY","OR","PR","UT","VA","VT","WA"]`
`CO`	`["AK","AL","AZ","CA","CO","CT","FL","GA","HI","IL","IN","LA","MA","MD","MI","MN","MO","MS","NC","NE","NH","NJ","NM","NV","NY","OH","OK","OR","PA","PR","RI","SC","TN","TX","UT","VA","VI","WA"]`
`DH`	`["AL","CA","CT","FL","GA","IL","MA","ME","MI","NC","NH","NJ","NV","NY","OH","PA","RI","SC","TN","VA","VT","WA","WV"]`

`arrayLong`	`arrayContains`
`[1,null,3]`	`false`
`null`	`null`
`[1,2,3]`	`true`
`[1,2,3]`	`true`
`[]`	`false`

`label`	`arrayLong`	`arrayContains`
`row1`	`[1,null,3]`	`false`
`row2`	`null`	`null`
`row3`	`[1,2,3]`	`true`
`row4`	`[1,2,3]`	`true`
`row5`	`[]`	`false`

`arrayString`	`arrayDouble`	`overlap`
`["a","b"]`	`[1.1,2.2,null]`	false
`[null,"b"]`	`[999,null,5.5]`	true
`[]`	`[null,2.2,1.1]`	false
`["a","b"]`	`[]`	false
`null`	`null`	`null`

`Reporting_Airline`	`StateFipsArray`	`ValueInArray`
`AA`	`[1,4,5,6,8,9,12,13,15,17,18,20,21,22,24,25,26,27,29,31,32,34,35,36,37,39,40,41,42,44,47,48,49,51,53,72,78]`	true
`AS`	`[2,4,6,8,12,16,17,25,32,34,41,48,51,53]`	false
`B6`	`[4,6,8,12,22,25,32,34,36,41,49,50,51,53,72]`	true
`CO`	`[1,2,4,6,8,9,12,13,15,17,18,22,24,25,26,27,28,29,31,32,33,34,35,36,37,39,40,41,42,44,45,47,48,49,51,53,72,78]`	true
`DH`	`[1,6,9,12,13,17,23,25,26,32,33,34,36,37,39,42,44,45,47,50,51,53,54]`	true

`arrayDouble`	`arrayNew`
`[1.1,2.2,null]`	`[1.1,2.2]`
`[999,null,5.5]`	`[999,null]`
`[null,2.2,1.1]`	`[null,2.2]`
`[]`	`[null,null]`
`null`	`null`

`agent_category`	`bloom_test`
`empty`	`false`
`Game console`	`false`
`Personal computer`	`true`
`Smart TV`	`false`
`Smartphone`	`true`
`Tablet`	`false`

`flight_day`	`num_flights`
`2005-11-01T00:00:00.000Z`	`18961`
`2005-11-02T00:00:00.000Z`	`19434`
`2005-11-03T00:00:00.000Z`	`19745`

`flight_day`	`airport`	`airline`	`num_flights`	`cume_dist`
`2005-11-01T00:00:00.000Z`	`KOA`	`HA`	`11`	`0.25`
`2005-11-01T00:00:00.000Z`	`KOA`	`UA`	`4`	`0.5`
`2005-11-01T00:00:00.000Z`	`KOA`	`AA`	`1`	`1`
`2005-11-01T00:00:00.000Z`	`KOA`	`NW`	`1`	`1`
`2005-11-01T00:00:00.000Z`	`LIH`	`HA`	`15`	`0.3333333333333333`
`2005-11-01T00:00:00.000Z`	`LIH`	`AA`	`2`	`1`
`2005-11-01T00:00:00.000Z`	`LIH`	`UA`	`2`	`1`

`user_agent_details`
`["Personal computer","Chrome","76.0.3809.100"]`
`["Smartphone","Chrome Mobile","50.0.2661.89"]`
`["Personal computer","Chrome","76.0.3809.100"]`
`["Personal computer","Opera","62.0.3331.116"]`
`["Smartphone","Mobile Safari","12.0"]`

`array_appended`
`[a, b, c]`
`[null,"b","c"]`
`[c]`
`[a, b, c]`
`null`

`arrayConcatenated`
`[1,null,3,1.1,2.2,null]`
`null`
`[1,2,3,null,2.2,1.1]`
`[1,2,3]`
`null`

`arrayPrepended`
`[c, a, b]`
`["c",null,"b"]`
`[c]`
`[c,a,b]`
`null`

`departure_day`	`origin`
`2005-11-01T00:00:00.000Z`	`LAS`
`2005-11-02T00:00:00.000Z`	`SDF`

`arrival_day`	`origin`
`2005-11-01T00:00:00.000Z`	`RSW`
`2005-11-02T00:00:00.000Z`	`CLE`

`DayOfWeek`	`Subgroup`	`MinutesDelayed`
`1`	`0`	`998505`
`2`	`0`	`1031599`
`3`	`0`	`884677`
`4`	`0`	`525351`
`5`	`0`	`519413`
`6`	`0`	`354601`
`7`	`0`	`848704`
`Total`	`1`	`5162850`

`event`	`percentage`
`{"type":"PercentClear","percentage":55}`	`55`
`{"type":"PercentClear","percentage":80}`	`80`

`event`	`percentage`
`{"type":"PercentClear","percentage":55}`	`[55]`
`{"type":"PercentClear","percentage":80}`	`[80]`

`geo_ip`	`city`
`{"continent":"Asia","country":"Taiwan","region":"Taipei City","city":"Taipei"}`	`Taipei`
`{"continent":"Asia","country":"Thailand","region":"Bangkok","city":"Bangkok"}`	`Bangkok`

`arrival_day`	`origin`
`2005-11-01T00:00:00.000Z`	`MCO`
`2005-11-02T00:00:00.000Z`	`BUF`

`origin_airport`	`full_airport_name`
`SJU`	`Luis Munoz Marin International Airport`
`BOS`	`key not found`

`origin_state`	`add_left_padding`
`Puerto Rico`	`Puerto Rico`
`Massachusetts`	`Massachuset`
`Florida`	`++++Florida`

`tags`	`contained`
`["t1","t2","t3"]`	`true`
`["t3","t4","t5"]`	`true`
`["t5","t6","t7"]`	`false`
`null`	`false`

id: sql-functions title: “All Druid SQL functions” sidebar_label: “All functions”

Example data

ABS

ACOS

ANY_VALUE

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT_BUILTIN

APPROX_COUNT_DISTINCT_DS_HLL

APPROX_COUNT_DISTINCT_DS_THETA

APPROX_QUANTILE

APPROX_QUANTILE_DS

APPROX_QUANTILE_FIXED_BUCKETS

ARRAY

ARRAY_AGG

ARRAY_APPEND

ARRAY_CONCAT

ARRAY_CONCAT_AGG

ARRAY_CONTAINS

Scalar

Array

ARRAY_LENGTH

ARRAY_OFFSET

ARRAY_OFFSET_OF

ARRAY_ORDINAL

ARRAY_ORDINAL_OF

ARRAY_OVERLAP

SCALAR_IN_ARRAY

ARRAY_PREPEND

ARRAY_SLICE

ARRAY_TO_MV

ARRAY_TO_STRING

ASIN

ATAN

ATAN2

AVG

BIT_AND

BIT_OR

BIT_XOR

BITWISE_AND

BITWISE_COMPLEMENT

BITWISE_CONVERT_DOUBLE_TO_LONG_BITS

BITWISE_CONVERT_LONG_BITS_TO_DOUBLE

BITWISE_OR

BITWISE_SHIFT_LEFT

BITWISE_SHIFT_RIGHT

BITWISE_XOR

BLOOM_FILTER

BLOOM_FILTER_TEST

BTRIM

CASE

Simple CASE

Searched CASE

CAST

CEIL

Date and time

Numeric

CHAR_LENGTH

CHARACTER_LENGTH

COALESCE

CONCAT

CONTAINS_STRING

COS

COT

COUNT

CUME_DIST

CURRENT_DATE

CURRENT_TIMESTAMP

DATE_TRUNC

DECODE_BASE64_COMPLEX

DECODE_BASE64_UTF8

DEGREES

DENSE_RANK

DIV

DS_CDF

DS_GET_QUANTILE

DS_GET_QUANTILES

DS_HISTOGRAM

DS_HLL

DS_QUANTILE_SUMMARY

DS_QUANTILES_SKETCH

`tags`	`elem`
`["t1","t2","t3"]`	`t3`
`["t3","t4","t5"]`	`t5`
`["t5","t6","t7"]`	`t7`
`null`	`null`

`tags`	`slice`
`["t1"","t2","t3"]`	`["t2","t3"]`
`["t3"","t4","t5"]`	`["t4","t5"]`
`["t5"","t6","t7"]`	`["t6","t7"]`
`null`	`null`

`original_timestamp`	`time_ceiling`
`2013-08-01T08:14:37.000Z`	`2013-08-01T08:45:00.000Z`
`2013-08-01T09:13:00.000Z`	`2013-08-01T09:30:00.000Z`

`original_timestamp`	`time_floor`
`2013-08-01T08:14:37.000Z`	`2013-08-01T08:00:00.000Z`
`2013-08-01T09:13:00.000Z`	`2013-08-01T08:45:00.000Z`