blob: 0e22332474d21fa7eecdf8bd3ebc136f94645964 [file] [log] [blame]
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="Apache Druid">
<meta name="keywords" content="druid,kafka,database,analytics,streaming,real-time,real time,apache,open source">
<meta name="author" content="Apache Software Foundation">
<title>Druid | Query Filters</title>
<link rel="alternate" type="application/atom+xml" href="/feed">
<link rel="shortcut icon" href="/img/favicon.png">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.2/css/all.css" integrity="sha384-fnmOCqbTlWIlj8LyTjo7mOUStjsKC4pOpQbqyi7RrhN7udi9RwhKkMHpvLbHG9Sr" crossorigin="anonymous">
<link href='//fonts.googleapis.com/css?family=Open+Sans+Condensed:300,700,300italic|Open+Sans:300italic,400italic,600italic,400,300,600,700' rel='stylesheet' type='text/css'>
<link rel="stylesheet" href="/css/bootstrap-pure.css?v=1.1">
<link rel="stylesheet" href="/css/base.css?v=1.1">
<link rel="stylesheet" href="/css/header.css?v=1.1">
<link rel="stylesheet" href="/css/footer.css?v=1.1">
<link rel="stylesheet" href="/css/syntax.css?v=1.1">
<link rel="stylesheet" href="/css/docs.css?v=1.1">
<script>
(function() {
var cx = '000162378814775985090:molvbm0vggm';
var gcse = document.createElement('script');
gcse.type = 'text/javascript';
gcse.async = true;
gcse.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') +
'//cse.google.com/cse.js?cx=' + cx;
var s = document.getElementsByTagName('script')[0];
s.parentNode.insertBefore(gcse, s);
})();
</script>
</head>
<body>
<!-- Start page_header include -->
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<div class="top-navigator">
<div class="container">
<div class="left-cont">
<a class="logo" href="/"><span class="druid-logo"></span></a>
</div>
<div class="right-cont">
<ul class="links">
<li class=""><a href="/technology">Technology</a></li>
<li class=""><a href="/use-cases">Use Cases</a></li>
<li class=""><a href="/druid-powered">Powered By</a></li>
<li class=""><a href="/docs/latest/design/">Docs</a></li>
<li class=""><a href="/community/">Community</a></li>
<li class="header-dropdown">
<a>Apache</a>
<div class="header-dropdown-menu">
<a href="https://www.apache.org/" target="_blank">Foundation</a>
<a href="https://www.apache.org/events/current-event" target="_blank">Events</a>
<a href="https://www.apache.org/licenses/" target="_blank">License</a>
<a href="https://www.apache.org/foundation/thanks.html" target="_blank">Thanks</a>
<a href="https://www.apache.org/security/" target="_blank">Security</a>
<a href="https://www.apache.org/foundation/sponsorship.html" target="_blank">Sponsorship</a>
</div>
</li>
<li class=" button-link"><a href="/downloads.html">Download</a></li>
</ul>
</div>
</div>
<div class="action-button menu-icon">
<span class="fa fa-bars"></span> MENU
</div>
<div class="action-button menu-icon-close">
<span class="fa fa-times"></span> MENU
</div>
</div>
<script type="text/javascript">
var $menu = $('.right-cont');
var $menuIcon = $('.menu-icon');
var $menuIconClose = $('.menu-icon-close');
function showMenu() {
$menu.fadeIn(100);
$menuIcon.fadeOut(100);
$menuIconClose.fadeIn(100);
}
$menuIcon.click(showMenu);
function hideMenu() {
$menu.fadeOut(100);
$menuIconClose.fadeOut(100);
$menuIcon.fadeIn(100);
}
$menuIconClose.click(hideMenu);
$(window).resize(function() {
if ($(window).width() >= 840) {
$menu.fadeIn(100);
$menuIcon.fadeOut(100);
$menuIconClose.fadeOut(100);
}
else {
$menu.fadeOut(100);
$menuIcon.fadeIn(100);
$menuIconClose.fadeOut(100);
}
});
</script>
<!-- Stop page_header include -->
<div class="container doc-container">
<p> Looking for the <a href="/docs/0.23.0/">latest stable documentation</a>?</p>
<div class="row">
<div class="col-md-9 doc-content">
<p>
<a class="btn btn-default btn-xs visible-xs-inline-block visible-sm-inline-block" href="#toc">Table of Contents</a>
</p>
<!--
~ Licensed to the Apache Software Foundation (ASF) under one
~ or more contributor license agreements. See the NOTICE file
~ distributed with this work for additional information
~ regarding copyright ownership. The ASF licenses this file
~ to you under the Apache License, Version 2.0 (the
~ "License"); you may not use this file except in compliance
~ with the License. You may obtain a copy of the License at
~
~ http://www.apache.org/licenses/LICENSE-2.0
~
~ Unless required by applicable law or agreed to in writing,
~ software distributed under the License is distributed on an
~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
~ KIND, either express or implied. See the License for the
~ specific language governing permissions and limitations
~ under the License.
-->
<h1 id="query-filters">Query Filters</h1>
<p>A filter is a JSON object indicating which rows of data should be included in the computation for a query. It’s essentially the equivalent of the WHERE clause in SQL. Apache Druid (incubating) supports the following types of filters.</p>
<h3 id="selector-filter">Selector filter</h3>
<p>The simplest filter is a selector filter. The selector filter will match a specific dimension with a specific value. Selector filters can be used as the base filters for more complex Boolean expressions of filters.</p>
<p>The grammar for a SELECTOR filter is as follows:</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span> <span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;selector&quot;</span><span class="p">,</span> <span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="err">&lt;dime</span><span class="kc">ns</span><span class="err">io</span><span class="kc">n</span><span class="err">_s</span><span class="kc">tr</span><span class="err">i</span><span class="kc">n</span><span class="err">g&gt;</span><span class="p">,</span> <span class="nt">&quot;value&quot;</span><span class="p">:</span> <span class="err">&lt;dime</span><span class="kc">ns</span><span class="err">io</span><span class="kc">n</span><span class="err">_value_s</span><span class="kc">tr</span><span class="err">i</span><span class="kc">n</span><span class="err">g&gt;</span> <span class="p">}</span>
</code></pre></div>
<p>This is the equivalent of <code>WHERE &lt;dimension_string&gt; = &#39;&lt;dimension_value_string&gt;&#39;</code>.</p>
<p>The selector filter supports the use of extraction functions, see <a href="#filtering-with-extraction-functions">Filtering with Extraction Functions</a> for details.</p>
<h3 id="column-comparison-filter">Column Comparison filter</h3>
<p>The column comparison filter is similar to the selector filter, but instead compares dimensions to each other. For example:</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span> <span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;columnComparison&quot;</span><span class="p">,</span> <span class="nt">&quot;dimensions&quot;</span><span class="p">:</span> <span class="p">[</span><span class="err">&lt;dime</span><span class="kc">ns</span><span class="err">io</span><span class="kc">n</span><span class="err">_a&gt;</span><span class="p">,</span> <span class="err">&lt;dime</span><span class="kc">ns</span><span class="err">io</span><span class="kc">n</span><span class="err">_b&gt;</span><span class="p">]</span> <span class="p">}</span>
</code></pre></div>
<p>This is the equivalent of <code>WHERE &lt;dimension_a&gt; = &lt;dimension_b&gt;</code>.</p>
<p><code>dimensions</code> is list of <a href="./dimensionspecs.html">DimensionSpecs</a>, making it possible to apply an extraction function if needed.</p>
<h3 id="regular-expression-filter">Regular expression filter</h3>
<p>The regular expression filter is similar to the selector filter, but using regular expressions. It matches the specified dimension with the given pattern. The pattern can be any standard <a href="http://docs.oracle.com/javase/6/docs/api/java/util/regex/Pattern.html">Java regular expression</a>.</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span> <span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;regex&quot;</span><span class="p">,</span> <span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="err">&lt;dime</span><span class="kc">ns</span><span class="err">io</span><span class="kc">n</span><span class="err">_s</span><span class="kc">tr</span><span class="err">i</span><span class="kc">n</span><span class="err">g&gt;</span><span class="p">,</span> <span class="nt">&quot;pattern&quot;</span><span class="p">:</span> <span class="err">&lt;pa</span><span class="kc">ttern</span><span class="err">_s</span><span class="kc">tr</span><span class="err">i</span><span class="kc">n</span><span class="err">g&gt;</span> <span class="p">}</span>
</code></pre></div>
<p>The regex filter supports the use of extraction functions, see <a href="#filtering-with-extraction-functions">Filtering with Extraction Functions</a> for details.</p>
<h3 id="logical-expression-filters">Logical expression filters</h3>
<h4 id="and">AND</h4>
<p>The grammar for an AND filter is as follows:</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span> <span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;and&quot;</span><span class="p">,</span> <span class="nt">&quot;fields&quot;</span><span class="p">:</span> <span class="p">[</span><span class="err">&lt;</span><span class="kc">f</span><span class="err">il</span><span class="kc">ter</span><span class="err">&gt;</span><span class="p">,</span> <span class="err">&lt;</span><span class="kc">f</span><span class="err">il</span><span class="kc">ter</span><span class="err">&gt;</span><span class="p">,</span> <span class="err">...</span><span class="p">]</span> <span class="p">}</span>
</code></pre></div>
<p>The filters in fields can be any other filter defined on this page.</p>
<h4 id="or">OR</h4>
<p>The grammar for an OR filter is as follows:</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span> <span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;or&quot;</span><span class="p">,</span> <span class="nt">&quot;fields&quot;</span><span class="p">:</span> <span class="p">[</span><span class="err">&lt;</span><span class="kc">f</span><span class="err">il</span><span class="kc">ter</span><span class="err">&gt;</span><span class="p">,</span> <span class="err">&lt;</span><span class="kc">f</span><span class="err">il</span><span class="kc">ter</span><span class="err">&gt;</span><span class="p">,</span> <span class="err">...</span><span class="p">]</span> <span class="p">}</span>
</code></pre></div>
<p>The filters in fields can be any other filter defined on this page.</p>
<h4 id="not">NOT</h4>
<p>The grammar for a NOT filter is as follows:</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span> <span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;not&quot;</span><span class="p">,</span> <span class="nt">&quot;field&quot;</span><span class="p">:</span> <span class="err">&lt;</span><span class="kc">f</span><span class="err">il</span><span class="kc">ter</span><span class="err">&gt;</span> <span class="p">}</span>
</code></pre></div>
<p>The filter specified at field can be any other filter defined on this page.</p>
<h3 id="javascript-filter">JavaScript filter</h3>
<p>The JavaScript filter matches a dimension against the specified JavaScript function predicate. The filter matches values for which the function returns true.</p>
<p>The function takes a single argument, the dimension value, and returns either true or false.</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="p">{</span>
<span class="nt">&quot;type&quot;</span> <span class="p">:</span> <span class="s2">&quot;javascript&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span> <span class="p">:</span> <span class="err">&lt;dime</span><span class="kc">ns</span><span class="err">io</span><span class="kc">n</span><span class="err">_s</span><span class="kc">tr</span><span class="err">i</span><span class="kc">n</span><span class="err">g&gt;</span><span class="p">,</span>
<span class="nt">&quot;function&quot;</span> <span class="p">:</span> <span class="s2">&quot;function(value) { &lt;...&gt; }&quot;</span>
<span class="p">}</span>
</code></pre></div>
<p><strong>Example</strong>
The following matches any dimension values for the dimension <code>name</code> between <code>&#39;bar&#39;</code> and <code>&#39;foo&#39;</code></p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="p">{</span>
<span class="nt">&quot;type&quot;</span> <span class="p">:</span> <span class="s2">&quot;javascript&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span> <span class="p">:</span> <span class="s2">&quot;name&quot;</span><span class="p">,</span>
<span class="nt">&quot;function&quot;</span> <span class="p">:</span> <span class="s2">&quot;function(x) { return(x &gt;= &#39;bar&#39; &amp;&amp; x &lt;= &#39;foo&#39;) }&quot;</span>
<span class="p">}</span>
</code></pre></div>
<p>The JavaScript filter supports the use of extraction functions, see <a href="#filtering-with-extraction-functions">Filtering with Extraction Functions</a> for details.</p>
<div class="note info">
JavaScript-based functionality is disabled by default. Please refer to the Druid <a href="../development/javascript.html">JavaScript programming guide</a> for guidelines about using Druid's JavaScript functionality, including instructions on how to enable it.
</div>
<h3 id="extraction-filter">Extraction filter</h3>
<div class="note caution">
The extraction filter is now deprecated. The selector filter with an extraction function specified
provides identical functionality and should be used instead.
</div>
<p>Extraction filter matches a dimension using some specific <a href="./dimensionspecs.html#extraction-functions">Extraction function</a>.
The following filter matches the values for which the extraction function has transformation entry <code>input_key=output_value</code> where
<code>output_value</code> is equal to the filter <code>value</code> and <code>input_key</code> is present as dimension.</p>
<p><strong>Example</strong>
The following matches dimension values in <code>[product_1, product_3, product_5]</code> for the column <code>product</code></p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="p">{</span>
<span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;extraction&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="s2">&quot;product&quot;</span><span class="p">,</span>
<span class="nt">&quot;value&quot;</span><span class="p">:</span> <span class="s2">&quot;bar_1&quot;</span><span class="p">,</span>
<span class="nt">&quot;extractionFn&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;lookup&quot;</span><span class="p">,</span>
<span class="nt">&quot;lookup&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;map&quot;</span><span class="p">,</span>
<span class="nt">&quot;map&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;product_1&quot;</span><span class="p">:</span> <span class="s2">&quot;bar_1&quot;</span><span class="p">,</span>
<span class="nt">&quot;product_5&quot;</span><span class="p">:</span> <span class="s2">&quot;bar_1&quot;</span><span class="p">,</span>
<span class="nt">&quot;product_3&quot;</span><span class="p">:</span> <span class="s2">&quot;bar_1&quot;</span>
<span class="p">}</span>
<span class="p">}</span>
<span class="p">}</span>
<span class="p">}</span>
<span class="p">}</span>
</code></pre></div>
<h3 id="search-filter">Search filter</h3>
<p>Search filters can be used to filter on partial string matches. </p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="p">{</span>
<span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;search&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="s2">&quot;product&quot;</span><span class="p">,</span>
<span class="nt">&quot;query&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;insensitive_contains&quot;</span><span class="p">,</span>
<span class="nt">&quot;value&quot;</span><span class="p">:</span> <span class="s2">&quot;foo&quot;</span>
<span class="p">}</span>
<span class="p">}</span>
<span class="p">}</span>
</code></pre></div>
<table><thead>
<tr>
<th>property</th>
<th>description</th>
<th>required?</th>
</tr>
</thead><tbody>
<tr>
<td>type</td>
<td>This String should always be &quot;search&quot;.</td>
<td>yes</td>
</tr>
<tr>
<td>dimension</td>
<td>The dimension to perform the search over.</td>
<td>yes</td>
</tr>
<tr>
<td>query</td>
<td>A JSON object for the type of search. See below for more information.</td>
<td>yes</td>
</tr>
<tr>
<td>extractionFn</td>
<td><a href="#filtering-with-extraction-functions">Extraction function</a> to apply to the dimension</td>
<td>no</td>
</tr>
</tbody></table>
<p>The search filter supports the use of extraction functions, see <a href="#filtering-with-extraction-functions">Filtering with Extraction Functions</a> for details.</p>
<h4 id="search-query-spec">Search Query Spec</h4>
<h5 id="contains">Contains</h5>
<table><thead>
<tr>
<th>property</th>
<th>description</th>
<th>required?</th>
</tr>
</thead><tbody>
<tr>
<td>type</td>
<td>This String should always be &quot;contains&quot;.</td>
<td>yes</td>
</tr>
<tr>
<td>value</td>
<td>A String value to run the search over.</td>
<td>yes</td>
</tr>
<tr>
<td>caseSensitive</td>
<td>Whether two string should be compared as case sensitive or not</td>
<td>no (default == false)</td>
</tr>
</tbody></table>
<h5 id="insensitive-contains">Insensitive Contains</h5>
<table><thead>
<tr>
<th>property</th>
<th>description</th>
<th>required?</th>
</tr>
</thead><tbody>
<tr>
<td>type</td>
<td>This String should always be &quot;insensitive_contains&quot;.</td>
<td>yes</td>
</tr>
<tr>
<td>value</td>
<td>A String value to run the search over.</td>
<td>yes</td>
</tr>
</tbody></table>
<p>Note that an &quot;insensitive_contains&quot; search is equivalent to a &quot;contains&quot; search with &quot;caseSensitive&quot;: false (or not
provided).</p>
<h5 id="fragment">Fragment</h5>
<table><thead>
<tr>
<th>property</th>
<th>description</th>
<th>required?</th>
</tr>
</thead><tbody>
<tr>
<td>type</td>
<td>This String should always be &quot;fragment&quot;.</td>
<td>yes</td>
</tr>
<tr>
<td>values</td>
<td>A JSON array of String values to run the search over.</td>
<td>yes</td>
</tr>
<tr>
<td>caseSensitive</td>
<td>Whether strings should be compared as case sensitive or not. Default: false(insensitive)</td>
<td>no</td>
</tr>
</tbody></table>
<h3 id="in-filter">In filter</h3>
<p>In filter can be used to express the following SQL query:</p>
<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span></span> <span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="s1">&#39;Count&#39;</span> <span class="k">FROM</span> <span class="o">`</span><span class="k">table</span><span class="o">`</span> <span class="k">WHERE</span> <span class="o">`</span><span class="n">outlaw</span><span class="o">`</span> <span class="k">IN</span> <span class="p">(</span><span class="s1">&#39;Good&#39;</span><span class="p">,</span> <span class="s1">&#39;Bad&#39;</span><span class="p">,</span> <span class="s1">&#39;Ugly&#39;</span><span class="p">)</span>
</code></pre></div>
<p>The grammar for a IN filter is as follows:</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;in&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="s2">&quot;outlaw&quot;</span><span class="p">,</span>
<span class="nt">&quot;values&quot;</span><span class="p">:</span> <span class="p">[</span><span class="s2">&quot;Good&quot;</span><span class="p">,</span> <span class="s2">&quot;Bad&quot;</span><span class="p">,</span> <span class="s2">&quot;Ugly&quot;</span><span class="p">]</span>
<span class="p">}</span>
</code></pre></div>
<p>The IN filter supports the use of extraction functions, see <a href="#filtering-with-extraction-functions">Filtering with Extraction Functions</a> for details.</p>
<p>If an empty <code>values</code> array is passed to the IN filter, it will simply return an empty result.</p>
<h3 id="like-filter">Like filter</h3>
<p>Like filters can be used for basic wildcard searches. They are equivalent to the SQL LIKE operator. Special characters
supported are &quot;%&quot; (matches any number of characters) and &quot;_&quot; (matches any one character).</p>
<table><thead>
<tr>
<th>property</th>
<th>type</th>
<th>description</th>
<th>required?</th>
</tr>
</thead><tbody>
<tr>
<td>type</td>
<td>String</td>
<td>This should always be &quot;like&quot;.</td>
<td>yes</td>
</tr>
<tr>
<td>dimension</td>
<td>String</td>
<td>The dimension to filter on</td>
<td>yes</td>
</tr>
<tr>
<td>pattern</td>
<td>String</td>
<td>LIKE pattern, such as &quot;foo%&quot; or &quot;___bar&quot;.</td>
<td>yes</td>
</tr>
<tr>
<td>escape</td>
<td>String</td>
<td>An escape character that can be used to escape special characters.</td>
<td>no</td>
</tr>
<tr>
<td>extractionFn</td>
<td><a href="#filtering-with-extraction-functions">Extraction function</a></td>
<td>Extraction function to apply to the dimension</td>
<td>no</td>
</tr>
</tbody></table>
<p>Like filters support the use of extraction functions, see <a href="#filtering-with-extraction-functions">Filtering with Extraction Functions</a> for details.</p>
<p>This Like filter expresses the condition <code>last_name LIKE &quot;D%&quot;</code> (i.e. last_name starts with &quot;D&quot;).</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;like&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="s2">&quot;last_name&quot;</span><span class="p">,</span>
<span class="nt">&quot;pattern&quot;</span><span class="p">:</span> <span class="s2">&quot;D%&quot;</span>
<span class="p">}</span>
</code></pre></div>
<h3 id="bound-filter">Bound filter</h3>
<p>Bound filters can be used to filter on ranges of dimension values. It can be used for comparison filtering like
greater than, less than, greater than or equal to, less than or equal to, and &quot;between&quot; (if both &quot;lower&quot; and
&quot;upper&quot; are set).</p>
<table><thead>
<tr>
<th>property</th>
<th>type</th>
<th>description</th>
<th>required?</th>
</tr>
</thead><tbody>
<tr>
<td>type</td>
<td>String</td>
<td>This should always be &quot;bound&quot;.</td>
<td>yes</td>
</tr>
<tr>
<td>dimension</td>
<td>String</td>
<td>The dimension to filter on</td>
<td>yes</td>
</tr>
<tr>
<td>lower</td>
<td>String</td>
<td>The lower bound for the filter</td>
<td>no</td>
</tr>
<tr>
<td>upper</td>
<td>String</td>
<td>The upper bound for the filter</td>
<td>no</td>
</tr>
<tr>
<td>lowerStrict</td>
<td>Boolean</td>
<td>Perform strict comparison on the lower bound (&quot;&gt;&quot; instead of &quot;&gt;=&quot;)</td>
<td>no, default: false</td>
</tr>
<tr>
<td>upperStrict</td>
<td>Boolean</td>
<td>Perform strict comparison on the upper bound (&quot;&lt;&quot; instead of &quot;&lt;=&quot;)</td>
<td>no, default: false</td>
</tr>
<tr>
<td>ordering</td>
<td>String</td>
<td>Specifies the sorting order to use when comparing values against the bound. Can be one of the following values: &quot;lexicographic&quot;, &quot;alphanumeric&quot;, &quot;numeric&quot;, &quot;strlen&quot;, &quot;version&quot;. See <a href="./sorting-orders.html">Sorting Orders</a> for more details.</td>
<td>no, default: &quot;lexicographic&quot;</td>
</tr>
<tr>
<td>extractionFn</td>
<td><a href="#filtering-with-extraction-functions">Extraction function</a></td>
<td>Extraction function to apply to the dimension</td>
<td>no</td>
</tr>
</tbody></table>
<p>Bound filters support the use of extraction functions, see <a href="#filtering-with-extraction-functions">Filtering with Extraction Functions</a> for details.</p>
<p>The following bound filter expresses the condition <code>21 &lt;= age &lt;= 31</code>:
<code>json
{
&quot;type&quot;: &quot;bound&quot;,
&quot;dimension&quot;: &quot;age&quot;,
&quot;lower&quot;: &quot;21&quot;,
&quot;upper&quot;: &quot;31&quot; ,
&quot;ordering&quot;: &quot;numeric&quot;
}
</code></p>
<p>This filter expresses the condition <code>foo &lt;= name &lt;= hoo</code>, using the default lexicographic sorting order.
<code>json
{
&quot;type&quot;: &quot;bound&quot;,
&quot;dimension&quot;: &quot;name&quot;,
&quot;lower&quot;: &quot;foo&quot;,
&quot;upper&quot;: &quot;hoo&quot;
}
</code></p>
<p>Using strict bounds, this filter expresses the condition <code>21 &lt; age &lt; 31</code>
<code>json
{
&quot;type&quot;: &quot;bound&quot;,
&quot;dimension&quot;: &quot;age&quot;,
&quot;lower&quot;: &quot;21&quot;,
&quot;lowerStrict&quot;: true,
&quot;upper&quot;: &quot;31&quot; ,
&quot;upperStrict&quot;: true,
&quot;ordering&quot;: &quot;numeric&quot;
}
</code></p>
<p>The user can also specify a one-sided bound by omitting &quot;upper&quot; or &quot;lower&quot;. This filter expresses <code>age &lt; 31</code>.
<code>json
{
&quot;type&quot;: &quot;bound&quot;,
&quot;dimension&quot;: &quot;age&quot;,
&quot;upper&quot;: &quot;31&quot; ,
&quot;upperStrict&quot;: true,
&quot;ordering&quot;: &quot;numeric&quot;
}
</code></p>
<p>Likewise, this filter expresses <code>age &gt;= 18</code>
<code>json
{
&quot;type&quot;: &quot;bound&quot;,
&quot;dimension&quot;: &quot;age&quot;,
&quot;lower&quot;: &quot;18&quot; ,
&quot;ordering&quot;: &quot;numeric&quot;
}
</code></p>
<h3 id="interval-filter">Interval Filter</h3>
<p>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 <code>__time</code> column, long metric columns, and dimensions with values that can be parsed as long milliseconds.</p>
<p>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 &lt;= time &lt; end).</p>
<table><thead>
<tr>
<th>property</th>
<th>type</th>
<th>description</th>
<th>required?</th>
</tr>
</thead><tbody>
<tr>
<td>type</td>
<td>String</td>
<td>This should always be &quot;interval&quot;.</td>
<td>yes</td>
</tr>
<tr>
<td>dimension</td>
<td>String</td>
<td>The dimension to filter on</td>
<td>yes</td>
</tr>
<tr>
<td>intervals</td>
<td>Array</td>
<td>A JSON array containing ISO-8601 interval strings. This defines the time ranges to filter on.</td>
<td>yes</td>
</tr>
<tr>
<td>extractionFn</td>
<td><a href="#filtering-with-extraction-functions">Extraction function</a></td>
<td>Extraction function to apply to the dimension</td>
<td>no</td>
</tr>
</tbody></table>
<p>The interval filter supports the use of extraction functions, see <a href="#filtering-with-extraction-functions">Filtering with Extraction Functions</a> for details.</p>
<p>If an extraction function is used with this filter, the extraction function should output values that are parseable as long milliseconds.</p>
<p>The following example filters on the time ranges of October 1-7, 2014 and November 15-16, 2014.
<code>json
{
&quot;type&quot; : &quot;interval&quot;,
&quot;dimension&quot; : &quot;__time&quot;,
&quot;intervals&quot; : [
&quot;2014-10-01T00:00:00.000Z/2014-10-07T00:00:00.000Z&quot;,
&quot;2014-11-15T00:00:00.000Z/2014-11-16T00:00:00.000Z&quot;
]
}
</code></p>
<p>The filter above is equivalent to the following OR of Bound filters:</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;or&quot;</span><span class="p">,</span>
<span class="nt">&quot;fields&quot;</span><span class="p">:</span> <span class="p">[</span>
<span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;bound&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="s2">&quot;__time&quot;</span><span class="p">,</span>
<span class="nt">&quot;lower&quot;</span><span class="p">:</span> <span class="s2">&quot;1412121600000&quot;</span><span class="p">,</span>
<span class="nt">&quot;lowerStrict&quot;</span><span class="p">:</span> <span class="kc">false</span><span class="p">,</span>
<span class="nt">&quot;upper&quot;</span><span class="p">:</span> <span class="s2">&quot;1412640000000&quot;</span> <span class="p">,</span>
<span class="nt">&quot;upperStrict&quot;</span><span class="p">:</span> <span class="kc">true</span><span class="p">,</span>
<span class="nt">&quot;ordering&quot;</span><span class="p">:</span> <span class="s2">&quot;numeric&quot;</span>
<span class="p">},</span>
<span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;bound&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="s2">&quot;__time&quot;</span><span class="p">,</span>
<span class="nt">&quot;lower&quot;</span><span class="p">:</span> <span class="s2">&quot;1416009600000&quot;</span><span class="p">,</span>
<span class="nt">&quot;lowerStrict&quot;</span><span class="p">:</span> <span class="kc">false</span><span class="p">,</span>
<span class="nt">&quot;upper&quot;</span><span class="p">:</span> <span class="s2">&quot;1416096000000&quot;</span> <span class="p">,</span>
<span class="nt">&quot;upperStrict&quot;</span><span class="p">:</span> <span class="kc">true</span><span class="p">,</span>
<span class="nt">&quot;ordering&quot;</span><span class="p">:</span> <span class="s2">&quot;numeric&quot;</span>
<span class="p">}</span>
<span class="p">]</span>
<span class="p">}</span>
</code></pre></div>
<h3 id="filtering-with-extraction-functions">Filtering with Extraction Functions</h3>
<p>All filters except the &quot;spatial&quot; filter support extraction functions.
An extraction function is defined by setting the &quot;extractionFn&quot; field on a filter.
See <a href="./dimensionspecs.html#extraction-functions">Extraction function</a> for more details on extraction functions.</p>
<p>If specified, the extraction function will be used to transform input values before the filter is applied.
The example below shows a selector filter combined with an extraction function. This filter will transform input values
according to the values defined in the lookup map; transformed values will then be matched with the string &quot;bar_1&quot;.</p>
<p><strong>Example</strong>
The following matches dimension values in <code>[product_1, product_3, product_5]</code> for the column <code>product</code></p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="p">{</span>
<span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;selector&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="s2">&quot;product&quot;</span><span class="p">,</span>
<span class="nt">&quot;value&quot;</span><span class="p">:</span> <span class="s2">&quot;bar_1&quot;</span><span class="p">,</span>
<span class="nt">&quot;extractionFn&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;lookup&quot;</span><span class="p">,</span>
<span class="nt">&quot;lookup&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;map&quot;</span><span class="p">,</span>
<span class="nt">&quot;map&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;product_1&quot;</span><span class="p">:</span> <span class="s2">&quot;bar_1&quot;</span><span class="p">,</span>
<span class="nt">&quot;product_5&quot;</span><span class="p">:</span> <span class="s2">&quot;bar_1&quot;</span><span class="p">,</span>
<span class="nt">&quot;product_3&quot;</span><span class="p">:</span> <span class="s2">&quot;bar_1&quot;</span>
<span class="p">}</span>
<span class="p">}</span>
<span class="p">}</span>
<span class="p">}</span>
<span class="p">}</span>
</code></pre></div>
<h2 id="column-types">Column types</h2>
<p>Druid supports filtering on timestamp, string, long, and float columns.</p>
<p>Note that only string columns have bitmap indexes. Therefore, queries that filter on other column types will need to
scan those columns.</p>
<h3 id="filtering-on-numeric-columns">Filtering on numeric columns</h3>
<p>When filtering on numeric columns, you can write filters as if they were strings. In most cases, your filter will be
converted into a numeric predicate and will be applied to the numeric column values directly. In some cases (such as
the &quot;regex&quot; filter) the numeric column values will be converted to strings during the scan.</p>
<p>For example, filtering on a specific value, <code>myFloatColumn = 10.1</code>:</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;selector&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="s2">&quot;myFloatColumn&quot;</span><span class="p">,</span>
<span class="nt">&quot;value&quot;</span><span class="p">:</span> <span class="s2">&quot;10.1&quot;</span>
<span class="p">}</span>
</code></pre></div>
<p>Filtering on a range of values, <code>10 &lt;= myFloatColumn &lt; 20</code>:</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;bound&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="s2">&quot;myFloatColumn&quot;</span><span class="p">,</span>
<span class="nt">&quot;ordering&quot;</span><span class="p">:</span> <span class="s2">&quot;numeric&quot;</span><span class="p">,</span>
<span class="nt">&quot;lower&quot;</span><span class="p">:</span> <span class="s2">&quot;10&quot;</span><span class="p">,</span>
<span class="nt">&quot;lowerStrict&quot;</span><span class="p">:</span> <span class="kc">false</span><span class="p">,</span>
<span class="nt">&quot;upper&quot;</span><span class="p">:</span> <span class="s2">&quot;20&quot;</span><span class="p">,</span>
<span class="nt">&quot;upperStrict&quot;</span><span class="p">:</span> <span class="kc">true</span>
<span class="p">}</span>
</code></pre></div>
<h3 id="filtering-on-the-timestamp-column">Filtering on the Timestamp Column</h3>
<p>Query filters can also be applied to the timestamp column. The timestamp column has long millisecond values. To refer
to the timestamp column, use the string <code>__time</code> as the dimension name. Like numeric dimensions, timestamp filters
should be specified as if the timestamp values were strings.</p>
<p>If the user wishes to interpret the timestamp with a specific format, timezone, or locale, the <a href="./dimensionspecs.html#time-format-extraction-function">Time Format Extraction Function</a> is useful.</p>
<p>For example, filtering on a long timestamp value:</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;selector&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="s2">&quot;__time&quot;</span><span class="p">,</span>
<span class="nt">&quot;value&quot;</span><span class="p">:</span> <span class="s2">&quot;124457387532&quot;</span>
<span class="p">}</span>
</code></pre></div>
<p>Filtering on day of week:</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="nt">&quot;filter&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;selector&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span><span class="p">:</span> <span class="s2">&quot;__time&quot;</span><span class="p">,</span>
<span class="nt">&quot;value&quot;</span><span class="p">:</span> <span class="s2">&quot;Friday&quot;</span><span class="p">,</span>
<span class="nt">&quot;extractionFn&quot;</span><span class="p">:</span> <span class="p">{</span>
<span class="nt">&quot;type&quot;</span><span class="p">:</span> <span class="s2">&quot;timeFormat&quot;</span><span class="p">,</span>
<span class="nt">&quot;format&quot;</span><span class="p">:</span> <span class="s2">&quot;EEEE&quot;</span><span class="p">,</span>
<span class="nt">&quot;timeZone&quot;</span><span class="p">:</span> <span class="s2">&quot;America/New_York&quot;</span><span class="p">,</span>
<span class="nt">&quot;locale&quot;</span><span class="p">:</span> <span class="s2">&quot;en&quot;</span>
<span class="p">}</span>
<span class="p">}</span>
</code></pre></div>
<p>Filtering on a set of ISO 8601 intervals:</p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="p">{</span>
<span class="nt">&quot;type&quot;</span> <span class="p">:</span> <span class="s2">&quot;interval&quot;</span><span class="p">,</span>
<span class="nt">&quot;dimension&quot;</span> <span class="p">:</span> <span class="s2">&quot;__time&quot;</span><span class="p">,</span>
<span class="nt">&quot;intervals&quot;</span> <span class="p">:</span> <span class="p">[</span>
<span class="s2">&quot;2014-10-01T00:00:00.000Z/2014-10-07T00:00:00.000Z&quot;</span><span class="p">,</span>
<span class="s2">&quot;2014-11-15T00:00:00.000Z/2014-11-16T00:00:00.000Z&quot;</span>
<span class="p">]</span>
<span class="p">}</span>
</code></pre></div>
<h3 id="true-filter">True Filter</h3>
<p>The true filter is a filter which matches all values. It can be used to temporarily disable other filters without removing the filter. </p>
<div class="highlight"><pre><code class="language-json" data-lang="json"><span></span><span class="p">{</span> <span class="nt">&quot;type&quot;</span> <span class="p">:</span> <span class="s2">&quot;true&quot;</span> <span class="p">}</span>
</code></pre></div>
</div>
<div class="col-md-3">
<div class="searchbox">
<gcse:searchbox-only></gcse:searchbox-only>
</div>
<div id="toc" class="nav toc hidden-print">
</div>
</div>
</div>
</div>
<!-- Start page_footer include -->
<footer class="druid-footer">
<div class="container">
<div class="text-center">
<p>
<a href="/technology">Technology</a>&ensp;·&ensp;
<a href="/use-cases">Use Cases</a>&ensp;·&ensp;
<a href="/druid-powered">Powered by Druid</a>&ensp;·&ensp;
<a href="/docs/latest/">Docs</a>&ensp;·&ensp;
<a href="/community/">Community</a>&ensp;·&ensp;
<a href="/downloads.html">Download</a>&ensp;·&ensp;
<a href="/faq">FAQ</a>
</p>
</div>
<div class="text-center">
<a title="Join the user group" href="https://groups.google.com/forum/#!forum/druid-user" target="_blank"><span class="fa fa-comments"></span></a>&ensp;·&ensp;
<a title="Follow Druid" href="https://twitter.com/druidio" target="_blank"><span class="fab fa-twitter"></span></a>&ensp;·&ensp;
<a title="GitHub" href="https://github.com/apache/druid" target="_blank"><span class="fab fa-github"></span></a>
</div>
<div class="text-center license">
Copyright © 2020 <a href="https://www.apache.org/" target="_blank">Apache Software Foundation</a>.<br>
Except where otherwise noted, licensed under <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">CC BY-SA 4.0</a>.<br>
Apache Druid, Druid, and the Druid logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.
</div>
</div>
</footer>
<script async src="https://www.googletagmanager.com/gtag/js?id=UA-131010415-1"></script>
<script>
window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments);}
gtag('js', new Date());
gtag('config', 'UA-131010415-1');
</script>
<script>
function trackDownload(type, url) {
ga('send', 'event', 'download', type, url);
}
</script>
<script src="//code.jquery.com/jquery.min.js"></script>
<script src="//maxcdn.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js"></script>
<script src="/assets/js/druid.js"></script>
<!-- stop page_footer include -->
<script>
$(function() {
$(".toc").load("/docs/0.14.1-incubating/toc.html");
// There is no way to tell when .gsc-input will be async loaded into the page so just try to set a placeholder until it works
var tries = 0;
var timer = setInterval(function() {
tries++;
if (tries > 300) clearInterval(timer);
var searchInput = $('input.gsc-input');
if (searchInput.length) {
searchInput.attr('placeholder', 'Search');
clearInterval(timer);
}
}, 100);
});
</script>
</body>
</html>