Filters

The ten Fetcher filter operators, their value shapes, and how each datasource applies them.

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

---

On relational engines, non-empty operators on the same field combine with AND. The example { "gt": [100], "lte": [5000] } reads as amount > 100 AND amount <= 5000.

The current MongoDB adapter does not safely combine eq with another operator on the same field, or two operators that write the same MongoDB key. A later assignment can replace the earlier condition. Use one non-conflicting operator per MongoDB field.

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

---

For non-empty arrays, Fetcher validates between as two values and gt, gte, lt, and lte as one value. Empty arrays add no condition.

like applies only when its first value is a string. Extra values, an empty array, and a non-string first value are currently ignored rather than rejected. eq, in, nin, and ne use the values they receive when the array is non-empty.

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:

1. The field name looks like a date field. It contains date, time, _at, created_at, updated_at, deleted_at, or completed_at.
2. Both bounds satisfy Fetcher’s date-like string heuristic: at least ten characters, a hyphen, and either ten characters or a T.
3. The upper bound is ten characters long.

This is a field-name and string-shape heuristic, not calendar-date validation. When it applies, Fetcher rewrites the upper bound to YYYY-MM-DDT23:59:59.999Z.

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 and MongoDB match the key literally. Oracle normalizes identifiers to uppercase, so its table-key matching is case-insensitive; it does not add or remove a default schema.

Next steps

---

Extraction jobs

The full job request and the path from creation to a stored result.

Datasources

What behaves differently on each of the five database engines.