# Dataset structured filters

Datasets support two kinds of filtering:

1. **SQL** — put a `WHERE` clause in your query. This is the most flexible and
runs inside DuckDB.
2. **Structured filters** — a typed filter tree sent alongside the query. The
service compiles it into a parameterized SQL predicate, so values are bound,
never interpolated.

The Hub's **Filters** panel builds structured filters for you. The same AST is
available in `QueryDatasetRequest.filter` (and `ExportDatasetRequest.filter`),
where values are bound as SQL parameters. Project-wide queries filter with SQL
`WHERE` clauses instead.

## UI filter builder

The Query tab starts with no filters. Use **Add filter**, choose a column and an
operator, enter a value, then press **Run**.

- **Match all / any** combines the conditions with AND or OR.
- Operators are limited to the selected column kind (see below).
- `in` and `not in` accept comma-separated values.
- Booleans use a true/false selector; timestamps use a date-time input.
- **is empty** / **is not empty** are `IS NULL` / `IS NOT NULL` and need no
value.
- Filters are not applied live; they take effect when the query runs.

## Operators

<table>
<thead>
  <tr>
    <th>
      Operator
    </th>
    
    <th>
      Enum
    </th>
    
    <th>
      Values
    </th>
    
    <th>
      Compiled predicate
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      Is
    </td>
    
    <td>
      <code>
        FILTER_OP_EQ
      </code>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      <code>
        "column" = ?
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Is not
    </td>
    
    <td>
      <code>
        FILTER_OP_NE
      </code>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      <code>
        "column" <> ?
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        >
      </code>
    </td>
    
    <td>
      <code>
        FILTER_OP_GT
      </code>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      <code>
        "column" > ?
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        >=
      </code>
    </td>
    
    <td>
      <code>
        FILTER_OP_GTE
      </code>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      <code>
        "column" >= ?
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        <
      </code>
    </td>
    
    <td>
      <code>
        FILTER_OP_LT
      </code>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      <code>
        "column" < ?
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        <=
      </code>
    </td>
    
    <td>
      <code>
        FILTER_OP_LTE
      </code>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      <code>
        "column" <= ?
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Contains
    </td>
    
    <td>
      <code>
        FILTER_OP_CONTAINS
      </code>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      <code>
        strpos("column", ?) > 0
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Starts with
    </td>
    
    <td>
      <code>
        FILTER_OP_STARTS_WITH
      </code>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      <code>
        starts_with("column", ?)
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Ends with
    </td>
    
    <td>
      <code>
        FILTER_OP_ENDS_WITH
      </code>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      <code>
        ends_with("column", ?)
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      In
    </td>
    
    <td>
      <code>
        FILTER_OP_IN
      </code>
    </td>
    
    <td>
      1+
    </td>
    
    <td>
      <code>
        "column" IN (?, …)
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Not in
    </td>
    
    <td>
      <code>
        FILTER_OP_NOT_IN
      </code>
    </td>
    
    <td>
      1+
    </td>
    
    <td>
      <code>
        "column" NOT IN (?, …)
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Is empty
    </td>
    
    <td>
      <code>
        FILTER_OP_IS_NULL
      </code>
    </td>
    
    <td>
      0
    </td>
    
    <td>
      <code>
        "column" IS NULL
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Is not empty
    </td>
    
    <td>
      <code>
        FILTER_OP_IS_NOT_NULL
      </code>
    </td>
    
    <td>
      0
    </td>
    
    <td>
      <code>
        "column" IS NOT NULL
      </code>
    </td>
  </tr>
</tbody>
</table>

Text operators are **case-sensitive** and literal: `contains` uses `strpos`, so
`%` and `_` have no wildcard meaning. Use SQL `ILIKE` if you need
case-insensitive matching.

## Groups and negation

```text
FilterNode
├── group      { any: bool, nodes: FilterNode[] }   AND when any = false
├── condition  { column, op, values: DatasetValue[] }
└── not        { node: FilterNode }
```

- An empty AND group compiles to `TRUE`; an empty OR group compiles to `FALSE`.
- `not` wraps any node: `NOT (<predicate>)`.
- Column names must be valid identifiers. They are quoted, and all values are
parameterized.

## Where filters run

This distinction matters when your SQL aggregates or renames columns.

### Single-dataset queries and exports

`QueryDataset` and `ExportDataset` apply the structured filter **outside** your
`SELECT`:

```sql
SELECT * FROM (<your SQL>) AS __bh_dataset_query WHERE <filter>
```

So the filter sees your query's **output columns**:

- With the default `SELECT * FROM dataset`, every schema column plus
`__ingested_at` and `__deleted` is available.
- If you project `SELECT host, count(*) AS n ...`, only `host` and `n` exist.
Referencing `severity` fails; filter on the output name or move the predicate
into the SQL `WHERE`.

### Project-wide queries

`QueryDatasets` does not use the structured filter builder. Filter inside the
SQL with a `WHERE` clause:

```sql
SELECT s.host, w.status_code
FROM subdomains AS s
JOIN "web-services" AS w ON s.host = w.host
WHERE s.resolved AND w.status_code BETWEEN 200 AND 299
```

