"use strict";(self.webpackChunk=self.webpackChunk||[]).push([[3121],{15680:(e,n,t)=>{t.d(n,{xA:()=>y,yg:()=>m});var r=t(96540);function a(e,n,t){return n in e?Object.defineProperty(e,n,{value:t,enumerable:!0,configurable:!0,writable:!0}):e[n]=t,e}function i(e,n){var t=Object.keys(e);if(Object.getOwnPropertySymbols){var r=Object.getOwnPropertySymbols(e);n&&(r=r.filter((function(n){return Object.getOwnPropertyDescriptor(e,n).enumerable}))),t.push.apply(t,r)}return t}function l(e){for(var n=1;n<arguments.length;n++){var t=null!=arguments[n]?arguments[n]:{};n%2?i(Object(t),!0).forEach((function(n){a(e,n,t[n])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(t)):i(Object(t)).forEach((function(n){Object.defineProperty(e,n,Object.getOwnPropertyDescriptor(t,n))}))}return e}function o(e,n){if(null==e)return{};var t,r,a=function(e,n){if(null==e)return{};var t,r,a={},i=Object.keys(e);for(r=0;r<i.length;r++)t=i[r],n.indexOf(t)>=0||(a[t]=e[t]);return a}(e,n);if(Object.getOwnPropertySymbols){var i=Object.getOwnPropertySymbols(e);for(r=0;r<i.length;r++)t=i[r],n.indexOf(t)>=0||Object.prototype.propertyIsEnumerable.call(e,t)&&(a[t]=e[t])}return a}var d=r.createContext({}),p=function(e){var n=r.useContext(d),t=n;return e&&(t="function"==typeof e?e(n):l(l({},n),e)),t},y=function(e){var n=p(e.components);return r.createElement(d.Provider,{value:n},e.children)},g="mdxType",s={inlineCode:"code",wrapper:function(e){var n=e.children;return r.createElement(r.Fragment,{},n)}},u=r.forwardRef((function(e,n){var t=e.components,a=e.mdxType,i=e.originalType,d=e.parentName,y=o(e,["components","mdxType","originalType","parentName"]),g=p(t),u=a,m=g["".concat(d,".").concat(u)]||g[u]||s[u]||i;return t?r.createElement(m,l(l({ref:n},y),{},{components:t})):r.createElement(m,l({ref:n},y))}));function m(e,n){var t=arguments,a=n&&n.mdxType;if("string"==typeof e||a){var i=t.length,l=new Array(i);l[0]=u;var o={};for(var d in n)hasOwnProperty.call(n,d)&&(o[d]=n[d]);o.originalType=e,o[g]="string"==typeof e?e:a,l[1]=o;for(var p=2;p<i;p++)l[p]=t[p];return r.createElement.apply(null,l)}return r.createElement.apply(null,t)}u.displayName="MDXCreateElement"},89525:(e,n,t)=>{t.r(n),t.d(n,{assets:()=>y,contentTitle:()=>d,default:()=>m,frontMatter:()=>o,metadata:()=>p,toc:()=>g});var r=t(58168),a=t(98587),i=(t(96540),t(15680)),l=["components"],o={id:"sql-array-functions",title:"SQL ARRAY functions",sidebar_label:"Array functions"},d=void 0,p={unversionedId:"querying/sql-array-functions",id:"querying/sql-array-functions",title:"SQL ARRAY functions",description:"\x3c!--",source:"@site/docs/latest/querying/sql-array-functions.md",sourceDirName:"querying",slug:"/querying/sql-array-functions",permalink:"/docs/latest/querying/sql-array-functions",draft:!1,tags:[],version:"current",frontMatter:{id:"sql-array-functions",title:"SQL ARRAY functions",sidebar_label:"Array functions"},sidebar:"docs",previous:{title:"Window functions",permalink:"/docs/latest/querying/sql-window-functions"},next:{title:"Multi-value string functions",permalink:"/docs/latest/querying/sql-multivalue-string-functions"}},y={},g=[],s={toc:g},u="wrapper";function m(e){var n=e.components,t=(0,a.A)(e,l);return(0,i.yg)(u,(0,r.A)({},s,t,{components:n,mdxType:"MDXLayout"}),(0,i.yg)("admonition",{type:"info"},(0,i.yg)("p",{parentName:"admonition"}," Apache Druid supports two query languages: Druid SQL and ",(0,i.yg)("a",{parentName:"p",href:"/docs/latest/querying/"},"native queries"),".\nThis document describes the SQL language.")),(0,i.yg)("p",null,"This page describes the operations you can perform on arrays using ",(0,i.yg)("a",{parentName:"p",href:"/docs/latest/querying/sql"},"Druid SQL"),". See ",(0,i.yg)("a",{parentName:"p",href:"/docs/latest/querying/sql-data-types#arrays"},(0,i.yg)("inlineCode",{parentName:"a"},"ARRAY")," data type documentation")," for additional details. "),(0,i.yg)("p",null,"All array references in the array function documentation can refer to multi-value string columns or ",(0,i.yg)("inlineCode",{parentName:"p"},"ARRAY")," literals.\nThese functions are largely identical to the ",(0,i.yg)("a",{parentName:"p",href:"/docs/latest/querying/sql-multivalue-string-functions"},"multi-value string functions"),", but\nuse ",(0,i.yg)("inlineCode",{parentName:"p"},"ARRAY")," types and behavior. Multi-value string ",(0,i.yg)("inlineCode",{parentName:"p"},"VARCHAR")," columns can be converted to ",(0,i.yg)("inlineCode",{parentName:"p"},"VARCHAR ARRAY")," to use with\nthese functions using ",(0,i.yg)("inlineCode",{parentName:"p"},"MV_TO_ARRAY"),", and ",(0,i.yg)("inlineCode",{parentName:"p"},"ARRAY")," types can be converted to multi-value string ",(0,i.yg)("inlineCode",{parentName:"p"},"VARCHAR")," with\n",(0,i.yg)("inlineCode",{parentName:"p"},"ARRAY_TO_MV"),"."),(0,i.yg)("p",null,"The following table describes array functions. To learn more about array aggregation functions, see ",(0,i.yg)("a",{parentName:"p",href:"/docs/latest/querying/sql-aggregations"},"SQL aggregation functions"),"."),(0,i.yg)("table",null,(0,i.yg)("thead",{parentName:"table"},(0,i.yg)("tr",{parentName:"thead"},(0,i.yg)("th",{parentName:"tr",align:null},"Function"),(0,i.yg)("th",{parentName:"tr",align:null},"Description"))),(0,i.yg)("tbody",{parentName:"table"},(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY[expr1, expr2, ...]")),(0,i.yg)("td",{parentName:"tr",align:null},"Constructs a SQL ",(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY")," literal from the expression arguments, using the type of the first argument as the output array type.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_LENGTH(arr)")),(0,i.yg)("td",{parentName:"tr",align:null},"Returns length of the array expression.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_OFFSET(arr, long)")),(0,i.yg)("td",{parentName:"tr",align:null},"Returns the array element at the 0-based index supplied, or null for an out of range index.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_ORDINAL(arr, long)")),(0,i.yg)("td",{parentName:"tr",align:null},"Returns the array element at the 1-based index supplied, or null for an out of range index.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_CONTAINS(arr, expr)")),(0,i.yg)("td",{parentName:"tr",align:null},"If ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," is a scalar type, returns 1 if ",(0,i.yg)("inlineCode",{parentName:"td"},"arr")," contains ",(0,i.yg)("inlineCode",{parentName:"td"},"expr"),". If ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," is an array, returns 1 if ",(0,i.yg)("inlineCode",{parentName:"td"},"arr")," contains all elements of ",(0,i.yg)("inlineCode",{parentName:"td"},"expr"),". Otherwise returns 0.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_OVERLAP(arr1, arr2)")),(0,i.yg)("td",{parentName:"tr",align:null},"Returns 1 if ",(0,i.yg)("inlineCode",{parentName:"td"},"arr1")," and ",(0,i.yg)("inlineCode",{parentName:"td"},"arr2")," have any elements in common, else 0.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_OFFSET_OF(arr, expr)")),(0,i.yg)("td",{parentName:"tr",align:null},"Returns the 0-based index of the first occurrence of ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," in the array. If no matching elements exist in the array, returns ",(0,i.yg)("inlineCode",{parentName:"td"},"null")," or ",(0,i.yg)("inlineCode",{parentName:"td"},"-1")," if ",(0,i.yg)("inlineCode",{parentName:"td"},"druid.generic.useDefaultValueForNull=true")," (deprecated legacy mode).")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_ORDINAL_OF(arr, expr)")),(0,i.yg)("td",{parentName:"tr",align:null},"Returns the 1-based index of the first occurrence of ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," in the array. If no matching elements exist in the array, returns ",(0,i.yg)("inlineCode",{parentName:"td"},"null")," or ",(0,i.yg)("inlineCode",{parentName:"td"},"-1")," if ",(0,i.yg)("inlineCode",{parentName:"td"},"druid.generic.useDefaultValueForNull=true")," (deprecated legacy mode).")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_PREPEND(expr, arr)")),(0,i.yg)("td",{parentName:"tr",align:null},"Adds ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," to the beginning of ",(0,i.yg)("inlineCode",{parentName:"td"},"arr"),", the resulting array type determined by the type of ",(0,i.yg)("inlineCode",{parentName:"td"},"arr"),".")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_APPEND(arr, expr)")),(0,i.yg)("td",{parentName:"tr",align:null},"Appends ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," to ",(0,i.yg)("inlineCode",{parentName:"td"},"arr"),", the resulting array type determined by the type of ",(0,i.yg)("inlineCode",{parentName:"td"},"arr"),".")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_CONCAT(arr1, arr2)")),(0,i.yg)("td",{parentName:"tr",align:null},"Concatenates ",(0,i.yg)("inlineCode",{parentName:"td"},"arr2")," to ",(0,i.yg)("inlineCode",{parentName:"td"},"arr1"),". The resulting array type is determined by the type of ",(0,i.yg)("inlineCode",{parentName:"td"},"arr1"),".")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_SLICE(arr, start, end)")),(0,i.yg)("td",{parentName:"tr",align:null},"Returns the subarray of ",(0,i.yg)("inlineCode",{parentName:"td"},"arr")," from the 0-based index ",(0,i.yg)("inlineCode",{parentName:"td"},"start")," (inclusive) to ",(0,i.yg)("inlineCode",{parentName:"td"},"end")," (exclusive). Returns ",(0,i.yg)("inlineCode",{parentName:"td"},"null"),", if ",(0,i.yg)("inlineCode",{parentName:"td"},"start")," is less than 0, greater than length of ",(0,i.yg)("inlineCode",{parentName:"td"},"arr"),", or greater than ",(0,i.yg)("inlineCode",{parentName:"td"},"end"),".")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_TO_STRING(arr, str)")),(0,i.yg)("td",{parentName:"tr",align:null},"Joins all elements of ",(0,i.yg)("inlineCode",{parentName:"td"},"arr")," by the delimiter specified by ",(0,i.yg)("inlineCode",{parentName:"td"},"str"),".")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"STRING_TO_ARRAY(str1, str2)")),(0,i.yg)("td",{parentName:"tr",align:null},"Splits ",(0,i.yg)("inlineCode",{parentName:"td"},"str1")," into an array on the delimiter specified by ",(0,i.yg)("inlineCode",{parentName:"td"},"str2"),", which is a regular expression.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY_TO_MV(arr)")),(0,i.yg)("td",{parentName:"tr",align:null},"Converts an ",(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY")," of any type into a multi-value string ",(0,i.yg)("inlineCode",{parentName:"td"},"VARCHAR"),".")))))}m.isMDXComponent=!0}}]);