WHERE keyword
WHERE
clause filters data. Filter expressions are required to return boolean
result.
#
SyntaxThe general syntax is as follows. Specific filters have distinct syntaxes detailed thereafter.
#
Logical operatorsQuestDB supports AND
, OR
, NOT
as logical operators and can assemble
conditions using brackets ()
.
#
Symbol and stringQuestDB can filter strings and symbols based on equality, inequality, and regular expression patterns.
#
Exact matchEvaluates match of a string or symbol.
name | age |
---|---|
John | 31 |
John | 45 |
... | ... |
#
Does NOT matchEvaluates mismatch of a string or symbol.
name | age |
---|---|
Tim | 31 |
Tom | 45 |
... | ... |
#
Regular expression matchEvaluates match against a regular expression defined using java.util.regex patterns.
name | age |
---|---|
Joe | 31 |
Jonathan | 45 |
... | ... |
#
Regular expression does NOT matchEvaluates mismatch against a regular expression defined using java.util.regex patterns.
name | age |
---|---|
Tim | 31 |
Tom | 45 |
... | ... |
#
List searchEvaluates match or mismatch against a list of elements.
name | age |
---|---|
Tim | 31 |
Tom | 45 |
... | ... |
name | age |
---|---|
Aaron | 31 |
Amelie | 45 |
... | ... |
#
NumericQuestDB can filter numeric values based on equality, inequality, comparison, and proximity
note
For timestamp filters, we recommend the timestamp search notation which is faster and less verbose.
#
Equality, inequality and comparison#
ProximityEvaluates whether the column value is within a range of the target value. This
is useful to simulate equality on double
and float
values.
tip
When performing multiple equality checks of double values against integer constants, it may be preferable to store double values as long integers with a scaling factor.
#
BooleanUsing the columnName will return true
values. To return false
values,
precede the column name with the NOT
operator.
userId | isActive |
---|---|
12532 | true |
38572 | true |
... | ... |
userId | isActive |
---|---|
876534 | false |
43234 | false |
... | ... |
#
Timestamp and dateQuestDB supports both its own timestamp search notation and standard search
based on inequality. This section describes the use of the
timestamp search notation
which is efficient and fast but requires a
designated timestamp. Remember,
designated timestamp can be applied
dynamically.
#
Exact timestamp#
Syntaxts | score |
---|---|
2010-01-12T00:02:26.000Z | 2.4 |
2010-01-12T00:02:26.000Z | 3.1 |
... | ... |
ts | score |
---|---|
2010-01-12T00:02:26.000000Z | 2.4 |
2010-01-12T00:02:26.000000Z | 3.1 |
... | ... |
#
Time rangeReturn results within a defined range
#
Syntaxts | score |
---|---|
2018-01-01T00:0000.000000Z | 123.4 |
... | ... |
2018-12-31T23:59:59.999999Z | 115.8 |
ts | score |
---|---|
2018-05-23T12:15:00.000000Z | 123.4 |
... | ... |
2018-05-23T12:15:59.999999Z | 115.8 |
#
Time range with modifierYou can apply a modifier to further customise the range. The algorithm will calculate the resulting range by modifying the upper bound of the original range by the modifier parameter.
#
Syntaxmultiplier
is a signed integer.
- A
positive
value extends the interval. - A
negative
value reduces the interval.
The range is 2018. The modifier extends the upper bound (originally 31 Dec 2018) by one month.
ts | score |
---|---|
2018-01-01T00:00:00.000000Z | 123.4 |
... | ... |
2019-01-31T23:59:59.999999Z | 115.8 |
The range is Jan 2018. The modifier reduces the upper bound (originally 31 Dec 2018) by 3 days.
ts | score |
---|---|
2018-01-01T00:00:00.000000Z | 123.4 |
... | ... |
2019-01-28T23:59:59.999999Z | 115.8 |
#
Explicit range#
SyntaxFor non-standard ranges, users can explicitly specify the target range using the
in
operator.
lower_bound
and upper_bound
must be valid timestamps or dates and are
inclusive
.
ts | value |
---|---|
2018-01-01T00:00:23.000000Z | 123.4 |
... | ... |
2018-01-01T00:00:23.500000Z | 131.5 |