"use strict";(self.webpackChunk=self.webpackChunk||[]).push([[9198],{15680:(e,n,t)=>{t.d(n,{xA:()=>g,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},g=function(e){var n=p(e.components);return r.createElement(d.Provider,{value:n},e.children)},u="mdxType",y={inlineCode:"code",wrapper:function(e){var n=e.children;return r.createElement(r.Fragment,{},n)}},s=r.forwardRef((function(e,n){var t=e.components,a=e.mdxType,i=e.originalType,d=e.parentName,g=o(e,["components","mdxType","originalType","parentName"]),u=p(t),s=a,m=u["".concat(d,".").concat(s)]||u[s]||y[s]||i;return t?r.createElement(m,l(l({ref:n},g),{},{components:t})):r.createElement(m,l({ref:n},g))}));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]=s;var o={};for(var d in n)hasOwnProperty.call(n,d)&&(o[d]=n[d]);o.originalType=e,o[u]="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)}s.displayName="MDXCreateElement"},18906:(e,n,t)=>{t.r(n),t.d(n,{assets:()=>g,contentTitle:()=>d,default:()=>m,frontMatter:()=>o,metadata:()=>p,toc:()=>u});var r=t(58168),a=t(98587),i=(t(96540),t(15680)),l=["components"],o={id:"sql-multivalue-string-functions",title:"SQL multi-value string functions",sidebar_label:"Multi-value string functions"},d=void 0,p={unversionedId:"querying/sql-multivalue-string-functions",id:"querying/sql-multivalue-string-functions",title:"SQL multi-value string functions",description:"\x3c!--",source:"@site/docs/29.0.0/querying/sql-multivalue-string-functions.md",sourceDirName:"querying",slug:"/querying/sql-multivalue-string-functions",permalink:"/docs/29.0.0/querying/sql-multivalue-string-functions",draft:!1,tags:[],version:"current",frontMatter:{id:"sql-multivalue-string-functions",title:"SQL multi-value string functions",sidebar_label:"Multi-value string functions"},sidebar:"docs",previous:{title:"Array functions",permalink:"/docs/29.0.0/querying/sql-array-functions"},next:{title:"JSON functions",permalink:"/docs/29.0.0/querying/sql-json-functions"}},g={},u=[],y={toc:u},s="wrapper";function m(e){var n=e.components,t=(0,a.A)(e,l);return(0,i.yg)(s,(0,r.A)({},y,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/29.0.0/querying/"},"native queries"),".\nThis document describes the SQL language.")),(0,i.yg)("p",null,"Druid supports string dimensions containing multiple values.\nThis page describes the operations you can perform on multi-value string dimensions using ",(0,i.yg)("a",{parentName:"p",href:"/docs/29.0.0/querying/sql"},"Druid SQL"),".\nSee ",(0,i.yg)("a",{parentName:"p",href:"/docs/29.0.0/querying/sql-data-types#multi-value-strings"},"SQL multi-value strings")," and native ",(0,i.yg)("a",{parentName:"p",href:"/docs/29.0.0/querying/multi-value-dimensions"},"Multi-value dimensions")," for more information."),(0,i.yg)("p",null,"All array references in the multi-value string function documentation can refer to multi-value string columns or\n",(0,i.yg)("inlineCode",{parentName:"p"},"ARRAY")," types. These functions are largely identical to the ",(0,i.yg)("a",{parentName:"p",href:"/docs/29.0.0/querying/sql-array-functions"},"array functions"),", but use\n",(0,i.yg)("inlineCode",{parentName:"p"},"VARCHAR")," types and behavior. Multi-value strings can also be converted to ",(0,i.yg)("inlineCode",{parentName:"p"},"ARRAY")," types using ",(0,i.yg)("inlineCode",{parentName:"p"},"MV_TO_ARRAY"),", and\n",(0,i.yg)("inlineCode",{parentName:"p"},"ARRAY")," into multi-value strings via ",(0,i.yg)("inlineCode",{parentName:"p"},"ARRAY_TO_MV"),". For additional details about ",(0,i.yg)("inlineCode",{parentName:"p"},"ARRAY")," types, see\n",(0,i.yg)("a",{parentName:"p",href:"/docs/29.0.0/querying/sql-data-types#arrays"},(0,i.yg)("inlineCode",{parentName:"a"},"ARRAY")," data type documentation"),"."),(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"},"MV_FILTER_ONLY(expr, arr)")),(0,i.yg)("td",{parentName:"tr",align:null},"Filters multi-value ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," to include only values contained in array ",(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"},"MV_FILTER_NONE(expr, arr)")),(0,i.yg)("td",{parentName:"tr",align:null},"Filters multi-value ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," to include no values contained in array ",(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"},"MV_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"},"MV_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"},"MV_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"},"MV_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"},"MV_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"},"MV_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 -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"},"MV_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"},"MV_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 ",(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"},"MV_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"},"MV_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"},"MV_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 start(inclusive) to end(exclusive), or ",(0,i.yg)("inlineCode",{parentName:"td"},"null"),", if start is less than 0, greater than length of arr or greater than end.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"MV_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_MV(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"},"MV_TO_ARRAY(str)")),(0,i.yg)("td",{parentName:"tr",align:null},"Converts a multi-value string from a ",(0,i.yg)("inlineCode",{parentName:"td"},"VARCHAR")," to a ",(0,i.yg)("inlineCode",{parentName:"td"},"VARCHAR ARRAY"),".")))))}m.isMDXComponent=!0}}]);