The per-relation `filters` field on `QueryDatasetsRequest` is reserved and is
not supported by the current execution path.

### Views

`ExecuteDatasetView` has no filter or time-range fields. Put predicates in the
view SQL.

## Time ranges and history

<table>
<thead>
  <tr>
    <th>
      Field
    </th>
    
    <th>
      Applies to
    </th>
    
    <th>
      Notes
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      <code>
        ingestedAtFrom
      </code>
    </td>
    
    <td>
      <code>
        __ingested_at >= value
      </code>
    </td>
    
    <td>
      Same “outside your SELECT” rule as filters.
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        ingestedAtTo
      </code>
    </td>
    
    <td>
      <code>
        __ingested_at <= value
      </code>
    </td>
    
    <td>
      Must not be before <code>
        ingestedAtFrom
      </code>
      
      .
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        includeHistory
      </code>
    </td>
    
    <td>
      Switches the relation to raw observations
    </td>
    
    <td>
      Filters and time bounds apply to history too.
    </td>
  </tr>
</tbody>
</table>

The default `SELECT * FROM dataset` includes `__ingested_at`. If you project a
subset and set a time range, include `__ingested_at` in the projection, or
filter inside SQL.

The Hub's date-time inputs use the browser's local time zone. API clients send
RFC 3339 timestamps.

## API examples

### AND group

```json
{
  "filter": {
    "group": {
      "any": false,
      "nodes": [
        {
          "condition": {
            "column": "status_code",
            "op": "FILTER_OP_GTE",
            "values": [{ "integerValue": "200" }]
          }
        },
        {
          "condition": {
            "column": "status_code",
            "op": "FILTER_OP_LT",
            "values": [{ "integerValue": "300" }]
          }
        },
        {
          "condition": {
            "column": "ip",
            "op": "FILTER_OP_IS_NOT_NULL",
            "values": []
          }
        }
      ]
    }
  }
}
```

### OR group with a negation

```json
{
  "filter": {
    "group": {
      "any": true,
      "nodes": [
        {
          "condition": {
            "column": "severity",
            "op": "FILTER_OP_IN",
            "values": [{ "textValue": "high" }, { "textValue": "critical" }]
          }
        },
        {
          "not": {
            "node": {
              "condition": {
                "column": "program",
                "op": "FILTER_OP_EQ",
                "values": [{ "textValue": "internal" }]
              }
            }
          }
        }
      ]
    }
  }
}
```

`integerValue` is an int64 and therefore a JSON string. Timestamps are RFC 3339
strings inside `timestampValue`.

## Value types

<table>
<thead>
  <tr>
    <th>
      Column kind
    </th>
    
    <th>
      Value field
    </th>
    
    <th>
      Example
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      Text
    </td>
    
    <td>
      <code>
        textValue
      </code>
    </td>
    
    <td>
      <code>
        {"textValue":"api.example.com"}
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Integer
    </td>
    
    <td>
      <code>
        integerValue
      </code>
      
       (string)
    </td>
    
    <td>
      <code>
        {"integerValue":"200"}
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Number
    </td>
    
    <td>
      <code>
        numberValue
      </code>
    </td>
    
    <td>
      <code>
        {"numberValue":9.8}
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Boolean
    </td>
    
    <td>
      <code>
        booleanValue
      </code>
    </td>
    
    <td>
      <code>
        {"booleanValue":true}
      </code>
    </td>
  </tr>
  
  <tr>
    <td>
      Timestamp
    </td>
    
    <td>
      <code>
        timestampValue
      </code>
    </td>
    
    <td>
      <code>
        {"timestampValue":"2026-01-01T00:00:00Z"}
      </code>
    </td>
  </tr>
</tbody>
</table>

Values must match the column kind. The UI builds the correct value type from
the schema; API clients should do the same. A mismatch surfaces as a DuckDB
binder or conversion error.

SQL `NULL` comparisons never match, so `is = null` is not a useful filter — use
**is empty** (`IS NULL`).

## Errors

<table>
<thead>
  <tr>
    <th>
      Error
    </th>
    
    <th>
      Cause
    </th>
  </tr>
</thead>

<tbody>
  <tr>
    <td>
      <code>
        Invalid dataset filter
      </code>
    </td>
    
    <td>
      Unsupported operator, wrong number of values, or an empty value (except null checks).
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        DuckDB could not prepare the query
      </code>
    </td>
    
    <td>
      The column does not exist in the filtered scope, or a value type does not match.
    </td>
  </tr>
  
  <tr>
    <td>
      <code>
        Invalid filter for dataset '<name>'
      </code>
    </td>
    
    <td>
      A project-wide <code>
        RelationFilter
      </code>
      
       failed to compile against that relation.
    </td>
  </tr>
</tbody>
</table>

## Related

- [SQL reference](/datasets/sql) — write the query the filter wraps.
- [Overview](/datasets/overview#current-state-and-history) — current state vs
history.
- [API and CLI](/datasets/api-cli) — request shapes.