Filters

This guide shows how to write filters in query strings and pass them to the where table operation. The wouldMatch operation also evaluates filters, but instead of removing rows, it adds a boolean column showing which rows match.

Partitioned table operations also accept filters. The withFilter method of rollup and tree tables takes a Filter object, such as Filter.and(Filter.from("Sym == `AAPL`")).

The related whereIn and whereNotIn operations do not take filter expressions. Instead, they keep rows based on whether their values match values in another table. For an overview of every filtering operation, including filtering on another table and by row position, see Filter table data.

This page covers the types of filters, ways to combine them, how clause structure and order affect performance, and utility classes for specialized filtering.

What is a filter?

A filter is a query string expression that evaluates to true or false. Unlike formulas, which add new columns to a table, filters select rows. Deephaven keeps the rows where the filter is true.

Each filter string you pass to where is one clause: where("X > 2", "Y < 0") has two clauses, and where("X > 2 && Y < 0") has one.

Filters work the same on static and ticking tables. As rows are added, modified, or removed, the result of where updates to keep exactly the rows that pass.

The following preview keeps rows that match a value, fall in a range, satisfy a formula, and pass two clauses at once:

Types of filters

Match filters

A match filter evaluates to true if the column value matches a value specified in the filter.

Match filters can search for a single-value match:

You can apply match filters to a column of any data type:

To match any of several values in a column of any type, use the set-inclusion match filters:

  • in: Returns only rows where the column matches one of the specified values.
  • not in: Returns only rows where the column does not match any of the specified values.

Note

Match filters created using the equality and inequality operators (=, ==, and !=) follow standard IEEE 754 rules for handling NaN values. Any comparison involving NaN returns false, except !=, which returns true whenever either side is NaN.

In contrast, match filters created with set-inclusion syntax (in, not in) do match NaN values. For example, value in NaN, 10.0 returns true if value is NaN or 10.0.

Range filters

Range filters evaluate to true if the column value is within a specified range. You typically apply range filters to numeric columns, but you can apply them to any column that supports comparison operators.

Note

Range comparisons treat null as less than any non-null value, so < and <= include null values. To exclude them, add !isNull(X) as its own clause, as in where("!isNull(X)", "X < 10"). Because X < 10 stays in its own clause, it keeps its dedicated range filter. See isNull.

Range comparisons on floating-point values follow IEEE 754 and exclude NaN. To include NaN values, write the filter as isNaN(value) || value < 10.0. See isNaN.

To filter for data that is not in a range, use the ! operator or disjunctively combine two separate range filters:

Note

These forms are only equivalent for non-NaN values. Under standard IEEE 754 comparison rules, inRange returns false for NaN. So !inRange(X, 2, 6) includes NaN rows, but X < 2 || X > 6 excludes them.

Writing each bound as its own clause, as in where("X >= 2", "X <= 6"), can run faster than the && or inRange forms. See Keep match and range filters in their own clauses.

String filters

String filters return only rows that match the specified criteria for string columns. Because string column values are java.lang.String objects, a string filter can call any of their methods.

String filtering is flexible. Beyond the exact match shown in Match filters, string filters can match substrings, use regular expressions, and more. The following example shows substring and regular-expression filters:

In addition to in and not in, string columns support these filters:

  • icase in: Returns only rows where the string column matches one of the specified values, ignoring case.
  • icase not in: Returns only rows where the string column does not match any of the specified values, ignoring case.
  • includes any: Returns only rows where the string column contains any of the specified substrings.
  • includes all: Returns only rows where the string column contains all of the specified substrings.

Plain includes works like includes any. Two prefixes change how includes matches, and you can combine them, as in Name icase not includes `al`:

  • icase ignores case.
  • not keeps rows that don't contain the substring. With more than one substring, not includes any keeps rows that are missing at least one of them, and not includes all keeps rows that contain none of them.

Combine filters

You can combine filters conjunctively or disjunctively.

Conjunctive

Conjunctive filters return only rows that match all of the specified filters. You can conjunctively combine filters in the following ways:

The following example shows all three forms:

