"use strict";(self.webpackChunk=self.webpackChunk||[]).push([[7132],{3905:(e,t,n)=>{n.d(t,{Zo:()=>p,kt:()=>k});var a=n(67294);function l(e,t,n){return t in e?Object.defineProperty(e,t,{value:n,enumerable:!0,configurable:!0,writable:!0}):e[t]=n,e}function r(e,t){var n=Object.keys(e);if(Object.getOwnPropertySymbols){var a=Object.getOwnPropertySymbols(e);t&&(a=a.filter((function(t){return Object.getOwnPropertyDescriptor(e,t).enumerable}))),n.push.apply(n,a)}return n}function i(e){for(var t=1;t<arguments.length;t++){var n=null!=arguments[t]?arguments[t]:{};t%2?r(Object(n),!0).forEach((function(t){l(e,t,n[t])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(n)):r(Object(n)).forEach((function(t){Object.defineProperty(e,t,Object.getOwnPropertyDescriptor(n,t))}))}return e}function o(e,t){if(null==e)return{};var n,a,l=function(e,t){if(null==e)return{};var n,a,l={},r=Object.keys(e);for(a=0;a<r.length;a++)n=r[a],t.indexOf(n)>=0||(l[n]=e[n]);return l}(e,t);if(Object.getOwnPropertySymbols){var r=Object.getOwnPropertySymbols(e);for(a=0;a<r.length;a++)n=r[a],t.indexOf(n)>=0||Object.prototype.propertyIsEnumerable.call(e,n)&&(l[n]=e[n])}return l}var m=a.createContext({}),u=function(e){var t=a.useContext(m),n=t;return e&&(n="function"==typeof e?e(t):i(i({},t),e)),n},p=function(e){var t=u(e.components);return a.createElement(m.Provider,{value:t},e.children)},s="mdxType",d={inlineCode:"code",wrapper:function(e){var t=e.children;return a.createElement(a.Fragment,{},t)}},c=a.forwardRef((function(e,t){var n=e.components,l=e.mdxType,r=e.originalType,m=e.parentName,p=o(e,["components","mdxType","originalType","parentName"]),s=u(n),c=l,k=s["".concat(m,".").concat(c)]||s[c]||d[c]||r;return n?a.createElement(k,i(i({ref:t},p),{},{components:n})):a.createElement(k,i({ref:t},p))}));function k(e,t){var n=arguments,l=t&&t.mdxType;if("string"==typeof e||l){var r=n.length,i=new Array(r);i[0]=c;var o={};for(var m in t)hasOwnProperty.call(t,m)&&(o[m]=t[m]);o.originalType=e,o[s]="string"==typeof e?e:l,i[1]=o;for(var u=2;u<r;u++)i[u]=n[u];return a.createElement.apply(null,i)}return a.createElement.apply(null,n)}c.displayName="MDXCreateElement"},98738:(e,t,n)=>{n.r(t),n.d(t,{assets:()=>p,contentTitle:()=>m,default:()=>k,frontMatter:()=>o,metadata:()=>u,toc:()=>s});var a=n(87462),l=n(63366),r=(n(67294),n(3905)),i=["components"],o={id:"filters",title:"Query filters",sidebar_label:"Filters"},m=void 0,u={unversionedId:"querying/filters",id:"querying/filters",title:"Query filters",description:"\x3c!--",source:"@site/docs/latest/querying/filters.md",sourceDirName:"querying",slug:"/querying/filters",permalink:"/docs/latest/querying/filters",draft:!1,tags:[],version:"current",frontMatter:{id:"filters",title:"Query filters",sidebar_label:"Filters"},sidebar:"docs",previous:{title:"DatasourceMetadata",permalink:"/docs/latest/querying/datasourcemetadataquery"},next:{title:"Granularities",permalink:"/docs/latest/querying/granularities"}},p={},s=[{value:"Selector filter",id:"selector-filter",level:2},{value:"Example: equivalent of <code>WHERE someColumn = &#39;hello&#39;</code>",id:"example-equivalent-of-where-somecolumn--hello",level:3},{value:"Example: equivalent of <code>WHERE someColumn IS NULL</code>",id:"example-equivalent-of-where-somecolumn-is-null",level:3},{value:"Equality Filter",id:"equality-filter",level:2},{value:"Example: equivalent of <code>WHERE someColumn = &#39;hello&#39;</code>",id:"example-equivalent-of-where-somecolumn--hello-1",level:3},{value:"Example: equivalent of <code>WHERE someNumericColumn = 1.23</code>",id:"example-equivalent-of-where-somenumericcolumn--123",level:3},{value:"Example: equivalent of <code>WHERE someArrayColumn = ARRAY[1, 2, 3]</code>",id:"example-equivalent-of-where-somearraycolumn--array1-2-3",level:3},{value:"Null Filter",id:"null-filter",level:2},{value:"Example: equivalent of <code>WHERE someColumn IS NULL</code>",id:"example-equivalent-of-where-somecolumn-is-null-1",level:3},{value:"Column comparison filter",id:"column-comparison-filter",level:2},{value:"Example: equivalent of <code>WHERE someColumn = someLongColumn</code>",id:"example-equivalent-of-where-somecolumn--somelongcolumn",level:3},{value:"Logical expression filters",id:"logical-expression-filters",level:2},{value:"AND",id:"and",level:3},{value:"Example: equivalent of <code>WHERE someColumn = &#39;a&#39; AND otherColumn = 1234 AND anotherColumn IS NULL</code>",id:"example-equivalent-of-where-somecolumn--a-and-othercolumn--1234-and-anothercolumn-is-null",level:4},{value:"OR",id:"or",level:3},{value:"Example: equivalent of <code>WHERE someColumn = &#39;a&#39; OR otherColumn = 1234 OR anotherColumn IS NULL</code>",id:"example-equivalent-of-where-somecolumn--a-or-othercolumn--1234-or-anothercolumn-is-null",level:4},{value:"NOT",id:"not",level:3},{value:"Example: equivalent of <code>WHERE someColumn IS NOT NULL</code>",id:"example-equivalent-of-where-somecolumn-is-not-null",level:4},{value:"In filter",id:"in-filter",level:2},{value:"Example: equivalent of <code>WHERE </code>outlaw<code> IN (&#39;Good&#39;, &#39;Bad&#39;, &#39;Ugly&#39;)</code>",id:"example-equivalent-of-where-outlaw-in-good-bad-ugly",level:3},{value:"Bound filter",id:"bound-filter",level:2},{value:"Example: equivalent to <code>WHERE 21 &lt;= age &lt;= 31</code>",id:"example-equivalent-to-where-21--age--31",level:3},{value:"Example: equivalent to <code>WHERE &#39;foo&#39; &lt;= name &lt;= &#39;hoo&#39;</code>, using the default lexicographic sorting order",id:"example-equivalent-to-where-foo--name--hoo-using-the-default-lexicographic-sorting-order",level:3},{value:"Example: equivalent to <code>WHERE 21 &lt; age &lt; 31</code>",id:"example-equivalent-to-where-21--age--31-1",level:3},{value:"Example: equivalent to <code>WHERE age &lt; 31</code>",id:"example-equivalent-to-where-age--31",level:3},{value:"Example: equivalent to <code>WHERE age &gt;= 18</code>",id:"example-equivalent-to-where-age--18",level:3},{value:"Range filter",id:"range-filter",level:2},{value:"Example: equivalent to <code>WHERE 21 &lt;= age &lt;= 31</code>",id:"example-equivalent-to-where-21--age--31-2",level:3},{value:"Example: equivalent to <code>WHERE &#39;foo&#39; &lt;= name &lt;= &#39;hoo&#39;</code>, using STRING comparison",id:"example-equivalent-to-where-foo--name--hoo-using-string-comparison",level:3},{value:"Example: equivalent to <code>WHERE 21 &lt; age &lt; 31</code>",id:"example-equivalent-to-where-21--age--31-3",level:3},{value:"Example: equivalent to <code>WHERE age &lt; 31</code>",id:"example-equivalent-to-where-age--31-1",level:3},{value:"Example: equivalent to <code>WHERE age &gt;= 18</code>",id:"example-equivalent-to-where-age--18-1",level:3},{value:"Example: equivalent to <code>WHERE ARRAY[&#39;a&#39;,&#39;b&#39;,&#39;c&#39;] &lt; arrayColumn &lt; ARRAY[&#39;d&#39;,&#39;e&#39;,&#39;f&#39;]</code>, using ARRAY comparison",id:"example-equivalent-to-where-arrayabc--arraycolumn--arraydef-using-array-comparison",level:3},{value:"Like filter",id:"like-filter",level:2},{value:"Example: equivalent of <code>WHERE last_name LIKE &quot;D%&quot;</code> (last_name starts with &quot;D&quot;)",id:"example-equivalent-of-where-last_name-like-d-last_name-starts-with-d",level:3},{value:"Regular expression filter",id:"regular-expression-filter",level:2},{value:"Example: matches values that start with &quot;50.&quot;",id:"example-matches-values-that-start-with-50",level:3},{value:"Array contains element filter",id:"array-contains-element-filter",level:2},{value:"Example: equivalent of <code>WHERE ARRAY_CONTAINS(someArrayColumn, &#39;hello&#39;)</code>",id:"example-equivalent-of-where-array_containssomearraycolumn-hello",level:3},{value:"Example: equivalent of <code>WHERE ARRAY_CONTAINS(someNumericArrayColumn, 1.23)</code>",id:"example-equivalent-of-where-array_containssomenumericarraycolumn-123",level:3},{value:"Example: equivalent of <code>WHERE ARRAY_CONTAINS(someNumericArrayColumn, ARRAY[1, 2, 3])</code>",id:"example-equivalent-of-where-array_containssomenumericarraycolumn-array1-2-3",level:3},{value:"Example: equivalent of <code>WHERE ARRAY_OVERLAPS(someNumericArrayColumn, ARRAY[1, 2, 3])</code>",id:"example-equivalent-of-where-array_overlapssomenumericarraycolumn-array1-2-3",level:3},{value:"Interval filter",id:"interval-filter",level:2},{value:"True filter",id:"true-filter",level:2},{value:"False filter",id:"false-filter",level:2},{value:"Search filter",id:"search-filter",level:2},{value:"Search query spec",id:"search-query-spec",level:3},{value:"Contains",id:"contains",level:4},{value:"Insensitive contains",id:"insensitive-contains",level:4},{value:"Fragment",id:"fragment",level:4},{value:"Expression filter",id:"expression-filter",level:2},{value:"Example: expression based matching",id:"example-expression-based-matching",level:3},{value:"JavaScript filter",id:"javascript-filter",level:2},{value:"Example: matching any dimension values for the dimension <code>name</code> between <code>&#39;bar&#39;</code> and <code>&#39;foo&#39;</code>",id:"example-matching-any-dimension-values-for-the-dimension-name-between-bar-and-foo",level:3},{value:"Extraction filter",id:"extraction-filter",level:2},{value:"Example: matching dimension values in <code>[product_1, product_3, product_5]</code> for the column <code>product</code>",id:"example-matching-dimension-values-in-product_1-product_3-product_5-for-the-column-product",level:3},{value:"Filtering with extraction functions",id:"filtering-with-extraction-functions",level:2},{value:"Example: matches dimension values in <code>[product_1, product_3, product_5]</code> for the column <code>product</code>",id:"example-matches-dimension-values-in-product_1-product_3-product_5-for-the-column-product",level:3},{value:"Column types",id:"column-types",level:2},{value:"Filtering on multi-value string columns",id:"filtering-on-multi-value-string-columns",level:3},{value:"Example: multi-value match behavior",id:"example-multi-value-match-behavior",level:4},{value:"Example: SQL &quot;contradiction&quot;",id:"example-sql-contradiction",level:4},{value:"Filtering on numeric columns",id:"filtering-on-numeric-columns",level:3},{value:"Example: filtering on a specific value, <code>myFloatColumn = 10.1</code>",id:"example-filtering-on-a-specific-value-myfloatcolumn--101",level:4},{value:"Example: filtering on a range of values, <code>10 &lt;= myFloatColumn &lt; 20</code>",id:"example-filtering-on-a-range-of-values-10--myfloatcolumn--20",level:4},{value:"Filtering on the timestamp column",id:"filtering-on-the-timestamp-column",level:3},{value:"Example: filtering on a long timestamp value",id:"example-filtering-on-a-long-timestamp-value",level:4},{value:"Example: filtering on day of week using an extraction function",id:"example-filtering-on-day-of-week-using-an-extraction-function",level:4},{value:"Example: filtering on a set of ISO 8601 intervals",id:"example-filtering-on-a-set-of-iso-8601-intervals",level:4}],d={toc:s},c="wrapper";function k(e){var t=e.components,n=(0,l.Z)(e,i);return(0,r.kt)(c,(0,a.Z)({},d,n,{components:t,mdxType:"MDXLayout"}),(0,r.kt)("admonition",{type:"info"},(0,r.kt)("p",{parentName:"admonition"}," Apache Druid supports two query languages: ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/sql"},"Druid SQL")," and ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/"},"native queries"),".\nThis document describes the native\nlanguage. For information about aggregators available in SQL, refer to the\n",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/sql-scalar"},"SQL documentation"),".")),(0,r.kt)("p",null,"A filter is a JSON object indicating which rows of data should be included in the computation for a query. It\u2019s essentially the equivalent of the WHERE clause in SQL.\nFilters are commonly applied on dimensions, but can be applied on aggregated metrics, for example, see ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/aggregations#filtered-aggregator"},"Filtered aggregator")," and ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/having"},"Having filters"),"."),(0,r.kt)("p",null,"By default, Druid uses SQL compatible three-value logic when filtering. See ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/sql-data-types#boolean-logic"},"Boolean logic")," for more details."),(0,r.kt)("p",null,"Apache Druid supports the following types of filters."),(0,r.kt)("h2",{id:"selector-filter"},"Selector filter"),(0,r.kt)("p",null,"The simplest filter is a selector filter. The selector filter matches a specific dimension with a specific value. Selector filters can be used as the base filters for more complex Boolean expressions of filters."),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "selector".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"dimension")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"value")),(0,r.kt)("td",{parentName:"tr",align:null},"String value to match."),(0,r.kt)("td",{parentName:"tr",align:null},"No. If not specified the filter matches NULL values.")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"extractionFn")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("a",{parentName:"td",href:"/docs/latest/querying/dimensionspecs#extraction-functions"},"Extraction function")," to apply to ",(0,r.kt)("inlineCode",{parentName:"td"},"dimension")," prior to value matching. See ",(0,r.kt)("a",{parentName:"td",href:"#filtering-with-extraction-functions"},"filtering with extraction functions")," for details."),(0,r.kt)("td",{parentName:"tr",align:null},"No")))),(0,r.kt)("p",null,"The selector filter can only match against ",(0,r.kt)("inlineCode",{parentName:"p"},"STRING")," (single and multi-valued), ",(0,r.kt)("inlineCode",{parentName:"p"},"LONG"),", ",(0,r.kt)("inlineCode",{parentName:"p"},"FLOAT"),", ",(0,r.kt)("inlineCode",{parentName:"p"},"DOUBLE")," types. Use the newer null and equality filters to match against ",(0,r.kt)("inlineCode",{parentName:"p"},"ARRAY")," or ",(0,r.kt)("inlineCode",{parentName:"p"},"COMPLEX")," types."),(0,r.kt)("p",null,"When the selector filter matches against numeric inputs, the string ",(0,r.kt)("inlineCode",{parentName:"p"},"value")," will be best-effort coerced into a numeric value."),(0,r.kt)("h3",{id:"example-equivalent-of-where-somecolumn--hello"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE someColumn = 'hello'")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ "type": "selector", "dimension": "someColumn", "value": "hello" }\n')),(0,r.kt)("h3",{id:"example-equivalent-of-where-somecolumn-is-null"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE someColumn IS NULL")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ "type": "selector", "dimension": "someColumn", "value": null }\n')),(0,r.kt)("h2",{id:"equality-filter"},"Equality Filter"),(0,r.kt)("p",null,"The equality filter is a replacement for the selector filter with the ability to match against any type of column. The equality filter is designed to have more SQL compatible behavior than the selector filter and so can not match null values. To match null values use the null filter."),(0,r.kt)("p",null,"Druid's SQL planner uses the equality filter by default instead of selector filter whenever ",(0,r.kt)("inlineCode",{parentName:"p"},"druid.generic.useDefaultValueForNull=false"),", or if ",(0,r.kt)("inlineCode",{parentName:"p"},"sqlUseBoundAndSelectors")," is set to false on the ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/sql-query-context"},"SQL query context"),"."),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "equality".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"column")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"matchValueType")),(0,r.kt)("td",{parentName:"tr",align:null},"String specifying the type of value to match. For example ",(0,r.kt)("inlineCode",{parentName:"td"},"STRING"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"LONG"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"DOUBLE"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"FLOAT"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"ARRAY<STRING>"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"ARRAY<LONG>"),", or any other Druid type. The ",(0,r.kt)("inlineCode",{parentName:"td"},"matchValueType")," determines how Druid interprets the ",(0,r.kt)("inlineCode",{parentName:"td"},"matchValue")," to assist in converting to the type of the matched ",(0,r.kt)("inlineCode",{parentName:"td"},"column"),"."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"matchValue")),(0,r.kt)("td",{parentName:"tr",align:null},"Value to match, must not be null."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")))),(0,r.kt)("h3",{id:"example-equivalent-of-where-somecolumn--hello-1"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE someColumn = 'hello'")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ "type": "equals", "column": "someColumn", "matchValueType": "STRING", "matchValue": "hello" }\n')),(0,r.kt)("h3",{id:"example-equivalent-of-where-somenumericcolumn--123"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE someNumericColumn = 1.23")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ "type": "equals", "column": "someNumericColumn", "matchValueType": "DOUBLE", "matchValue": 1.23 }\n')),(0,r.kt)("h3",{id:"example-equivalent-of-where-somearraycolumn--array1-2-3"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE someArrayColumn = ARRAY[1, 2, 3]")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ "type": "equals", "column": "someArrayColumn", "matchValueType": "ARRAY<LONG>", "matchValue": [1, 2, 3] }\n')),(0,r.kt)("h2",{id:"null-filter"},"Null Filter"),(0,r.kt)("p",null,"The null filter is a partial replacement for the selector filter. It is dedicated to matching NULL values."),(0,r.kt)("p",null,"Druid's SQL planner uses the null filter by default instead of selector filter whenever ",(0,r.kt)("inlineCode",{parentName:"p"},"druid.generic.useDefaultValueForNull=false"),", or if ",(0,r.kt)("inlineCode",{parentName:"p"},"sqlUseBoundAndSelectors")," is set to false on the ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/sql-query-context"},"SQL query context"),"."),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "null".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"column")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")))),(0,r.kt)("h3",{id:"example-equivalent-of-where-somecolumn-is-null-1"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE someColumn IS NULL")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ "type": "null", "column": "someColumn" }\n')),(0,r.kt)("h2",{id:"column-comparison-filter"},"Column comparison filter"),(0,r.kt)("p",null,"The column comparison filter is similar to the selector filter, but compares dimensions to each other. For example:"),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "selector".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"dimensions")),(0,r.kt)("td",{parentName:"tr",align:null},"List of ",(0,r.kt)("a",{parentName:"td",href:"/docs/latest/querying/dimensionspecs"},(0,r.kt)("inlineCode",{parentName:"a"},"DimensionSpec"))," to compare."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")))),(0,r.kt)("p",null,(0,r.kt)("inlineCode",{parentName:"p"},"dimensions")," is list of ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/dimensionspecs"},"DimensionSpecs"),", making it possible to apply an extraction function if needed."),(0,r.kt)("p",null,"Note that the column comparison filter converts all values to strings prior to comparison. This allows differently-typed input columns to match without a cast operation."),(0,r.kt)("h3",{id:"example-equivalent-of-where-somecolumn--somelongcolumn"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE someColumn = someLongColumn")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "columnComparison",\n  "dimensions": [\n    "someColumn",\n    {\n      "type" : "default",\n      "dimension" : someLongColumn,\n      "outputType": "LONG"\n    }\n  ]\n}\n')),(0,r.kt)("h2",{id:"logical-expression-filters"},"Logical expression filters"),(0,r.kt)("h3",{id:"and"},"AND"),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "and".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"fields")),(0,r.kt)("td",{parentName:"tr",align:null},"List of filter JSON objects, such as any other filter defined on this page or provided by extensions."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")))),(0,r.kt)("h4",{id:"example-equivalent-of-where-somecolumn--a-and-othercolumn--1234-and-anothercolumn-is-null"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h4"},"WHERE someColumn = 'a' AND otherColumn = 1234 AND anotherColumn IS NULL")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "and",\n  "fields": [\n    { "type": "equals", "column": "someColumn", "matchValue": "a", "matchValueType": "STRING" },\n    { "type": "equals", "column": "otherColumn", "matchValue": 1234, "matchValueType": "LONG" },\n    { "type": "null", "column": "anotherColumn" } \n  ]\n}\n')),(0,r.kt)("h3",{id:"or"},"OR"),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "or".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"fields")),(0,r.kt)("td",{parentName:"tr",align:null},"List of filter JSON objects, such as any other filter defined on this page or provided by extensions."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")))),(0,r.kt)("h4",{id:"example-equivalent-of-where-somecolumn--a-or-othercolumn--1234-or-anothercolumn-is-null"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h4"},"WHERE someColumn = 'a' OR otherColumn = 1234 OR anotherColumn IS NULL")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "or",\n  "fields": [\n    { "type": "equals", "column": "someColumn", "matchValue": "a", "matchValueType": "STRING" },\n    { "type": "equals", "column": "otherColumn", "matchValue": 1234, "matchValueType": "LONG" },\n    { "type": "null", "column": "anotherColumn" } \n  ]\n}\n')),(0,r.kt)("h3",{id:"not"},"NOT"),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "not".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"field")),(0,r.kt)("td",{parentName:"tr",align:null},"Filter JSON objects, such as any other filter defined on this page or provided by extensions."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")))),(0,r.kt)("h4",{id:"example-equivalent-of-where-somecolumn-is-not-null"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h4"},"WHERE someColumn IS NOT NULL")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ "type": "not", "field": { "type": "null", "column": "someColumn" }}\n')),(0,r.kt)("h2",{id:"in-filter"},"In filter"),(0,r.kt)("p",null,"The in filter can match input rows against a set of values, where a match occurs if the value is contained in the set."),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "in".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"dimension")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"values")),(0,r.kt)("td",{parentName:"tr",align:null},"List of string value to match."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"extractionFn")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("a",{parentName:"td",href:"/docs/latest/querying/dimensionspecs#extraction-functions"},"Extraction function")," to apply to ",(0,r.kt)("inlineCode",{parentName:"td"},"dimension")," prior to value matching. See ",(0,r.kt)("a",{parentName:"td",href:"#filtering-with-extraction-functions"},"filtering with extraction functions")," for details."),(0,r.kt)("td",{parentName:"tr",align:null},"No")))),(0,r.kt)("p",null,"If an empty ",(0,r.kt)("inlineCode",{parentName:"p"},"values"),' array is passed to the "in" filter, it will simply return an empty result.'),(0,r.kt)("p",null,"If the ",(0,r.kt)("inlineCode",{parentName:"p"},"values")," array contains ",(0,r.kt)("inlineCode",{parentName:"p"},"null"),', the "in" filter matches null values. This differs from the SQL IN filter, which\ndoes not match NULL values.'),(0,r.kt)("h3",{id:"example-equivalent-of-where-outlaw-in-good-bad-ugly"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE "),"outlaw",(0,r.kt)("inlineCode",{parentName:"h3"}," IN ('Good', 'Bad', 'Ugly')")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "in",\n    "dimension": "outlaw",\n    "values": ["Good", "Bad", "Ugly"]\n}\n')),(0,r.kt)("h2",{id:"bound-filter"},"Bound filter"),(0,r.kt)("p",null,'Bound filters can be used to filter on ranges of dimension values. It can be used for comparison filtering like\ngreater than, less than, greater than or equal to, less than or equal to, and "between" (if both "lower" and\n"upper" are set).'),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "bound".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"dimension")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"lower")),(0,r.kt)("td",{parentName:"tr",align:null},"The lower bound string match value for the filter."),(0,r.kt)("td",{parentName:"tr",align:null},"No")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"upper")),(0,r.kt)("td",{parentName:"tr",align:null},"The upper bound string match value for the filter."),(0,r.kt)("td",{parentName:"tr",align:null},"No")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"lowerStrict")),(0,r.kt)("td",{parentName:"tr",align:null},"Boolean indicating whether to perform strict comparison on the ",(0,r.kt)("inlineCode",{parentName:"td"},"lower"),' bound (">" instead of ">=").'),(0,r.kt)("td",{parentName:"tr",align:null},"No, default: ",(0,r.kt)("inlineCode",{parentName:"td"},"false"))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"upperStrict")),(0,r.kt)("td",{parentName:"tr",align:null},'Boolean indicating whether to perform strict comparison on the upper bound ("<" instead of "<=").'),(0,r.kt)("td",{parentName:"tr",align:null},"No, default: ",(0,r.kt)("inlineCode",{parentName:"td"},"false"))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"ordering")),(0,r.kt)("td",{parentName:"tr",align:null},"String that specifies the sorting order to use when comparing values against the bound. Can be one of the following values: ",(0,r.kt)("inlineCode",{parentName:"td"},'"lexicographic"'),", ",(0,r.kt)("inlineCode",{parentName:"td"},'"alphanumeric"'),", ",(0,r.kt)("inlineCode",{parentName:"td"},'"numeric"'),", ",(0,r.kt)("inlineCode",{parentName:"td"},'"strlen"'),", ",(0,r.kt)("inlineCode",{parentName:"td"},'"version"'),". See ",(0,r.kt)("a",{parentName:"td",href:"/docs/latest/querying/sorting-orders"},"Sorting Orders")," for more details."),(0,r.kt)("td",{parentName:"tr",align:null},"No, default: ",(0,r.kt)("inlineCode",{parentName:"td"},'"lexicographic"'))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"extractionFn")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("a",{parentName:"td",href:"/docs/latest/querying/dimensionspecs#extraction-functions"},"Extraction function")," to apply to ",(0,r.kt)("inlineCode",{parentName:"td"},"dimension")," prior to value matching. See ",(0,r.kt)("a",{parentName:"td",href:"#filtering-with-extraction-functions"},"filtering with extraction functions")," for details."),(0,r.kt)("td",{parentName:"tr",align:null},"No")))),(0,r.kt)("p",null,"When the bound filter matches against numeric inputs, the string ",(0,r.kt)("inlineCode",{parentName:"p"},"lower")," and ",(0,r.kt)("inlineCode",{parentName:"p"},"upper")," bound values are best-effort coerced into a numeric value when using the ",(0,r.kt)("inlineCode",{parentName:"p"},'"numeric"')," mode of ordering."),(0,r.kt)("p",null,"The bound filter can only match against ",(0,r.kt)("inlineCode",{parentName:"p"},"STRING")," (single and multi-valued), ",(0,r.kt)("inlineCode",{parentName:"p"},"LONG"),", ",(0,r.kt)("inlineCode",{parentName:"p"},"FLOAT"),", ",(0,r.kt)("inlineCode",{parentName:"p"},"DOUBLE")," types. Use the newer range to match against ",(0,r.kt)("inlineCode",{parentName:"p"},"ARRAY")," or ",(0,r.kt)("inlineCode",{parentName:"p"},"COMPLEX")," types."),(0,r.kt)("p",null,"Note that the bound filter matches null values if you don't specify a lower bound. Use the range filter if SQL-compatible behavior."),(0,r.kt)("h3",{id:"example-equivalent-to-where-21--age--31"},"Example: equivalent to ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE 21 <= age <= 31")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "bound",\n    "dimension": "age",\n    "lower": "21",\n    "upper": "31" ,\n    "ordering": "numeric"\n}\n')),(0,r.kt)("h3",{id:"example-equivalent-to-where-foo--name--hoo-using-the-default-lexicographic-sorting-order"},"Example: equivalent to ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE 'foo' <= name <= 'hoo'"),", using the default lexicographic sorting order"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "bound",\n    "dimension": "name",\n    "lower": "foo",\n    "upper": "hoo"\n}\n')),(0,r.kt)("h3",{id:"example-equivalent-to-where-21--age--31-1"},"Example: equivalent to ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE 21 < age < 31")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "bound",\n    "dimension": "age",\n    "lower": "21",\n    "lowerStrict": true,\n    "upper": "31" ,\n    "upperStrict": true,\n    "ordering": "numeric"\n}\n')),(0,r.kt)("h3",{id:"example-equivalent-to-where-age--31"},"Example: equivalent to ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE age < 31")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "bound",\n    "dimension": "age",\n    "upper": "31" ,\n    "upperStrict": true,\n    "ordering": "numeric"\n}\n')),(0,r.kt)("h3",{id:"example-equivalent-to-where-age--18"},"Example: equivalent to ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE age >= 18")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "bound",\n    "dimension": "age",\n    "lower": "18" ,\n    "ordering": "numeric"\n}\n')),(0,r.kt)("h2",{id:"range-filter"},"Range filter"),(0,r.kt)("p",null,"The range filter is a replacement for the bound filter. It compares against any type of column and is designed to have has more SQL compliant behavior than the bound filter. It won't match null values, even if you don't specify a lower bound."),(0,r.kt)("p",null,"Druid's SQL planner uses the range filter by default instead of bound filter whenever ",(0,r.kt)("inlineCode",{parentName:"p"},"druid.generic.useDefaultValueForNull=false"),", or if ",(0,r.kt)("inlineCode",{parentName:"p"},"sqlUseBoundAndSelectors")," is set to false on the ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/sql-query-context"},"SQL query context"),"."),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "range".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"column")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"matchValueType")),(0,r.kt)("td",{parentName:"tr",align:null},"String specifying the type of bounds to match. For example ",(0,r.kt)("inlineCode",{parentName:"td"},"STRING"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"LONG"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"DOUBLE"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"FLOAT"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"ARRAY<STRING>"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"ARRAY<LONG>"),", or any other Druid type. The ",(0,r.kt)("inlineCode",{parentName:"td"},"matchValueType")," determines how Druid interprets the ",(0,r.kt)("inlineCode",{parentName:"td"},"matchValue")," to assist in converting to the type of the matched ",(0,r.kt)("inlineCode",{parentName:"td"},"column")," and also defines the type of comparison used when matching values."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"lower")),(0,r.kt)("td",{parentName:"tr",align:null},"Lower bound value to match."),(0,r.kt)("td",{parentName:"tr",align:null},"No. At least one of ",(0,r.kt)("inlineCode",{parentName:"td"},"lower")," or ",(0,r.kt)("inlineCode",{parentName:"td"},"upper")," must not be null.")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"upper")),(0,r.kt)("td",{parentName:"tr",align:null},"Upper bound value to match."),(0,r.kt)("td",{parentName:"tr",align:null},"No. At least one of ",(0,r.kt)("inlineCode",{parentName:"td"},"lower")," or ",(0,r.kt)("inlineCode",{parentName:"td"},"upper")," must not be null.")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"lowerOpen")),(0,r.kt)("td",{parentName:"tr",align:null},'Boolean indicating if lower bound is open in the interval of values defined by the range (">" instead of ">=").'),(0,r.kt)("td",{parentName:"tr",align:null},"No")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"upperOpen")),(0,r.kt)("td",{parentName:"tr",align:null},'Boolean indicating if upper bound is open on the interval of values defined by range ("<" instead of "<=").'),(0,r.kt)("td",{parentName:"tr",align:null},"No")))),(0,r.kt)("h3",{id:"example-equivalent-to-where-21--age--31-2"},"Example: equivalent to ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE 21 <= age <= 31")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "range",\n    "column": "age",\n    "matchValueType": "LONG",\n    "lower": 21,\n    "upper": 31\n}\n')),(0,r.kt)("h3",{id:"example-equivalent-to-where-foo--name--hoo-using-string-comparison"},"Example: equivalent to ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE 'foo' <= name <= 'hoo'"),", using STRING comparison"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "range",\n    "column": "name",\n    "matchValueType": "STRING",\n    "lower": "foo",\n    "upper": "hoo"\n}\n')),(0,r.kt)("h3",{id:"example-equivalent-to-where-21--age--31-3"},"Example: equivalent to ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE 21 < age < 31")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "range",\n    "column": "age",\n    "matchValueType": "LONG",\n    "lower": "21",\n    "lowerOpen": true,\n    "upper": "31" ,\n    "upperOpen": true\n}\n')),(0,r.kt)("h3",{id:"example-equivalent-to-where-age--31-1"},"Example: equivalent to ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE age < 31")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "range",\n    "column": "age",\n    "matchValueType": "LONG",\n    "upper": "31" ,\n    "upperOpen": true\n}\n')),(0,r.kt)("h3",{id:"example-equivalent-to-where-age--18-1"},"Example: equivalent to ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE age >= 18")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "range",\n    "column": "age",\n    "matchValueType": "LONG",\n    "lower": 18\n}\n')),(0,r.kt)("h3",{id:"example-equivalent-to-where-arrayabc--arraycolumn--arraydef-using-array-comparison"},"Example: equivalent to ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE ARRAY['a','b','c'] < arrayColumn < ARRAY['d','e','f']"),", using ARRAY comparison"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "range",\n    "column": "name",\n    "matchValueType": "ARRAY<STRING>",\n    "lower": ["a","b","c"],\n    "lowerOpen": true,\n    "upper": ["d","e","f"],\n    "upperOpen": true\n}\n')),(0,r.kt)("h2",{id:"like-filter"},"Like filter"),(0,r.kt)("p",null,'Like filters can be used for basic wildcard searches. They are equivalent to the SQL LIKE operator. Special characters\nsupported are "%" (matches any number of characters) and "',"_",'" (matches any one character).'),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "like".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"dimension")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"pattern")),(0,r.kt)("td",{parentName:"tr",align:null},'String LIKE pattern, such as "foo%" or "___bar".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"escape")),(0,r.kt)("td",{parentName:"tr",align:null},"A string escape character that can be used to escape special characters."),(0,r.kt)("td",{parentName:"tr",align:null},"No")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"extractionFn")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("a",{parentName:"td",href:"/docs/latest/querying/dimensionspecs#extraction-functions"},"Extraction function")," to apply to ",(0,r.kt)("inlineCode",{parentName:"td"},"dimension")," prior to value matching. See ",(0,r.kt)("a",{parentName:"td",href:"#filtering-with-extraction-functions"},"filtering with extraction functions")," for details."),(0,r.kt)("td",{parentName:"tr",align:null},"No")))),(0,r.kt)("p",null,"Like filters support the use of extraction functions, see ",(0,r.kt)("a",{parentName:"p",href:"#filtering-with-extraction-functions"},"Filtering with Extraction Functions")," for details."),(0,r.kt)("h3",{id:"example-equivalent-of-where-last_name-like-d-last_name-starts-with-d"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},'WHERE last_name LIKE "D%"'),' (last_name starts with "D")'),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "like",\n    "dimension": "last_name",\n    "pattern": "D%"\n}\n')),(0,r.kt)("h2",{id:"regular-expression-filter"},"Regular expression filter"),(0,r.kt)("p",null,"The regular expression filter is similar to the selector filter, but using regular expressions. It matches the specified dimension with the given pattern."),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "regex".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"dimension")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"pattern")),(0,r.kt)("td",{parentName:"tr",align:null},"String pattern to match - any standard ",(0,r.kt)("a",{parentName:"td",href:"http://docs.oracle.com/javase/6/docs/api/java/util/regex/Pattern.html"},"Java regular expression"),"."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"extractionFn")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("a",{parentName:"td",href:"/docs/latest/querying/dimensionspecs#extraction-functions"},"Extraction function")," to apply to ",(0,r.kt)("inlineCode",{parentName:"td"},"dimension")," prior to value matching. See ",(0,r.kt)("a",{parentName:"td",href:"#filtering-with-extraction-functions"},"filtering with extraction functions")," for details."),(0,r.kt)("td",{parentName:"tr",align:null},"No")))),(0,r.kt)("p",null,"Note that it is often more optimal to use a like filter instead of a regex for simple matching of prefixes."),(0,r.kt)("h3",{id:"example-matches-values-that-start-with-50"},'Example: matches values that start with "50."'),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ "type": "regex", "dimension": "someColumn", "pattern": ^50.* }\n')),(0,r.kt)("h2",{id:"array-contains-element-filter"},"Array contains element filter"),(0,r.kt)("p",null,"The ",(0,r.kt)("inlineCode",{parentName:"p"},"arrayContainsElement")," filter checks if an ",(0,r.kt)("inlineCode",{parentName:"p"},"ARRAY")," contains a specific element but can also match against any type of column. When matching against scalar columns, scalar columns are treated as single-element arrays."),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "arrayContainsElement".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"column")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"elementMatchValueType")),(0,r.kt)("td",{parentName:"tr",align:null},"String specifying the type of element value to match. For example ",(0,r.kt)("inlineCode",{parentName:"td"},"STRING"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"LONG"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"DOUBLE"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"FLOAT"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"ARRAY<STRING>"),", ",(0,r.kt)("inlineCode",{parentName:"td"},"ARRAY<LONG>"),", or any other Druid type. The ",(0,r.kt)("inlineCode",{parentName:"td"},"elementMatchValueType")," determines how Druid interprets the ",(0,r.kt)("inlineCode",{parentName:"td"},"elementMatchValue")," to assist in converting to the type of elements contained in the matched ",(0,r.kt)("inlineCode",{parentName:"td"},"column"),"."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"elementMatchValue")),(0,r.kt)("td",{parentName:"tr",align:null},"Array element value to match. This value can be null."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")))),(0,r.kt)("h3",{id:"example-equivalent-of-where-array_containssomearraycolumn-hello"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE ARRAY_CONTAINS(someArrayColumn, 'hello')")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ "type": "arrayContainsElement", "column": "someArrayColumn", "elementMatchValueType": "STRING", "elementMatchValue": "hello" }\n')),(0,r.kt)("h3",{id:"example-equivalent-of-where-array_containssomenumericarraycolumn-123"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE ARRAY_CONTAINS(someNumericArrayColumn, 1.23)")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ "type": "arrayContainsElement", "column": "someNumericArrayColumn", "elementMatchValueType": "DOUBLE", "elementMatchValue": 1.23 }\n')),(0,r.kt)("h3",{id:"example-equivalent-of-where-array_containssomenumericarraycolumn-array1-2-3"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE ARRAY_CONTAINS(someNumericArrayColumn, ARRAY[1, 2, 3])")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "and",\n  "fields": [\n    { "type": "arrayContainsElement", "column": "someNumericArrayColumn", "elementMatchValueType": "LONG", "elementMatchValue": 1 },\n    { "type": "arrayContainsElement", "column": "someNumericArrayColumn", "elementMatchValueType": "LONG", "elementMatchValue": 2 },\n    { "type": "arrayContainsElement", "column": "someNumericArrayColumn", "elementMatchValueType": "LONG", "elementMatchValue": 3 }\n  ]\n}\n\n')),(0,r.kt)("h3",{id:"example-equivalent-of-where-array_overlapssomenumericarraycolumn-array1-2-3"},"Example: equivalent of ",(0,r.kt)("inlineCode",{parentName:"h3"},"WHERE ARRAY_OVERLAPS(someNumericArrayColumn, ARRAY[1, 2, 3])")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n "type": "or",\n "fields": [\n  { "type": "arrayContainsElement", "column": "someNumericArrayColumn", "elementMatchValueType": "LONG", "elementMatchValue": 1 },\n  { "type": "arrayContainsElement", "column": "someNumericArrayColumn", "elementMatchValueType": "LONG", "elementMatchValue": 2 },\n  { "type": "arrayContainsElement", "column": "someNumericArrayColumn", "elementMatchValueType": "LONG", "elementMatchValue": 3 }\n ]\n}\n')),(0,r.kt)("h2",{id:"interval-filter"},"Interval filter"),(0,r.kt)("p",null,"The Interval filter enables range filtering on columns that contain long millisecond values, with the boundaries specified as ISO 8601 time intervals. It is suitable for the ",(0,r.kt)("inlineCode",{parentName:"p"},"__time")," column, long metric columns, and dimensions with values that can be parsed as long milliseconds."),(0,r.kt)("p",null,"This filter converts the ISO 8601 intervals to long millisecond start/end ranges and translates to an OR of Bound filters on those millisecond ranges, with numeric comparison. The Bound filters will have left-closed and right-open matching (i.e., start <= time < end)."),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "interval".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"dimension")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"intervals")),(0,r.kt)("td",{parentName:"tr",align:null},"A JSON array containing ISO-8601 interval strings that defines the time ranges to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"extractionFn")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("a",{parentName:"td",href:"/docs/latest/querying/dimensionspecs#extraction-functions"},"Extraction function")," to apply to ",(0,r.kt)("inlineCode",{parentName:"td"},"dimension")," prior to value matching. See ",(0,r.kt)("a",{parentName:"td",href:"#filtering-with-extraction-functions"},"filtering with extraction functions")," for details."),(0,r.kt)("td",{parentName:"tr",align:null},"No")))),(0,r.kt)("p",null,"The interval filter supports the use of extraction functions, see ",(0,r.kt)("a",{parentName:"p",href:"#filtering-with-extraction-functions"},"Filtering with Extraction Functions")," for details."),(0,r.kt)("p",null,"If an extraction function is used with this filter, the extraction function should output values that are parseable as long milliseconds."),(0,r.kt)("p",null,"The following example filters on the time ranges of October 1-7, 2014 and November 15-16, 2014."),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type" : "interval",\n    "dimension" : "__time",\n    "intervals" : [\n      "2014-10-01T00:00:00.000Z/2014-10-07T00:00:00.000Z",\n      "2014-11-15T00:00:00.000Z/2014-11-16T00:00:00.000Z"\n    ]\n}\n')),(0,r.kt)("p",null,"The filter above is equivalent to the following OR of Bound filters:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type": "or",\n    "fields": [\n      {\n        "type": "bound",\n        "dimension": "__time",\n        "lower": "1412121600000",\n        "lowerStrict": false,\n        "upper": "1412640000000" ,\n        "upperStrict": true,\n        "ordering": "numeric"\n      },\n      {\n         "type": "bound",\n         "dimension": "__time",\n         "lower": "1416009600000",\n         "lowerStrict": false,\n         "upper": "1416096000000" ,\n         "upperStrict": true,\n         "ordering": "numeric"\n      }\n    ]\n}\n')),(0,r.kt)("h2",{id:"true-filter"},"True filter"),(0,r.kt)("p",null,"A filter which matches all values. You can use it to temporarily disable other filters without removing them."),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ "type" : "true" }\n')),(0,r.kt)("h2",{id:"false-filter"},"False filter"),(0,r.kt)("p",null,"A filter matches no values. You can use it to force a query to match no values."),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{"type": "false" }\n')),(0,r.kt)("h2",{id:"search-filter"},"Search filter"),(0,r.kt)("p",null,"You can use search filters to filter on partial string matches."),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "filter": {\n        "type": "search",\n        "dimension": "product",\n        "query": {\n          "type": "insensitive_contains",\n          "value": "foo"\n        }\n    }\n}\n')),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "search".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"dimension")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"query")),(0,r.kt)("td",{parentName:"tr",align:null},"A JSON object for the type of search. See ",(0,r.kt)("a",{parentName:"td",href:"#search-query-spec"},"search query spec")," for more information."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"extractionFn")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("a",{parentName:"td",href:"/docs/latest/querying/dimensionspecs#extraction-functions"},"Extraction function")," to apply to ",(0,r.kt)("inlineCode",{parentName:"td"},"dimension")," prior to value matching. See ",(0,r.kt)("a",{parentName:"td",href:"#filtering-with-extraction-functions"},"filtering with extraction functions")," for details."),(0,r.kt)("td",{parentName:"tr",align:null},"No")))),(0,r.kt)("h3",{id:"search-query-spec"},"Search query spec"),(0,r.kt)("h4",{id:"contains"},"Contains"),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "contains".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"value")),(0,r.kt)("td",{parentName:"tr",align:null},"A String value to search."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"caseSensitive")),(0,r.kt)("td",{parentName:"tr",align:null},"Whether the string comparison is case-sensitive or not."),(0,r.kt)("td",{parentName:"tr",align:null},"No, default is false (insensitive)")))),(0,r.kt)("h4",{id:"insensitive-contains"},"Insensitive contains"),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "insensitive_contains".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"value")),(0,r.kt)("td",{parentName:"tr",align:null},"A String value to search."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")))),(0,r.kt)("p",null,'Note that an "insensitive_contains" search is equivalent to a "contains" search with "caseSensitive": false (or not\nprovided).'),(0,r.kt)("h4",{id:"fragment"},"Fragment"),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "fragment".'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"values")),(0,r.kt)("td",{parentName:"tr",align:null},"A JSON array of string values to search."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"caseSensitive")),(0,r.kt)("td",{parentName:"tr",align:null},"Whether the string comparison is case-sensitive or not."),(0,r.kt)("td",{parentName:"tr",align:null},"No, default is false (insensitive)")))),(0,r.kt)("h2",{id:"expression-filter"},"Expression filter"),(0,r.kt)("p",null,"The expression filter allows for the implementation of arbitrary conditions, leveraging the Druid expression system. This filter allows for complete flexibility, but it might be less performant than a combination of the other filters on this page because it can't always use the same optimizations available to other filters."),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "expression"'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"expression")),(0,r.kt)("td",{parentName:"tr",align:null},"Expression string to evaluate into true or false. See the ",(0,r.kt)("a",{parentName:"td",href:"/docs/latest/querying/math-expr"},"Druid expression system")," for more details."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")))),(0,r.kt)("h3",{id:"example-expression-based-matching"},"Example: expression based matching"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ \n    "type" : "expression" ,\n    "expression" : "((product_type == 42) && (!is_deleted))"\n}\n')),(0,r.kt)("h2",{id:"javascript-filter"},"JavaScript filter"),(0,r.kt)("p",null,"The JavaScript filter matches a dimension against the specified JavaScript function predicate. The filter matches values for which the function returns true."),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "javascript"'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"dimension")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"function")),(0,r.kt)("td",{parentName:"tr",align:null},"JavaScript function which accepts the dimension value as a single argument, and returns either true or false."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"extractionFn")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("a",{parentName:"td",href:"/docs/latest/querying/dimensionspecs#extraction-functions"},"Extraction function")," to apply to ",(0,r.kt)("inlineCode",{parentName:"td"},"dimension")," prior to value matching. See ",(0,r.kt)("a",{parentName:"td",href:"#filtering-with-extraction-functions"},"filtering with extraction functions")," for details."),(0,r.kt)("td",{parentName:"tr",align:null},"No")))),(0,r.kt)("h3",{id:"example-matching-any-dimension-values-for-the-dimension-name-between-bar-and-foo"},"Example: matching any dimension values for the dimension ",(0,r.kt)("inlineCode",{parentName:"h3"},"name")," between ",(0,r.kt)("inlineCode",{parentName:"h3"},"'bar'")," and ",(0,r.kt)("inlineCode",{parentName:"h3"},"'foo'")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type" : "javascript",\n  "dimension" : "name",\n  "function" : "function(x) { return(x >= \'bar\' && x <= \'foo\') }"\n}\n')),(0,r.kt)("admonition",{type:"info"},(0,r.kt)("p",{parentName:"admonition"}," JavaScript-based functionality is disabled by default. Please refer to the Druid ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/development/javascript"},"JavaScript programming guide")," for guidelines about using Druid's JavaScript functionality, including instructions on how to enable it.")),(0,r.kt)("h2",{id:"extraction-filter"},"Extraction filter"),(0,r.kt)("admonition",{type:"info"},(0,r.kt)("p",{parentName:"admonition"}," The extraction filter is now deprecated. The selector filter with an extraction function specified\nprovides identical functionality and should be used instead.")),(0,r.kt)("p",null,"Extraction filter matches a dimension using a specific ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/dimensionspecs#extraction-functions"},"extraction function"),".\nThe following filter matches the values for which the extraction function has a transformation entry ",(0,r.kt)("inlineCode",{parentName:"p"},"input_key=output_value")," where\n",(0,r.kt)("inlineCode",{parentName:"p"},"output_value")," is equal to the filter ",(0,r.kt)("inlineCode",{parentName:"p"},"value")," and ",(0,r.kt)("inlineCode",{parentName:"p"},"input_key")," is present as a dimension."),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Property"),(0,r.kt)("th",{parentName:"tr",align:null},"Description"),(0,r.kt)("th",{parentName:"tr",align:null},"Required"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"type")),(0,r.kt)("td",{parentName:"tr",align:null},'Must be "extraction"'),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"dimension")),(0,r.kt)("td",{parentName:"tr",align:null},"Input column or virtual column name to filter on."),(0,r.kt)("td",{parentName:"tr",align:null},"Yes")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"value")),(0,r.kt)("td",{parentName:"tr",align:null},"String value to match."),(0,r.kt)("td",{parentName:"tr",align:null},"No. If not specified the filter will match NULL values.")),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"extractionFn")),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("a",{parentName:"td",href:"/docs/latest/querying/dimensionspecs#extraction-functions"},"Extraction function")," to apply to ",(0,r.kt)("inlineCode",{parentName:"td"},"dimension")," prior to value matching. See ",(0,r.kt)("a",{parentName:"td",href:"#filtering-with-extraction-functions"},"filtering with extraction functions")," for details."),(0,r.kt)("td",{parentName:"tr",align:null},"No")))),(0,r.kt)("h3",{id:"example-matching-dimension-values-in-product_1-product_3-product_5-for-the-column-product"},"Example: matching dimension values in ",(0,r.kt)("inlineCode",{parentName:"h3"},"[product_1, product_3, product_5]")," for the column ",(0,r.kt)("inlineCode",{parentName:"h3"},"product")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "filter": {\n        "type": "extraction",\n        "dimension": "product",\n        "value": "bar_1",\n        "extractionFn": {\n            "type": "lookup",\n            "lookup": {\n                "type": "map",\n                "map": {\n                    "product_1": "bar_1",\n                    "product_5": "bar_1",\n                    "product_3": "bar_1"\n                }\n            }\n        }\n    }\n}\n')),(0,r.kt)("h2",{id:"filtering-with-extraction-functions"},"Filtering with extraction functions"),(0,r.kt)("p",null,'All filters except the "spatial" filter support extraction functions.\nAn extraction function is defined by setting the "extractionFn" field on a filter.\nSee ',(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/dimensionspecs#extraction-functions"},"Extraction function")," for more details on extraction functions."),(0,r.kt)("p",null,'If specified, the extraction function will be used to transform input values before the filter is applied.\nThe example below shows a selector filter combined with an extraction function. This filter will transform input values\naccording to the values defined in the lookup map; transformed values will then be matched with the string "bar_1".'),(0,r.kt)("h3",{id:"example-matches-dimension-values-in-product_1-product_3-product_5-for-the-column-product"},"Example: matches dimension values in ",(0,r.kt)("inlineCode",{parentName:"h3"},"[product_1, product_3, product_5]")," for the column ",(0,r.kt)("inlineCode",{parentName:"h3"},"product")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "filter": {\n        "type": "selector",\n        "dimension": "product",\n        "value": "bar_1",\n        "extractionFn": {\n            "type": "lookup",\n            "lookup": {\n                "type": "map",\n                "map": {\n                    "product_1": "bar_1",\n                    "product_5": "bar_1",\n                    "product_3": "bar_1"\n                }\n            }\n        }\n    }\n}\n')),(0,r.kt)("h2",{id:"column-types"},"Column types"),(0,r.kt)("p",null,"Druid supports filtering on timestamp, string, long, and float columns."),(0,r.kt)("p",null,"Note that only string columns and columns produced with the ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/ingestion/ingestion-spec#dimension-objects"},"'auto' ingestion spec")," also used by ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/ingestion/schema-design#type-aware-schema-discovery"},"type aware schema discovery")," have bitmap indexes. Queries that filter on other column types must\nscan those columns."),(0,r.kt)("h3",{id:"filtering-on-multi-value-string-columns"},"Filtering on multi-value string columns"),(0,r.kt)("p",null,"All filters return true if any one of the dimension values is satisfies the filter."),(0,r.kt)("h4",{id:"example-multi-value-match-behavior"},"Example: multi-value match behavior"),(0,r.kt)("p",null,"Given a multi-value STRING row with values ",(0,r.kt)("inlineCode",{parentName:"p"},"['a', 'b', 'c']"),", a filter such as"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{ "type": "equals", "column": "someMultiValueColumn", "matchValueType": "STRING", "matchValue": "b" }\n')),(0,r.kt)("p",null,"will successfully match the entire row. This can produce sometimes unintuitive behavior when coupled with the implicit UNNEST functionality of Druid ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/groupbyquery"},"GroupBy")," and ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/topnquery"},"TopN")," queries."),(0,r.kt)("p",null,"Additionally, contradictory filters may be defined and perfectly legal in native queries which will not work in SQL."),(0,r.kt)("h4",{id:"example-sql-contradiction"},'Example: SQL "contradiction"'),(0,r.kt)("p",null,"This query is impossible to express as is in SQL since it is a contradiction that the SQL planner will optimize to false and match nothing."),(0,r.kt)("p",null,"Given a multi-value STRING row with values ",(0,r.kt)("inlineCode",{parentName:"p"},"['a', 'b', 'c']"),", and filter such as"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "and",\n  "fields": [\n    {\n      "type": "equals",\n      "column": "someMultiValueColumn",\n      "matchValueType": "STRING",\n      "matchValue": "a"\n    },\n    {\n      "type": "equals",\n      "column": "someMultiValueColumn",\n      "matchValueType": "STRING",\n      "matchValue": "b"\n    }\n  ]\n}\n')),(0,r.kt)("p",null,"will successfully match the entire row, but not match a row with value ",(0,r.kt)("inlineCode",{parentName:"p"},"['a', 'c']"),"."),(0,r.kt)("p",null,"To express this filter in SQL, use ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/sql-multivalue-string-functions"},"SQL multi-value string functions")," such as ",(0,r.kt)("inlineCode",{parentName:"p"},"MV_CONTAINS"),", which can be optimized by the planner to the same native filters."),(0,r.kt)("h3",{id:"filtering-on-numeric-columns"},"Filtering on numeric columns"),(0,r.kt)("p",null,"Some filters, such as equality and range filters allow accepting numeric match values directly since they include a secondary ",(0,r.kt)("inlineCode",{parentName:"p"},"matchValueType")," parameter."),(0,r.kt)("p",null,'When filtering on numeric columns using string based filters such as the selector, in, and bounds filters, you can write filter match values as if they were strings. In most cases, your filter will be\nconverted into a numeric predicate and will be applied to the numeric column values directly. In some cases (such as\nthe "regex" filter) the numeric column values will be converted to strings during the scan.'),(0,r.kt)("h4",{id:"example-filtering-on-a-specific-value-myfloatcolumn--101"},"Example: filtering on a specific value, ",(0,r.kt)("inlineCode",{parentName:"h4"},"myFloatColumn = 10.1")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "equals",\n  "dimension": "myFloatColumn",\n  "matchValueType": "FLOAT",\n  "value": 10.1\n}\n')),(0,r.kt)("p",null,"or with a selector filter:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "selector",\n  "dimension": "myFloatColumn",\n  "value": "10.1"\n}\n')),(0,r.kt)("h4",{id:"example-filtering-on-a-range-of-values-10--myfloatcolumn--20"},"Example: filtering on a range of values, ",(0,r.kt)("inlineCode",{parentName:"h4"},"10 <= myFloatColumn < 20")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "range",\n  "column": "myFloatColumn",\n  "matchvalueType": "FLOAT",\n  "lower": 10.1,\n  "lowerOpen": false,\n  "upper": 20.9,\n  "upperOpen": true\n}\n')),(0,r.kt)("p",null,"or with a bound filter:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "bound",\n  "dimension": "myFloatColumn",\n  "ordering": "numeric",\n  "lower": "10",\n  "lowerStrict": false,\n  "upper": "20",\n  "upperStrict": true\n}\n')),(0,r.kt)("h3",{id:"filtering-on-the-timestamp-column"},"Filtering on the timestamp column"),(0,r.kt)("p",null,"Query filters can also be applied to the timestamp column. The timestamp column has long millisecond values. To refer\nto the timestamp column, use the string ",(0,r.kt)("inlineCode",{parentName:"p"},"__time")," as the dimension name. Like numeric dimensions, timestamp filters\nshould be specified as if the timestamp values were strings."),(0,r.kt)("p",null,"If you want to interpret the timestamp with a specific format, timezone, or locale, the ",(0,r.kt)("a",{parentName:"p",href:"/docs/latest/querying/dimensionspecs#time-format-extraction-function"},"Time Format Extraction Function")," is useful."),(0,r.kt)("h4",{id:"example-filtering-on-a-long-timestamp-value"},"Example: filtering on a long timestamp value"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "equals",\n  "dimension": "__time",\n  "matchValueType": "LONG",\n  "value": 124457387532\n}\n')),(0,r.kt)("p",null,"or with a selector filter:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "selector",\n  "dimension": "__time",\n  "value": "124457387532"\n}\n')),(0,r.kt)("h4",{id:"example-filtering-on-day-of-week-using-an-extraction-function"},"Example: filtering on day of week using an extraction function"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n  "type": "selector",\n  "dimension": "__time",\n  "value": "Friday",\n  "extractionFn": {\n    "type": "timeFormat",\n    "format": "EEEE",\n    "timeZone": "America/New_York",\n    "locale": "en"\n  }\n}\n')),(0,r.kt)("h4",{id:"example-filtering-on-a-set-of-iso-8601-intervals"},"Example: filtering on a set of ISO 8601 intervals"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-json"},'{\n    "type" : "interval",\n    "dimension" : "__time",\n    "intervals" : [\n      "2014-10-01T00:00:00.000Z/2014-10-07T00:00:00.000Z",\n      "2014-11-15T00:00:00.000Z/2014-11-16T00:00:00.000Z"\n    ]\n}\n')))}k.isMDXComponent=!0}}]);