| "use strict";(self.webpackChunk=self.webpackChunk||[]).push([[2758],{3905:(e,a,n)=>{n.d(a,{Zo:()=>u,kt:()=>y});var r=n(67294);function t(e,a,n){return a in e?Object.defineProperty(e,a,{value:n,enumerable:!0,configurable:!0,writable:!0}):e[a]=n,e}function i(e,a){var n=Object.keys(e);if(Object.getOwnPropertySymbols){var r=Object.getOwnPropertySymbols(e);a&&(r=r.filter((function(a){return Object.getOwnPropertyDescriptor(e,a).enumerable}))),n.push.apply(n,r)}return n}function l(e){for(var a=1;a<arguments.length;a++){var n=null!=arguments[a]?arguments[a]:{};a%2?i(Object(n),!0).forEach((function(a){t(e,a,n[a])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(n)):i(Object(n)).forEach((function(a){Object.defineProperty(e,a,Object.getOwnPropertyDescriptor(n,a))}))}return e}function o(e,a){if(null==e)return{};var n,r,t=function(e,a){if(null==e)return{};var n,r,t={},i=Object.keys(e);for(r=0;r<i.length;r++)n=i[r],a.indexOf(n)>=0||(t[n]=e[n]);return t}(e,a);if(Object.getOwnPropertySymbols){var i=Object.getOwnPropertySymbols(e);for(r=0;r<i.length;r++)n=i[r],a.indexOf(n)>=0||Object.prototype.propertyIsEnumerable.call(e,n)&&(t[n]=e[n])}return t}var s=r.createContext({}),p=function(e){var a=r.useContext(s),n=a;return e&&(n="function"==typeof e?e(a):l(l({},a),e)),n},u=function(e){var a=p(e.components);return r.createElement(s.Provider,{value:a},e.children)},m="mdxType",d={inlineCode:"code",wrapper:function(e){var a=e.children;return r.createElement(r.Fragment,{},a)}},g=r.forwardRef((function(e,a){var n=e.components,t=e.mdxType,i=e.originalType,s=e.parentName,u=o(e,["components","mdxType","originalType","parentName"]),m=p(n),g=t,y=m["".concat(s,".").concat(g)]||m[g]||d[g]||i;return n?r.createElement(y,l(l({ref:a},u),{},{components:n})):r.createElement(y,l({ref:a},u))}));function y(e,a){var n=arguments,t=a&&a.mdxType;if("string"==typeof e||t){var i=n.length,l=new Array(i);l[0]=g;var o={};for(var s in a)hasOwnProperty.call(a,s)&&(o[s]=a[s]);o.originalType=e,o[m]="string"==typeof e?e:t,l[1]=o;for(var p=2;p<i;p++)l[p]=n[p];return r.createElement.apply(null,l)}return r.createElement.apply(null,n)}g.displayName="MDXCreateElement"},81266:(e,a,n)=>{n.r(a),n.d(a,{assets:()=>u,contentTitle:()=>s,default:()=>y,frontMatter:()=>o,metadata:()=>p,toc:()=>m});var r=n(87462),t=n(63366),i=(n(67294),n(3905)),l=["components"],o={id:"arrays",title:"Arrays"},s=void 0,p={unversionedId:"querying/arrays",id:"querying/arrays",title:"Arrays",description:"\x3c!--",source:"@site/docs/29.0.1/querying/arrays.md",sourceDirName:"querying",slug:"/querying/arrays",permalink:"/docs/29.0.1/querying/arrays",draft:!1,tags:[],version:"current",frontMatter:{id:"arrays",title:"Arrays"},sidebar:"docs",previous:{title:"Multi-value dimensions",permalink:"/docs/29.0.1/querying/multi-value-dimensions"},next:{title:"Nested columns",permalink:"/docs/29.0.1/querying/nested-columns"}},u={},m=[{value:"Ingesting arrays",id:"ingesting-arrays",level:2},{value:"Native batch and streaming ingestion",id:"native-batch-and-streaming-ingestion",level:3},{value:"SQL-based ingestion",id:"sql-based-ingestion",level:3},{value:"<code>arrayIngestMode</code>",id:"arrayingestmode",level:4},{value:"Examples",id:"examples",level:4},{value:"Querying arrays",id:"querying-arrays",level:2},{value:"Filtering",id:"filtering",level:3},{value:"Example: equality",id:"example-equality",level:4},{value:"Example: null",id:"example-null",level:4},{value:"Example: range",id:"example-range",level:4},{value:"Example: ARRAY_CONTAINS",id:"example-array_contains",level:4},{value:"Grouping",id:"grouping",level:3},{value:"Example: SQL grouping query with no filtering",id:"example-sql-grouping-query-with-no-filtering",level:4},{value:"Example: SQL grouping query with a filter",id:"example-sql-grouping-query-with-a-filter",level:4},{value:"Example: UNNEST",id:"example-unnest",level:4},{value:"Differences between arrays and multi-value dimensions",id:"differences-between-arrays-and-multi-value-dimensions",level:2}],d={toc:m},g="wrapper";function y(e){var a=e.components,n=(0,t.Z)(e,l);return(0,i.kt)(g,(0,r.Z)({},d,n,{components:a,mdxType:"MDXLayout"}),(0,i.kt)("p",null,"Apache Druid supports SQL standard ",(0,i.kt)("inlineCode",{parentName:"p"},"ARRAY")," typed columns for ",(0,i.kt)("inlineCode",{parentName:"p"},"VARCHAR"),", ",(0,i.kt)("inlineCode",{parentName:"p"},"BIGINT"),", and ",(0,i.kt)("inlineCode",{parentName:"p"},"DOUBLE")," types (native types ",(0,i.kt)("inlineCode",{parentName:"p"},"ARRAY<STRING>"),", ",(0,i.kt)("inlineCode",{parentName:"p"},"ARRAY<LONG>"),", and ",(0,i.kt)("inlineCode",{parentName:"p"},"ARRAY<DOUBLE>"),"). Other more complicated ARRAY types must be stored in ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/nested-columns"},"nested columns"),". Druid ARRAY types are distinct from ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/multi-value-dimensions"},"multi-value dimension"),", which have significantly different behavior than standard arrays."),(0,i.kt)("p",null,"This document describes inserting, filtering, and grouping behavior for ",(0,i.kt)("inlineCode",{parentName:"p"},"ARRAY")," typed columns.\nRefer to the ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/sql-data-types#arrays"},"Druid SQL data type documentation")," and ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/sql-array-functions"},"SQL array function reference")," for additional details\nabout the functions available to use with ARRAY columns and types in SQL."),(0,i.kt)("p",null,"The following sections describe inserting, filtering, and grouping behavior based on the following example data, which includes 3 array typed columns:"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-json",metastring:"lines",lines:!0},'{"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}\n')),(0,i.kt)("h2",{id:"ingesting-arrays"},"Ingesting arrays"),(0,i.kt)("h3",{id:"native-batch-and-streaming-ingestion"},"Native batch and streaming ingestion"),(0,i.kt)("p",null,"When using native ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/ingestion/native-batch"},"batch")," or streaming ingestion such as with ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/ingestion/kafka-ingestion"},"Apache Kafka"),", arrays can be ingested using the ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/ingestion/ingestion-spec#dimension-objects"},(0,i.kt)("inlineCode",{parentName:"a"},'"auto"'))," type dimension schema which is shared with ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/ingestion/schema-design#type-aware-schema-discovery"},"type-aware schema discovery"),"."),(0,i.kt)("p",null,"When ingesting from TSV or CSV data, you can specify the array delimiters using the ",(0,i.kt)("inlineCode",{parentName:"p"},"listDelimiter")," field in the ",(0,i.kt)("inlineCode",{parentName:"p"},"inputFormat"),". JSON data must be formatted as a JSON array to be ingested as an array type. JSON data does not require ",(0,i.kt)("inlineCode",{parentName:"p"},"inputFormat")," configuration."),(0,i.kt)("p",null,"The following shows an example ",(0,i.kt)("inlineCode",{parentName:"p"},"dimensionsSpec")," for native ingestion of the data used in this document:"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre"},'"dimensions": [\n {\n "type": "auto",\n "name": "label"\n },\n {\n "type": "auto",\n "name": "arrayString"\n },\n {\n "type": "auto",\n "name": "arrayLong"\n },\n {\n "type": "auto",\n "name": "arrayDouble"\n }\n],\n')),(0,i.kt)("h3",{id:"sql-based-ingestion"},"SQL-based ingestion"),(0,i.kt)("h4",{id:"arrayingestmode"},(0,i.kt)("inlineCode",{parentName:"h4"},"arrayIngestMode")),(0,i.kt)("p",null,"Arrays can be inserted with ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/multi-stage-query/"},"SQL-based ingestion")," when you include the query context\nparameter ",(0,i.kt)("inlineCode",{parentName:"p"},"arrayIngestMode: array"),"."),(0,i.kt)("p",null,"When ",(0,i.kt)("inlineCode",{parentName:"p"},"arrayIngestMode")," is ",(0,i.kt)("inlineCode",{parentName:"p"},"array"),", SQL ARRAY types are stored using Druid array columns. This is recommended for new\ntables."),(0,i.kt)("p",null,"When ",(0,i.kt)("inlineCode",{parentName:"p"},"arrayIngestMode")," is ",(0,i.kt)("inlineCode",{parentName:"p"},"mvd"),", SQL ",(0,i.kt)("inlineCode",{parentName:"p"},"VARCHAR ARRAY")," are implicitly wrapped in ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/sql-functions#array_to_mv"},(0,i.kt)("inlineCode",{parentName:"a"},"ARRAY_TO_MV")),".\nThis causes them to be stored as ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/multi-value-dimensions"},"multi-value strings"),", using the same ",(0,i.kt)("inlineCode",{parentName:"p"},"STRING")," column type\nas regular scalar strings. SQL ",(0,i.kt)("inlineCode",{parentName:"p"},"BIGINT ARRAY")," and ",(0,i.kt)("inlineCode",{parentName:"p"},"DOUBLE ARRAY")," cannot be loaded under ",(0,i.kt)("inlineCode",{parentName:"p"},"arrayIngestMode: mvd"),". This\nis the default behavior when ",(0,i.kt)("inlineCode",{parentName:"p"},"arrayIngestMode")," is not provided in your query context, although the default behavior\nmay change to ",(0,i.kt)("inlineCode",{parentName:"p"},"array")," in a future release."),(0,i.kt)("p",null,"When ",(0,i.kt)("inlineCode",{parentName:"p"},"arrayIngestMode")," is ",(0,i.kt)("inlineCode",{parentName:"p"},"none"),", Druid throws an exception when trying to store any type of arrays. This mode is most\nuseful when set in the system default query context with ",(0,i.kt)("inlineCode",{parentName:"p"},"druid.query.default.context.arrayIngestMode = none"),", in cases\nwhere the cluster administrator wants SQL query authors to explicitly provide one or the other in their query context."),(0,i.kt)("p",null,"The following table summarizes the differences in SQL ARRAY handling between ",(0,i.kt)("inlineCode",{parentName:"p"},"arrayIngestMode: array")," and\n",(0,i.kt)("inlineCode",{parentName:"p"},"arrayIngestMode: mvd"),"."),(0,i.kt)("table",null,(0,i.kt)("thead",{parentName:"table"},(0,i.kt)("tr",{parentName:"thead"},(0,i.kt)("th",{parentName:"tr",align:null},"SQL type"),(0,i.kt)("th",{parentName:"tr",align:null},"Stored type when ",(0,i.kt)("inlineCode",{parentName:"th"},"arrayIngestMode: array")),(0,i.kt)("th",{parentName:"tr",align:null},"Stored type when ",(0,i.kt)("inlineCode",{parentName:"th"},"arrayIngestMode: mvd")," (default)"))),(0,i.kt)("tbody",{parentName:"table"},(0,i.kt)("tr",{parentName:"tbody"},(0,i.kt)("td",{parentName:"tr",align:null},(0,i.kt)("inlineCode",{parentName:"td"},"VARCHAR ARRAY")),(0,i.kt)("td",{parentName:"tr",align:null},(0,i.kt)("inlineCode",{parentName:"td"},"ARRAY<STRING>")),(0,i.kt)("td",{parentName:"tr",align:null},(0,i.kt)("a",{parentName:"td",href:"/docs/29.0.1/querying/multi-value-dimensions"},"multi-value ",(0,i.kt)("inlineCode",{parentName:"a"},"STRING")))),(0,i.kt)("tr",{parentName:"tbody"},(0,i.kt)("td",{parentName:"tr",align:null},(0,i.kt)("inlineCode",{parentName:"td"},"BIGINT ARRAY")),(0,i.kt)("td",{parentName:"tr",align:null},(0,i.kt)("inlineCode",{parentName:"td"},"ARRAY<LONG>")),(0,i.kt)("td",{parentName:"tr",align:null},"not possible (validation error)")),(0,i.kt)("tr",{parentName:"tbody"},(0,i.kt)("td",{parentName:"tr",align:null},(0,i.kt)("inlineCode",{parentName:"td"},"DOUBLE ARRAY")),(0,i.kt)("td",{parentName:"tr",align:null},(0,i.kt)("inlineCode",{parentName:"td"},"ARRAY<DOUBLE>")),(0,i.kt)("td",{parentName:"tr",align:null},"not possible (validation error)")))),(0,i.kt)("p",null,"In either mode, you can explicitly wrap string arrays in ",(0,i.kt)("inlineCode",{parentName:"p"},"ARRAY_TO_MV")," to cause them to be stored as\n",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/multi-value-dimensions"},"multi-value strings"),"."),(0,i.kt)("p",null,"When validating a SQL INSERT or REPLACE statement that contains arrays, Druid checks whether the statement would lead\nto mixing string arrays and multi-value strings in the same column. If this condition is detected, the statement fails\nvalidation unless the column is named under the ",(0,i.kt)("inlineCode",{parentName:"p"},"skipTypeVerification")," context parameter. This parameter can be either\na comma-separated list of column names, or a JSON array in string form. This validation is done to prevent accidentally\nmixing arrays and multi-value strings in the same column."),(0,i.kt)("h4",{id:"examples"},"Examples"),(0,i.kt)("p",null,"Set ",(0,i.kt)("a",{parentName:"p",href:"#arrayingestmode"},(0,i.kt)("inlineCode",{parentName:"a"},"arrayIngestMode: array"))," in your query context to run the following examples."),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-sql"},'REPLACE INTO "array_example" OVERWRITE ALL\nWITH "ext" AS (\n SELECT *\n FROM TABLE(\n EXTERN(\n \'{"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}"}\',\n \'{"type":"json"}\'\n )\n ) EXTEND (\n "timestamp" VARCHAR,\n "label" VARCHAR,\n "arrayString" VARCHAR ARRAY,\n "arrayLong" BIGINT ARRAY,\n "arrayDouble" DOUBLE ARRAY\n )\n)\nSELECT\n TIME_PARSE("timestamp") AS "__time",\n "label",\n "arrayString",\n "arrayLong",\n "arrayDouble"\nFROM "ext"\nPARTITIONED BY DAY\n')),(0,i.kt)("p",null,"Arrays can also be used as ",(0,i.kt)("inlineCode",{parentName:"p"},"GROUP BY")," keys for rollup:"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-sql"},'REPLACE INTO "array_example_rollup" OVERWRITE ALL\nWITH "ext" AS (\n SELECT *\n FROM TABLE(\n EXTERN(\n \'{"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}"}\',\n \'{"type":"json"}\'\n )\n ) EXTEND (\n "timestamp" VARCHAR,\n "label" VARCHAR,\n "arrayString" VARCHAR ARRAY,\n "arrayLong" BIGINT ARRAY,\n "arrayDouble" DOUBLE ARRAY\n )\n)\nSELECT\n TIME_PARSE("timestamp") AS "__time",\n "label",\n "arrayString",\n "arrayLong",\n "arrayDouble",\n COUNT(*) as "count"\nFROM "ext"\nGROUP BY 1,2,3,4,5\nPARTITIONED BY DAY\n')),(0,i.kt)("h2",{id:"querying-arrays"},"Querying arrays"),(0,i.kt)("h3",{id:"filtering"},"Filtering"),(0,i.kt)("p",null,"All query types, as well as ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/aggregations#filtered-aggregator"},"filtered aggregators"),", can filter on array typed columns. Filters follow these rules for array types:"),(0,i.kt)("ul",null,(0,i.kt)("li",{parentName:"ul"},"All filters match against the entire array value for the row"),(0,i.kt)("li",{parentName:"ul"},"Native value filters like ",(0,i.kt)("a",{parentName:"li",href:"/docs/29.0.1/querying/filters#equality-filter"},"equality")," and ",(0,i.kt)("a",{parentName:"li",href:"/docs/29.0.1/querying/filters#range-filter"},"range")," match on entire array values, as do SQL constructs that plan into these native filters"),(0,i.kt)("li",{parentName:"ul"},"The ",(0,i.kt)("a",{parentName:"li",href:"/docs/29.0.1/querying/filters#null-filter"},(0,i.kt)("inlineCode",{parentName:"a"},"IS NULL"))," filter will match rows where the entire array value is null"),(0,i.kt)("li",{parentName:"ul"},(0,i.kt)("a",{parentName:"li",href:"/docs/29.0.1/querying/sql-array-functions"},"Array specific functions")," like ",(0,i.kt)("inlineCode",{parentName:"li"},"ARRAY_CONTAINS")," and ",(0,i.kt)("inlineCode",{parentName:"li"},"ARRAY_OVERLAP")," follow the behavior specified by those functions"),(0,i.kt)("li",{parentName:"ul"},"All other filters do not directly support ARRAY types and will result in a query error")),(0,i.kt)("h4",{id:"example-equality"},"Example: equality"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-sql"},'SELECT *\nFROM "array_example"\nWHERE arrayLong = ARRAY[1,2,3]\n')),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-json",metastring:"lines",lines:!0},'{"__time":"2023-01-01T00:00:00.000Z","label":"row3","arrayString":"[]","arrayLong":"[1,2,3]","arrayDouble":"[null,2.2,1.1]"}\n{"__time":"2023-01-01T00:00:00.000Z","label":"row4","arrayString":"[\\"a\\",\\"b\\"]","arrayLong":"[1,2,3]","arrayDouble":"[]"}\n')),(0,i.kt)("h4",{id:"example-null"},"Example: null"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-sql"},'SELECT *\nFROM "array_example"\nWHERE arrayLong IS NULL\n')),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-json",metastring:"lines",lines:!0},'{"__time":"2023-01-01T00:00:00.000Z","label":"row2","arrayString":"[null,\\"b\\"]","arrayLong":null,"arrayDouble":"[999.0,null,5.5]"}\n')),(0,i.kt)("h4",{id:"example-range"},"Example: range"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-sql"},"SELECT *\nFROM \"array_example\"\nWHERE arrayString >= ARRAY['a','b']\n")),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-json",metastring:"lines",lines:!0},'{"__time":"2023-01-01T00:00:00.000Z","label":"row1","arrayString":"[\\"a\\",\\"b\\"]","arrayLong":"[1,null,3]","arrayDouble":"[1.1,2.2,null]"}\n{"__time":"2023-01-01T00:00:00.000Z","label":"row4","arrayString":"[\\"a\\",\\"b\\"]","arrayLong":"[1,2,3]","arrayDouble":"[]"}\n')),(0,i.kt)("h4",{id:"example-array_contains"},"Example: ARRAY_CONTAINS"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-sql"},"SELECT *\nFROM \"array_example\"\nWHERE ARRAY_CONTAINS(arrayString, 'a')\n")),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-json",metastring:"lines",lines:!0},'{"__time":"2023-01-01T00:00:00.000Z","label":"row1","arrayString":"[\\"a\\",\\"b\\"]","arrayLong":"[1,null,3]","arrayDouble":"[1.1,2.2,null]"}\n{"__time":"2023-01-01T00:00:00.000Z","label":"row4","arrayString":"[\\"a\\",\\"b\\"]","arrayLong":"[1,2,3]","arrayDouble":"[]"}\n')),(0,i.kt)("h3",{id:"grouping"},"Grouping"),(0,i.kt)("p",null,"When grouping on an array with SQL or a native ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/groupbyquery"},"groupBy query"),", grouping follows standard SQL behavior and groups on the entire array as a single value. The ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/sql#unnest"},(0,i.kt)("inlineCode",{parentName:"a"},"UNNEST"))," function allows grouping on the individual array elements."),(0,i.kt)("h4",{id:"example-sql-grouping-query-with-no-filtering"},"Example: SQL grouping query with no filtering"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-sql"},'SELECT label, arrayString\nFROM "array_example"\nGROUP BY 1,2\n')),(0,i.kt)("p",null,"results in:"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-json",metastring:"lines",lines:!0},'{"label":"row1","arrayString":"[\\"a\\",\\"b\\"]"}\n{"label":"row2","arrayString":"[null,\\"b\\"]"}\n{"label":"row3","arrayString":"[]"}\n{"label":"row4","arrayString":"[\\"a\\",\\"b\\"]"}\n{"label":"row5","arrayString":null}\n')),(0,i.kt)("h4",{id:"example-sql-grouping-query-with-a-filter"},"Example: SQL grouping query with a filter"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-sql"},'SELECT label, arrayString\nFROM "array_example"\nWHERE arrayLong = ARRAY[1,2,3]\nGROUP BY 1,2\n')),(0,i.kt)("p",null,"results:"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-json",metastring:"lines",lines:!0},'{"label":"row3","arrayString":"[]"}\n{"label":"row4","arrayString":"[\\"a\\",\\"b\\"]"}\n')),(0,i.kt)("h4",{id:"example-unnest"},"Example: UNNEST"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-sql"},'SELECT label, strings\nFROM "array_example" CROSS JOIN UNNEST(arrayString) as u(strings)\nGROUP BY 1,2\n')),(0,i.kt)("p",null,"results:"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-json",metastring:"lines",lines:!0},'{"label":"row1","strings":"a"}\n{"label":"row1","strings":"b"}\n{"label":"row2","strings":null}\n{"label":"row2","strings":"b"}\n{"label":"row4","strings":"a"}\n{"label":"row4","strings":"b"}\n')),(0,i.kt)("h2",{id:"differences-between-arrays-and-multi-value-dimensions"},"Differences between arrays and multi-value dimensions"),(0,i.kt)("p",null,"Avoid confusing string arrays with ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/multi-value-dimensions"},"multi-value dimensions"),". Arrays and multi-value dimensions are stored in different column types, and query behavior is different. You can use the functions ",(0,i.kt)("inlineCode",{parentName:"p"},"MV_TO_ARRAY")," and ",(0,i.kt)("inlineCode",{parentName:"p"},"ARRAY_TO_MV")," to convert between the two if needed. In general, we recommend using arrays whenever possible, since they are a newer and more powerful feature and have SQL compliant behavior."),(0,i.kt)("p",null,"Use care during ingestion to ensure you get the type you want."),(0,i.kt)("p",null,"To get arrays when performing an ingestion using JSON ingestion specs, such as ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/ingestion/native-batch"},"native batch")," or streaming ingestion such as with ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/ingestion/kafka-ingestion"},"Apache Kafka"),", use dimension type ",(0,i.kt)("inlineCode",{parentName:"p"},"auto")," or enable ",(0,i.kt)("inlineCode",{parentName:"p"},"useSchemaDiscovery"),". When performing a ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/multi-stage-query/"},"SQL-based ingestion"),", write a query that generates arrays and set the context parameter ",(0,i.kt)("inlineCode",{parentName:"p"},'"arrayIngestMode": "array"'),". Arrays may contain strings or numbers."),(0,i.kt)("p",null,"To get multi-value dimensions when performing an ingestion using JSON ingestion specs, use dimension type ",(0,i.kt)("inlineCode",{parentName:"p"},"string")," and do not enable ",(0,i.kt)("inlineCode",{parentName:"p"},"useSchemaDiscovery"),". When performing a ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/multi-stage-query/"},"SQL-based ingestion"),", wrap arrays in ",(0,i.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/multi-value-dimensions#sql-based-ingestion"},(0,i.kt)("inlineCode",{parentName:"a"},"ARRAY_TO_MV")),", which ensures you get multi-value dimensions in any ",(0,i.kt)("inlineCode",{parentName:"p"},"arrayIngestMode"),". Multi-value dimensions can only contain strings."),(0,i.kt)("p",null,"You can tell which type you have by checking the ",(0,i.kt)("inlineCode",{parentName:"p"},"INFORMATION_SCHEMA.COLUMNS")," table, using a query like:"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-sql"},"SELECT COLUMN_NAME, DATA_TYPE\nFROM INFORMATION_SCHEMA.COLUMNS\nWHERE TABLE_NAME = 'mytable'\n")),(0,i.kt)("p",null,"Arrays are type ",(0,i.kt)("inlineCode",{parentName:"p"},"ARRAY"),", multi-value strings are type ",(0,i.kt)("inlineCode",{parentName:"p"},"VARCHAR"),"."))}y.isMDXComponent=!0}}]); |