All three forms return the same rows, but they don't run the same way. A single && string compiles to one formula filter. The multiple-string and Filter.and forms keep each match or range filter in its own clause, with its dedicated implementation, so they can run faster. See Keep match and range filters in their own clauses.

Disjunctive

Disjunctive filters return only rows that match any of the specified filters. You can disjunctively combine filters in the following ways:

The following example shows both forms:

Use Filter.or when you build filters programmatically or when the logic is hard to express in one query string.

The following example shows how to use Filter.or to combine multiple conditions across different columns:

Filter performance

Three things drive filter cost:

  • Reading data: The engine reads the filtered column's data from its source, such as a disk or a network server. Filters can skip some of that reading with predicate pushdown. Match and range filters get the most pushdown optimizations, as the next section explains.
  • Evaluating the filter: A formula filter evaluates its whole expression for each row it examines. Match and range filters use faster dedicated implementations.
  • Building RowSets: Each filter builds a RowSet, the set of rows that pass it, and that work can be expensive.

You control that cost with two choices, covered in the next sections: how you split filters into clauses, and the order of those clauses.

These guidelines are rules of thumb. They don't produce the fastest query for every combination of filters and data. For important queries, measure performance to find the best order and structure for your filters.

Keep match and range filters in their own clauses

Deephaven uses a dedicated match-filter or range-filter implementation for a clause that compares one column with a value, such as X == 5, X in 1, 2, 3, or X > 10. Match and range filters can use predicate pushdown optimizations, such as binary search on sorted columns and Parquet row group metadata.

Deephaven compiles the following clauses as a formula filter, which evaluates the whole expression for each row:

  • A clause that joins several conditions with && or ||.
  • A function or method call, such as inRange in inRange(X, 2, 6) or Name.contains(`an`).
  • A comparison on the row variables i, ii, or k, such as ii > 5.

The set-inclusion filters (in, not in, icase in, icase not in) are match filters and keep their dedicated handling. The includes filters (includes any, includes all, and their icase and not forms) also get a dedicated string-pattern filter rather than a formula filter.

If you put a match or range filter in the same clause as other conditions, it becomes part of that formula and loses the dedicated handling. Keep it in its own clause:

Both produce the same result.

Put the most selective clause first

The engine evaluates clauses one after another. Earlier clauses reduce the rows that later clauses examine and the size of the RowSet each one builds. Put the clause that removes the most rows first. If none of the clauses is very selective, the order makes little difference.

The following example puts the filter on the rare category first:

The engine keeps the order you give, with two exceptions:

Pushdown reordering moves a stateless filter only within its run of adjacent stateless filters. A filter marked stateful ends that run. Pushdown never moves a stateful filter, and never moves another filter past it.

Neither kind of reordering moves a filter past a filter marked serial, or ahead of the filter that declares a Barrier it respects.

Filter utilities

Beyond query-string filters, Deephaven provides utility classes for specialized filtering scenarios, such as limiting initial data, keeping related tables synchronized, and marking rows within a time window.

TailInitializationFilter

TailInitializationFilter keeps only the most recent rows when a query initializes, so downstream operations start from a smaller input. After that, every new row passes. Use it when a query restarts mid-day against a large add-only table and only recent rows matter at startup.

It works per partition. A partition is one region of the source table, such as one file, or one contiguous range of row keys.

  • mostRecent keeps rows within a time period of each partition's newest timestamp. Each partition must be sorted by the timestamp column, with no null timestamps.
  • mostRecentRows keeps the last rows of each partition by position.

See the TailInitializationFilter reference page for usage examples.

SyncTableFilter and LeaderTableFilter

SyncTableFilter and LeaderTableFilter keep filtered results consistent across related tables that update at different rates. See Synchronize multiple tables for usage examples and guidance on choosing between these utilities.

WindowCheck

WindowCheck doesn't filter rows itself. It adds a boolean column that indicates whether each row's timestamp falls within a specified time window, updating as rows age out of the window. You can filter on that column yourself if you want to remove out-of-window rows. To add the column, call WindowCheck.addTimeWindow. Its reference page has usage examples.