Advanced Filtering Operators

Uncontrolled filter parameters are one of the most common sources of API contract drift. When operators are implicitly parsed — different microservices accepting status__eq, status:eq, and filter[status][eq] interchangeably — clients cannot build reliable query builders, generated SDKs produce divergent types, and database indexes go unused. This page defines the canonical approach: specify every filter operator explicitly in the OpenAPI contract, enforce it in CI, and generate type-safe clients from that single source of truth. Part of the Query Patterns & Data Shaping Strategies reference.

For production-relevant boolean logic (AND / OR / NOT trees), see Handling Complex Boolean Filtering in REST APIs.

Spec Definition

Define every supported operator explicitly in the OpenAPI 3.1 filter parameter using anyOf to enforce mutually exclusive operator groups. additionalProperties: false closes the contract — unregistered operator tokens return a 400 before touching the database.

# openapi.yaml — OpenAPI 3.1.0
components:
  parameters:
    FilterParam:
      name: "filter"
      in: query
      required: false
      style: deepObject
      explode: true
      schema:
        type: object
        additionalProperties: false
        properties:
          status:
            type: object
            additionalProperties: false
            properties:
              eq:  { type: string, enum: ["active", "inactive", "pending"] }
              neq: { type: string, enum: ["active", "inactive", "pending"] }
              in:
                type: array
                items: { type: string, enum: ["active", "inactive", "pending"] }
                minItems: 1
                maxItems: 50
          created_at:
            type: object
            additionalProperties: false
            anyOf:
              - properties:
                  gte: { type: string, format: date-time }
                required: [gte]
              - properties:
                  lte: { type: string, format: date-time }
                required: [lte]
              - properties:
                  gte: { type: string, format: date-time }
                  lte: { type: string, format: date-time }
                required: [gte, lte]
          price:
            type: object
            additionalProperties: false
            properties:
              gte: { type: number, minimum: 0 }
              lte: { type: number, minimum: 0 }
              gt:  { type: number, minimum: 0 }
              lt:  { type: number, minimum: 0 }
          name:
            type: object
            additionalProperties: false
            properties:
              contains: { type: string, maxLength: 100 }
              eq:        { type: string, maxLength: 255 }
          category:
            type: object
            additionalProperties: false
            properties:
              in:
                type: array
                items: { type: string }
                minItems: 1
                maxItems: 20
              nin:
                type: array
                items: { type: string }
                minItems: 1
                maxItems: 20

The style: deepObject / explode: true combination maps directly to filter[status][eq]=active in URL-encoded query strings — no custom parsing, standard framework deserialization handles it.

RFC / Standard Alignment

Implementation Walkthrough

Step 1 — Server-side filter parser

The parser resolves each operator token to an ORM method or SQL fragment. Parameterized bindings prevent injection; the operator registry table ensures only known tokens can reach the query builder.

TypeScript (Express + Knex):

// src/filters/parse-filter.ts
import type { Knex } from "knex";

type ScalarOp = "eq" | "neq" | "gt" | "gte" | "lt" | "lte";
type SetOp    = "in" | "nin";
type TextOp   = "contains";
type Op       = ScalarOp | SetOp | TextOp;

const SCALAR_MAP: Record<ScalarOp, string> = {
  eq:  "=",  neq: "!=",
  gt:  ">",  gte: ">=",
  lt:  "<",  lte: "<=",
};

interface FilterInput {
  [field: string]: Partial<Record<Op, unknown>>;
}

/**
 * Applies validated filter parameters to a Knex query builder.
 * All values are bound as parameters — no string interpolation.
 */
export function applyFilters(
  qb: Knex.QueryBuilder,
  filter: FilterInput,
): Knex.QueryBuilder {
  for (const [field, ops] of Object.entries(filter)) {
    // Allowlist field names — reject anything not in the whitelist
    if (!/^[a-z_]+$/.test(field)) {
      throw new Error(`Invalid filter field: ${field}`);
    }

    for (const [op, value] of Object.entries(ops ?? {})) {
      if (op in SCALAR_MAP) {
        qb.where(field, SCALAR_MAP[op as ScalarOp], value as string | number);
      } else if (op === "in" && Array.isArray(value)) {
        qb.whereIn(field, value as string[]);
      } else if (op === "nin" && Array.isArray(value)) {
        qb.whereNotIn(field, value as string[]);
      } else if (op === "contains" && typeof value === "string") {
        // Use parameterized ILIKE, not string concatenation
        qb.whereILike(field, `%${value.replace(/[%_\\]/g, "\\$&")}%`);
      } else {
        throw new Error(`Unknown operator: ${op} on field ${field}`);
      }
    }
  }
  return qb;
}

Python (FastAPI + SQLAlchemy):

