Lesson 06 of 07 · published

Filtering, Sorting & Sparse Fieldsets — The Query Toolkit

~10 min · rest-design, filtering, sorting, fieldsets

Level 0HTTP Newbie

0 XP0/46 lessons0/12 achievements

0/120 XP to next level120 XP to go0% complete

"Once you have pagination, the next three things clients ask for are: 'filter by X,' 'sort by Y,' and 'give me only fields A, B, C.' These don't need GraphQL — they need consistent query conventions."

Four Common Query Surfaces

List endpoints accumulate four kinds of query parameters as they grow:

Filtering — narrow the set: ?status=active&role=admin
Sorting — order the set: ?sort=-created_at,name
Sparse fieldsets — trim each item: ?fields=id,name
Related includes — expand related resources: ?include=orders,profile

Each has multiple competing conventions. Pick one and stick to it across your whole API; consistency matters more than which specific convention you chose.

Filtering Conventions

Simple equality (most common): ?status=active means "items where status equals active." ?status=active&role=admin means AND. Easy to parse, easy to document, covers 80% of needs.

Comma-separated for IN: ?status=active,pending means "status is one of these." Some APIs use repeated params instead (?status=active&status=pending) — both common.

Operator suffix for ranges: ?created_at_gte=2026-01-01&created_at_lt=2026-06-01. The suffix convention varies (Django uses __gte, REST APIs often _gte or [gte]=).

RSQL / FIQL for complex queries: ?filter=status==active;role==admin;created_at=ge=2026-01-01. A mini query language. Rarely worth the complexity unless you have a true "power user" segment.

When filtering needs a query body: if your filter is genuinely complex (nested boolean logic, large IN lists, full-text with operators), switch to POST + JSON body. URL length limits are real (most servers cap at 8KB). That's no longer GET — it's a POST that doesn't change state, which violates safety in spirit but is the practical workaround. Document the deviation.

Sorting Conventions

Three common patterns:

Sign prefix (terse): ?sort=-created_at,name — minus prefix for descending, comma for multi-field. Most compact; widely understood.
Colon suffix: ?sort=created_at:desc,name:asc — explicit, more verbose, friendlier for some tooling.
JSON:API style: ?sort=-created_at,name (same as sign prefix). JSON:API standardized this.

Validate allowed sort fields server-side — if you let clients sort on arbitrary columns, you've created a denial-of-service via unindexed sorts. Whitelist: {created_at, updated_at, name} allowed; everything else rejected with 400.

Sparse Fieldsets — Save Bytes Without GraphQL

Sometimes clients only need a few fields. Why download the full user record when a typeahead just needs {id, display_name}?

GET /users/42?fields=id,display_name
→ {"id":"u_42","display_name":"Pippa"}

GET /users?fields=id,display_name,avatar_url
→ {"items":[{"id":"u_42","display_name":"Pippa","avatar_url":"..."}, ...]}

JSON:API formalizes per-type fieldsets: ?fields[user]=name,email&fields[order]=total. Useful when responses include multiple resource types via includes.

Bandwidth savings can be 10x+ for clients that only need summaries. Implementation cost: server filters fields in the serializer; caching layer needs Vary: query (or specifically Vary: Accept-Encoding + query-aware caching) so different field selections aren't served stale.

Related Includes — The 'Avoid N+1' Query

A user has orders. Clients displaying a user often need the orders too. Two ways to deliver:

N+1 (bad): GET /users/42, then for each order GET /orders/{id}. N requests for one user.
Include parameter (good): GET /users/42?include=orders returns the user plus the related orders in one shot.

JSON:API standardizes this with a top-level included array; ad-hoc APIs nest the related resources inline. The point is the same: avoid the request-amplification trap.

Query conventions are a contract — be consistent. If your /users endpoint uses ?sort=-created_at, your /orders endpoint shouldn't use ?sort_by=created_at&order=desc. The protocol gives clients no help; only your consistency does. Pick one convention per axis and apply it everywhere.

When to Reach for GraphQL or POST-Body Queries

If your clients regularly want "give me this user, plus their last 10 orders, with only certain fields per order, plus the user's account balance" — that's GraphQL territory or POST-body-query territory. Query parameters were never designed for that complexity.

The signs you've outgrown query parameters:

URLs approaching 2KB (or hitting CDN/proxy limits).
Filter logic with nested AND/OR/NOT.
Clients regularly assembling 5+ query parameters.
Documentation that requires a separate "query syntax" page.

Reach for GraphQL only if you have many clients with many different query needs. For most REST APIs, query parameters + sparse fieldsets + includes are enough.

cwkPippa's Query Reality

cwkPippa's filtering surface is minimal: ?brain=claude on session lists, ?archived=true, ?folder_id=.... No sorting parameter (default created_at desc); no fieldsets (responses are already lean); no includes (the React client is fine with N+1 because everything is local). When the council list grew past 100 rounds, the existing offset pagination + ?status=active filter handled it. Query complexity has stayed low; query conventions have stayed simple. That's not luck — it's a deliberate "don't grow features without proving you need them" stance.

