where
The where
method filters rows of data from the source table.
The engine will address filters in series, consistent with the ordering of arguments. It is best practice to place filters related to partitioning and grouping columns first, as significant data volumes can then be excluded. Additionally, match filters are highly optimized and should usually come before conditional filters.
Syntax
table.where(filters...)
Parameters
Parameter | Type | Description |
---|---|---|
filters | String... | Formula for filtering. Filters can be:
|
filters | Collection | Collection of formulas for filtering. |
Returns
A new table with only the rows meeting the filter criteria in the column(s) of the source table.
Examples
The following example returns rows where Color
is blue
.
source = newTable(
stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),
intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),
stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),
intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),
)
result = source.where("Color = `blue`")
- source
- result
The following example returns rows where Number
is greater than 3.
source = newTable(
stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),
intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),
stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),
intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),
)
result = source.where("Number > 3")
- source
- result
The following returns rows where Color
is blue
and Number
is greater than 3.
source = newTable(
stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),
intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),
stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),
intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),
)
result = source.where("Color = `blue`", "Number > 3")
- source
- result
The following returns rows where Color
is blue
or Number
is greater than 3.
import io.deephaven.api.filter.FilterOr
import io.deephaven.api.filter.Filter
source = newTable(
stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),
intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),
stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),
intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),
)
result = source.where(FilterOr.of(Filter.from("Color = `blue`", "Number > 3")))
- source
- result
The following shows how to apply a custom function as a filter. Take note that the function call must be explicitly cast to a (boolean)
.
my_filter = { int a -> a <= 4 }
source = newTable(
intCol("IntegerColumn", 1, 2, 3, 4, 5, 6, 7, 8)
)
result_filtered = source.where("(boolean)my_filter(IntegerColumn)")
result_not_filtered = source.where("!((boolean)my_filter(IntegerColumn))")
- source
- result_filtered
- result_not_filtered