API Filtering, Sorting, Pagination, and Field Selection

Introduction

Collection endpoints often need more than a single fixed response: clients may need to filter records, choose a stable order, move through pages, or request only a few fields. Each option changes the API contract and the database work required to serve it.

This guide compares offset and cursor pagination, shows how to validate filters and field masks, and explains why authorization scope and bounded query cost matter. It also covers failure modes and monitoring for collection APIs.

Pagination choices

Offset pagination is easy to understand: limit=25&offset=50. It works well for small datasets, but deep offsets can require scanning many rows, and insertions between page requests can shift results. Cursor pagination instead continues after a stable position. The cursor should be opaque to clients and encode or reference the last sort values, including the unique tie-breaker. Cursors are not automatically snapshots; if a client needs a consistent export while data changes, use a snapshot or export job.

GET /api/orders?status=shipped&limit=25&sort=-createdAt HTTP/1.1
Accept: application/json

HTTP/1.1 200 OK
Content-Type: application/json

{"items":[{"id":"ord_92","status":"shipped"}],"nextCursor":"cD0yMDI2LTA5LTMw...","hasMore":true}

Query and response flow

flowchart LR
    A[Parse query parameters] --> B[Validate allowlisted fields]
    B --> C[Apply tenant and permission scope]
    C --> D[Add deterministic ordering]
    D --> E[Fetch bounded page]
    E --> F[Return items and continuation cursor]

Authorization scope must be applied before filters and pagination. Otherwise the API may leak counts or return cursors over records the caller cannot access.

When to use and when not to use

Use filters, sorting, and pagination for collections that may grow or are searched in user interfaces. Offer field selection when representations are large and client needs differ. Keep a narrow fixed response if the object is small; dynamic field masks can complicate caching, schemas, and authorization. Do not promise arbitrary filtering or sorting unless you can support its query cost and index behavior.

Trade-off Analysis

Implementation snippet

Parse a small allowlist, clamp limits, enforce scope, and use a stable order:

const SORTS = { createdAt: "created_at", id: "id" } as const;
function parseListQuery(url: URL) {
  const requestedLimit = Number(url.searchParams.get("limit") ?? 25);
  if (!Number.isSafeInteger(requestedLimit) || requestedLimit < 1) {
    throw new HttpError(400, "invalid_limit");
  }
  const limit = Math.min(requestedLimit, 100);
  const sort = url.searchParams.get("sort") ?? "-createdAt";
  const field = sort.startsWith("-") ? sort.slice(1) : sort;
  if (!Object.hasOwn(SORTS, field)) throw new HttpError(400, "invalid_sort");
  return {
    limit,
    sortColumn: SORTS[field as keyof typeof SORTS],
    descending: sort.startsWith("-"),
  };
}

The repository query should include tenant scope, then the validated filters, then an index-backed order and limit + 1 to determine whether another page exists.

Production failure scenarios and mitigations

A client requests limit=1000000, exhausting memory; clamp limits and reject unreasonable values. A sort parameter interpolates raw SQL and enables injection; map known names to trusted columns. Offset pagination skips or repeats rows during frequent inserts; use cursor pagination over a deterministic order. A cursor is reused after permissions change; reapply authorization on every page and treat cursors as navigation hints, not access grants. An unindexed filter drives database CPU high; monitor query plans and restrict supported combinations.

Observability checklist

• Track requested and effective page sizes, response bytes, and query latency.
• Measure slow query rates by normalized filter and sort shape, not raw sensitive values.
• Watch invalid query rates and cursor expiration or decode failures.
• Alert on high-cardinality filter patterns that correlate with database saturation.

Security notes and pitfalls

Treat all query values as untrusted. Allowlist fields and operators, parameterize values, enforce per-tenant scope, and never expose fields that authorization forbids. Opaque cursors may be signed to detect tampering, but still reauthorize each request. Common mistakes include unstable sorting, unlimited page size, leaking total counts, interpreting empty filters inconsistently, and assuming a cursor itself grants permission.

Quick Recap Checklist

• Allowlist filter and sort fields, and validate requested values.
• Use a deterministic sort order with a unique tie-breaker for cursor pagination.
• Set a server-side maximum page size and define behavior for invalid limits.
• Return only authorized fields and avoid exposing sensitive data through field selection.
• Apply tenant permissions before pagination and measure query costs in production.

Interview Questions

Further Reading

• API Resource Names, Relationships, and Collections — Collection design, route structure, and relationship choices.
• RESTful API Design — Broader resource and HTTP conventions.
• Google API Improvement Proposal 132: List — Conventions for listing collections.
• Google API Improvement Proposal 158: Pagination — Page tokens, bounded page sizes, and continuation behavior.
• Google API Improvement Proposal 161: Field Masks — A standard approach to selecting response fields.

Conclusion

Collection query options are part of the API contract and part of the server’s resource budget. Keep them bounded, predictable, permission-aware, and backed by measured query plans.