Code

Combine query, sort, fields, include in one endpoint·bash

# The four common query surfaces — in one endpoint

# Filtering (equality + IN list + range)
curl 'https://api.example.com/users?role=admin&status=active,pending&created_at_gte=2026-01-01'

# Sorting (multi-field, sign-prefix for descending)
curl 'https://api.example.com/users?sort=-created_at,name'

# Sparse fieldsets (trim each item)
curl 'https://api.example.com/users?fields=id,display_name,avatar_url'

# Related includes (avoid N+1)
curl 'https://api.example.com/users/42?include=orders,profile'

# Pagination on top (Lesson 3.5)
curl 'https://api.example.com/users?cursor=usr_abc&limit=20&sort=-created_at&fields=id,name'

FastAPI: validate every query parameter against a whitelist·python

# FastAPI — wire up all four surfaces with validation
from fastapi import FastAPI, Query, HTTPException
from typing import Annotated

app = FastAPI()

ALLOWED_SORT_FIELDS = {'created_at', 'updated_at', 'name'}
ALLOWED_FIELDS = {'id', 'name', 'email', 'role', 'created_at'}
ALLOWED_INCLUDES = {'orders', 'profile'}

@app.get('/users')
async def list_users(
    role: str | None = None,                          # filter
    status: str | None = None,                        # filter (comma-sep IN)
    created_at_gte: str | None = None,                # filter (range)
    sort: str = '-created_at',                        # sort with sign prefix
    fields: str | None = None,                        # sparse fieldset
    include: str | None = None,                       # related
    limit: int = Query(20, ge=1, le=100),
    cursor: str | None = None,
):
    # Validate sort fields
    sort_fields = [s.lstrip('-+') for s in sort.split(',')]
    invalid = set(sort_fields) - ALLOWED_SORT_FIELDS
    if invalid:
        raise HTTPException(400, detail=f'sort: invalid fields {invalid}, allowed: {ALLOWED_SORT_FIELDS}')

    # Validate fields whitelist
    if fields:
        requested = set(fields.split(','))
        if not requested.issubset(ALLOWED_FIELDS):
            raise HTTPException(400, detail=f'fields: {requested - ALLOWED_FIELDS} not allowed')

    # Validate includes
    if include:
        wanted = set(include.split(','))
        if not wanted.issubset(ALLOWED_INCLUDES):
            raise HTTPException(400, detail=f'include: {wanted - ALLOWED_INCLUDES} not supported')

    # ... then build query, fetch, apply field projection, eager-load includes
    return await fetch_users(
        filters={'role': role, 'status': status, 'created_at_gte': created_at_gte},
        sort=sort_fields,
        fields=requested if fields else ALLOWED_FIELDS,
        includes=wanted if include else set(),
        limit=limit,
        cursor=cursor,
    )

Escape hatch: POST /search when query parameters can't carry the complexity·python

# When query parameters outgrow GET — switch to POST + JSON body
from fastapi import FastAPI

app = FastAPI()

# Complex search with nested boolean logic — too much for query strings
@app.post('/users/search')
async def search_users(query: dict):
    """
    POST /users/search
    {
      "where": {
        "or": [
          {"status": "active"},
          {"and": [{"role": "admin"}, {"last_login_gte": "2026-05-01"}]}
        ]
      },
      "sort": [{"field": "created_at", "dir": "desc"}],
      "fields": ["id", "display_name", "role"],
      "include": ["orders"],
      "limit": 20
    }
    """
    # POST with a body that doesn't change state is technically un-RESTful (POST
    # should not be safe). Document the deviation; it beats 8KB URLs.
    return await complex_query(query)

External links

Exercise

Take your FastAPI users endpoint from earlier lessons. Add filtering by role and status, sorting by -created_at (default), sparse fieldsets via ?fields=, and an ?include=orders related-fetch. Validate every parameter against a whitelist; return 400 with a useful message if invalid. Then deliberately request ?sort=password and ?fields=password. Watch your validation catch both. Bonus: build a small POST /users/search endpoint that takes a JSON body with nested AND/OR logic — observe how much cleaner the JSON shape is than trying to fit the same query into query parameters.

Hint

The whitelist enforcement is the load-bearing part: ALLOWED_FIELDS = {'id', 'name', 'email', 'created_at'} means password (or any other secret column) can never leak through ?fields=. Same logic for sort: ALLOWED_SORT_FIELDS prevents attackers from sorting on an unindexed column and tanking your DB. The POST /search bonus shows why GET query parameters have practical limits — once you have nested booleans, the URL becomes unreadable.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousPagination — Cursor Beats Offset for Reasons You'll Hit Next →Error Envelope Design — Give the Client Something to Work With

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.