# app/filters/parse_filter.py
from typing import Any
from sqlalchemy.orm import Query
from sqlalchemy import Column, and_

SCALAR_OPS = {
    "eq": lambda col, v: col == v,
    "neq": lambda col, v: col != v,
    "gt":  lambda col, v: col > v,
    "gte": lambda col, v: col >= v,
    "lt":  lambda col, v: col < v,
    "lte": lambda col, v: col <= v,
}

def apply_filters(query: Query, model, filter_input: dict[str, Any]) -> Query:
    """
    Applies validated filter parameters to a SQLAlchemy query.
    All comparisons use SQLAlchemy column expressions — no raw SQL.
    """
    clauses = []
    for field, ops in filter_input.items():
        col: Column = getattr(model, field)
        for op, value in ops.items():
            if op in SCALAR_OPS:
                clauses.append(SCALAR_OPS[op](col, value))
            elif op == "in":
                clauses.append(col.in_(value))
            elif op == "nin":
                clauses.append(col.not_in(value))
            elif op == "contains":
                # Escape wildcards before handing to ILIKE
                safe = value.replace("%", r"\%").replace("_", r"\_")
                clauses.append(col.ilike(f"%{safe}%"))
            else:
                raise ValueError(f"Unknown operator '{op}' on field '{field}'")
    return query.filter(and_(*clauses)) if clauses else query

The field allowlist check prevents column injection even if a malformed request bypasses schema validation at the gateway.

Step 2 — Client-side validation and SDK integration

Generated SDKs surface the operator set as a strongly-typed builder. This example shows what openapi-generator produces from the spec above and how to extend it with a Zod validation layer for runtime safety in browser or edge environments.

TypeScript (generated + Zod runtime guard):

// src/filters/filter-schema.ts  — hand-written Zod mirror of the OpenAPI schema
import { z } from "zod";

const StatusFilter = z.object({
  eq:  z.enum(["active", "inactive", "pending"]).optional(),
  neq: z.enum(["active", "inactive", "pending"]).optional(),
  in:  z.array(z.enum(["active", "inactive", "pending"])).min(1).max(50).optional(),
}).strict();  // .strict() rejects unknown operator keys at runtime

const DateFilter = z.union([
  z.object({ gte: z.string().datetime() }).strict(),
  z.object({ lte: z.string().datetime() }).strict(),
  z.object({ gte: z.string().datetime(), lte: z.string().datetime() }).strict(),
]);

const CategoryFilter = z.object({
  in:  z.array(z.string()).min(1).max(20).optional(),
  nin: z.array(z.string()).min(1).max(20).optional(),
}).strict();

export const FilterSchema = z.object({
  status:     StatusFilter.optional(),
  created_at: DateFilter.optional(),
  category:   CategoryFilter.optional(),
}).strict();

export type Filter = z.infer<typeof FilterSchema>;

// Usage in API client
import { apiClient } from "./api-client";

async function fetchItems(filter: Filter) {
  FilterSchema.parse(filter);  // throws ZodError with path + message on invalid input
  return apiClient.get("/v1/items", { params: { filter } });
}

Python (Pydantic v2 model):

# app/schemas/filter_schema.py
from pydantic import BaseModel, Field, model_validator
from typing import Literal, Optional
from datetime import datetime

class StatusFilter(BaseModel):
    model_config = {"extra": "forbid"}  # rejects unknown operators
    eq:  Optional[Literal["active", "inactive", "pending"]] = None
    neq: Optional[Literal["active", "inactive", "pending"]] = None
    in_: Optional[list[Literal["active", "inactive", "pending"]]] = Field(
        None, alias="in", min_length=1, max_length=50
    )

class DateFilter(BaseModel):
    model_config = {"extra": "forbid"}
    gte: Optional[datetime] = None
    lte: Optional[datetime] = None

    @model_validator(mode="after")
    def at_least_one_bound(self) -> "DateFilter":
        if self.gte is None and self.lte is None:
            raise ValueError("date filter requires at least one of gte or lte")
        return self

class ItemFilter(BaseModel):
    model_config = {"extra": "forbid"}
    status:     Optional[StatusFilter] = None
    created_at: Optional[DateFilter]   = None
    category:   Optional["CategoryFilter"] = None

Edge-Case Handling

Null and missing values. A filter[status][eq]= (empty string) is distinct from omitting the parameter entirely. The OpenAPI schema should mark fields with minLength: 1 to reject empty-string equality filters; the server parser treats an absent filter key as “no constraint”, not “null equals”.

Arrays exceeding maxItems. Return 400 with an RFC 9457 Problem Details body listing the offending parameter path. Do not silently truncate — truncation changes query semantics invisibly.

Conflicting range bounds. filter[price][gte]=100&filter[price][lte]=50 is a valid parse but an impossible range. Detect this at the validation layer and return a 422 Unprocessable Content with a clear detail field rather than executing a query that returns zero rows without explanation.

