Skip to main content
Version: Java (Groovy)

where

The where method filters rows of data from the source table.

note

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

ParameterTypeDescription
filtersString...

Formula for filtering.

Filters can be:

  • A match filter.
  • A conditional filter. This may be a custom boolean function.
filtersCollection

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`")

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")

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")

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")))

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))")