Filters

Filters narrow the rows an extraction job reads. You attach them per field, and Fetcher turns them into a WHERE clause on a relational engine or a query document on MongoDB.

Where filters live

Filters sit next to mappedFields in the request, four levels deep: datasource, then table, then field, then the operator object.

{
  "dataRequest": {
    "mappedFields": {
      "my_postgres": { "transactions": ["id", "amount", "status", "created_at"] }
    },
    "filters": {
      "my_postgres": {
        "transactions": {
          "status": { "in": ["completed", "pending"] },
          "amount": { "gt": [100], "lte": [5000] },
          "created_at": { "between": ["2026-06-01", "2026-06-30"] }
        }
      }
    }
  }
}

Every datasource named under filters must also appear under mappedFields. The Manager rejects a filter that points at an unknown datasource.

The ten operators

Every operator takes a JSON array, even when it holds one value. The array is the shape. What changes per operator is the number of elements.

Operator	Value shape	Result
`eq`	One or more values	One value matches with `=`. Two or more become `IN (…)`.
`gt`	Exactly one value	`field > value`
`gte`	Exactly one value	`field >= value`
`lt`	Exactly one value	`field < value`
`lte`	Exactly one value	`field <= value`
`between`	Exactly two values, `[min, max]`	`field >= min AND field <= max`, inclusive on both ends.
`in`	One or more values	`field IN (…)`
`nin`	One or more values	`field NOT IN (…)`
`ne`	One or more values	One value gives `field <> value`. Each extra value adds another `<>` condition.
`like`	One string pattern	`field LIKE pattern`, with the SQL wildcards `%` and `_`.

Examples of each shape:

{
  "status":      { "eq": ["active", "pending"] },
  "amount":      { "gt": [100] },
  "created_at":  { "gte": ["2026-06-01"] },
  "total":       { "lt": [1000] },
  "closed_at":   { "lte": ["2026-06-30"] },
  "value":       { "between": [100, 1000] },
  "state":       { "in": ["active", "pending", "suspended"] },
  "state_two":   { "nin": ["deleted", "archived"] },
  "kind":        { "ne": ["internal"] },
  "description": { "like": ["%refund%"] }
}

How operators combine

You can put several operators on the same field. Fetcher combines all of them with AND. The example { "gt": [100], "lte": [5000] } reads as amount > 100 AND amount <= 5000. Filters on different fields also combine with AND. There is no OR between fields. Use in when you need OR over the values of one field.

Validation rules

Fetcher rejects a filter before it builds the query when the value shape is wrong:

between with a count other than two fails with between operator for field 'X' must have exactly 2 values, got N.
gt, gte, lt, and lte with a count other than one fail with the matching message for that operator.

The other operators take the shape the table gives them:

like takes exactly one string pattern.
eq, in, nin, and ne take one or more values.

Fields that look like identifiers

On the relational engines, Fetcher inspects the field name. A name that contains id, _id, uuid, template_id, organization_id, user_id, or account_id is treated as a UUID field. Every string value under eq, gt, gte, lt, lte, between, in, and nin must then parse as a UUID. A value that does not parse fails the request and names the field. Two operators are outside this check: ne and like. MongoDB does not run the check at all.

Dates in a between filter

On the relational engines, Fetcher extends the upper bound of a between filter to the end of the day when three things hold at the same time:

The field name looks like a date field. It contains date, time, _at, created_at, updated_at, deleted_at, or completed_at.
Both bounds look like date strings.
The upper bound is exactly ten characters, in YYYY-MM-DD form.

Fetcher then rewrites the upper bound to YYYY-MM-DDT23:59:59.999Z. A filter of ["2026-06-01", "2026-06-30"] therefore includes the whole of 30 June. On MongoDB, both bounds apply exactly as you write them. To cover a whole day there, write the upper bound as a full timestamp: ["2026-06-01", "2026-06-30T23:59:59.999Z"].

Filters on MongoDB

MongoDB takes the same ten operators, and Fetcher translates them into query operators:

Operator	MongoDB form
`eq` with one value	`{ "field": value }`
`eq` with two or more	`$in`
`gt`, `gte`, `lt`, `lte`	`$gt`, `$gte`, `$lt`, `$lte`
`between`	`$gte` and `$lte`
`in`	`$in`
`nin`	`$nin`
`ne` with one value	`$ne`
`ne` with two or more	`$nin`
`like`	`$regex` with the `i` option

The like pattern becomes a regular expression: % turns into .*, and _ turns into .. Fetcher anchors the pattern at the start unless it opens with %, and at the end unless it closes with %. The i option makes the match case-insensitive.

Matching the table key

The table key under filters must find its table under mappedFields. PostgreSQL and SQL Server accept three forms of the key: the exact table name, the name without its schema prefix, and the name with the default schema added. A filter keyed transactions therefore still applies to the table public.transactions. MySQL, Oracle, and MongoDB match the key exactly. Write the key exactly as you wrote the table or collection name under mappedFields.

Overview

Getting started

Concepts

Engine

Integration

Operations

Where filters live

The ten operators

How operators combine

Validation rules

Fields that look like identifiers

Dates in a between filter

Filters on MongoDB

Matching the table key

Next steps

Extraction jobs

Datasources

​Where filters live

​The ten operators

​How operators combine

​Validation rules

​Fields that look like identifiers

​Dates in a between filter

​Filters on MongoDB

​Matching the table key

​Next steps

Extraction jobs

Datasources

Where filters live

The ten operators

How operators combine

Validation rules

Fields that look like identifiers

Dates in a between filter

Filters on MongoDB

Matching the table key

Next steps