in with a single value. Treat filter[category][in][]=electronics (one element) identically to filter[category][eq]=electronics at the query builder level — both should hit the same index path.

Interaction with Offset vs Cursor Pagination. When cursor pagination encodes sort position, filters must be applied before the cursor predicate in the WHERE clause. A cursor generated without a filter must not be reused with a different filter set — the encoded position no longer corresponds to a stable position in the filtered result set. Return a 400 if the client attempts to pass a mismatched cursor.

Interaction with Sorting & Multi-Field Ordering. Composite indexes should cover the filter fields first, then the sort fields, to avoid index intersection. Validate combined query plans in staging using EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL or EXPLAIN in MySQL.

Validation and Testing Patterns

Spectral rule — reject unknown operators:

# .spectral.yaml
rules:
  filter-additionalProperties-closed:
    description: >
      Filter parameter schemas must set additionalProperties: false
      to prevent undocumented operator tokens from reaching the query planner.
    given: >
      $.components.parameters[?(@.name == "filter")].schema.properties.*
    severity: error
    then:
      field: additionalProperties
      function: falsy

  filter-deepObject-style:
    description: Filter parameters must use deepObject style for predictable serialization.
    given: $.components.parameters[?(@.name == "filter")]
    severity: error
    then:
      - field: style
        function: pattern
        functionOptions: { match: "^deepObject$" }
      - field: explode
        function: truthy

Contract test — operator boundary cases (Node.js / Vitest):

// tests/filter-contract.test.ts
import { describe, it, expect } from "vitest";
import { buildApp } from "../src/app";

const app = buildApp();

describe("filter operator contract", () => {
  it("returns 400 for an unknown operator token", async () => {
    const res = await app.inject({
      method: "GET",
      url: "/v1/items?filter[status][like]=act",
    });
    expect(res.statusCode).toBe(400);
    const body = JSON.parse(res.body);
    expect(body.type).toMatch(/validation-error/);
    expect(body["invalid-params"]).toContainEqual(
      expect.objectContaining({ name: "filter.status.like" })
    );
  });

  it("returns 400 for empty in[] array", async () => {
    const res = await app.inject({
      method: "GET",
      url: "/v1/items?filter[category][in][]=",
    });
    expect(res.statusCode).toBe(400);
  });

  it("accepts a valid combined filter", async () => {
    const res = await app.inject({
      method: "GET",
      url: "/v1/items?filter[status][eq]=active&filter[price][gte]=10",
    });
    expect(res.statusCode).toBe(200);
  });

  it("returns 422 for impossible range (gte > lte)", async () => {
    const res = await app.inject({
      method: "GET",
      url: "/v1/items?filter[price][gte]=200&filter[price][lte]=50",
    });
    expect(res.statusCode).toBe(422);
  });
});

Run against a seeded test database so operator resolution and index paths are exercised end-to-end, not just at the parsing layer.

SDK Generation Impact

The deepObject / additionalProperties: false combination has direct consequences for generated clients:

TypeScript (typescript-axios generator). Each filter field becomes a typed interface property. Unknown operator keys raise a TypeScript compile error — no cast needed. The in array gets typed as string[] with length constraints surfaced as JSDoc comments (though not enforced at runtime by the generated code alone, which is why the Zod layer above matters).

Python (python generator, Pydantic v2 mode). The anyOf on DateFilter generates a Union type. Pydantic’s extra = "forbid" mirrors additionalProperties: false. The alias="in" pattern is necessary because in is a Python keyword.

Go (go generator). The generator emits a struct with pointer fields for optional operators. omitempty JSON tags mean absent operators are not serialized, which matches the intended contract.

Breaking-change detection. Adding a new operator (between, startsWith) to an existing field is non-breaking for clients — they ignore keys they do not know. Removing an operator is breaking. Use openapi-diff in CI to gate SDK re-publishing:

openapi-diff openapi.yaml ./baseline/openapi.yaml \
  --fail-on-breaking \
  --report drift-report.json

# Only publish new SDK version if no breaking changes detected
if [ $? -eq 0 ]; then
  openapi-generator-cli generate \
    -i openapi.yaml \
    -g typescript-axios \
    -o ./sdk-ts \
    --additional-properties=npmVersion=$(cat package.json | jq -r .version)
fi

Anti-Patterns Quick Reference

FAQ

Related

• Query Patterns & Data Shaping Strategies — parent reference
• Handling Complex Boolean Filtering in REST APIs — AND / OR / NOT operator trees
• Offset vs Cursor Pagination — combining filters with pagination strategies
• Sorting & Multi-Field Ordering — composite indexes for filter + sort paths
• Sparse Fieldsets & Projection — reduce payload size alongside complex filters