API Filtering: retrieving data like a coffee connoisseur
Table of Contents
In my first year of working, I once wrote an endpoint GET /api/menus that returned… the entire menu. 200 items every time it was called. The JSON was 1.2MB heavy. The frontend only needed the name and price of 10 active dishes. I remember the first thing my lead said: “You’re sending the entire warehouse to someone who just needs to view the menu, aren’t you?”
That lesson took a week to refactor. Since then, every time I design an API, I think of a coffee shop: when a customer orders an iced Americano, the barista doesn’t bring out the entire counter - they take the order clearly, make the right drink, and serve the right cup. APIs should work the same way.
Here are 3 patterns I use to make APIs “order” instead of “bring the entire counter.” All the code is in Go, runs smoothly, and can be copy-pasted into your project for immediate use.
1. Only show items that are on sale - filter query params
The most basic filter: the client only wants active items.
GET /api/products?status=active
Simple Go backend parsing:
func parseFilters(q url.Values) []Filter {
var filters []Filter
for key, values := range q {
switch {
case strings.HasSuffix(key, "_gte"):
field := strings.TrimSuffix(key, "_gte")
filters = append(filters, Filter{field, ">=", parseFloat(values[0])})
case strings.HasSuffix(key, "_lte"):
field := strings.TrimSuffix(key, "_lte")
filters = append(filters, Filter{field, "<=", parseFloat(values[0])})
case strings.HasSuffix(key, "_in"):
field := strings.TrimSuffix(key, "_in")
filters = append(filters, Filter{field, "IN", strings.Split(values[0], ",")})
case strings.HasSuffix(key, "_like"):
field := strings.TrimSuffix(key, "_like")
filters = append(filters, Filter{field, "LIKE", "%" + values[0] + "%"})
default:
// exact match
filters = append(filters, Filter{key, "=", values[0]})
}
}
return filters
}
Pitfall I’ve encountered: using _like on a column without a full-text index. 100K records × LIKE ‘%keyword%’ = sequential scan. If you need fuzzy search, push it to Elasticsearch or at least use PostgreSQL pg_trgm.
Some operators that should be supported:
| Operator | Example | When to use |
|---|---|---|
_eq (or default) | status=active | Exact filtering |
_ne | category_ne=archived | Exclusion |
_gte / _lte | price_gte=50000 | Price range |
_in | status_in=active,pending | Multiple values |
_like | name_like=coffee | Text search (be careful with indexing!) |
2. Sorting New Items to the Top
GET /api/posts?sort=created_at&order=desc
Go Code:
func applySort(db *gorm.DB, q url.Values) *gorm.DB {
sortField := q.Get("sort")
order := q.Get("order")
if sortField == "" {
return db.Order("created_at DESC") // default
}
// Whitelist fields - don't let the client sort arbitrary columns!
allowed := map[string]bool{
"created_at": true, "price": true, "name": true, "updated_at": true,
}
if !allowed[sortField] {
return db // ignore invalid field
}
if order == "asc" {
return db.Order(sortField + " ASC")
}
return db.Order(sortField + " DESC")
}
I once made a mistake: allowing ?sort=anything without whitelisting. If the client enters ?sort=1;DROP TABLE products;--, it won’t cause any issues because GORM escapes the input, but ?sort=json_data will result in a 300ms query because the column is not indexed. Whitelisting and indexing are mandatory, not optional.
-- Index common sort columns to avoid full scans
CREATE INDEX idx_products_created_at ON products(created_at DESC);
CREATE INDEX idx_products_price ON products(price ASC);
3. I only need the name and price - field selection
GET /api/products?fields=name,price,thumbnail
This is the most overlooked optimization. A JSON response with 20 fields and nested objects is much heavier than 3 flat fields.
func applyFieldSelection(db *gorm.DB, q url.Values) *gorm.DB {
fields := q.Get("fields")
if fields == "" {
return db // return all columns
}
selected := strings.Split(fields, ",")
allowed := map[string]bool{
"id": true, "name": true, "price": true,
"thumbnail": true, "status": true, "created_at": true,
}
var columns []string
for _, f := range selected {
if allowed[strings.TrimSpace(f)] {
columns = append(columns, strings.TrimSpace(f))
}
}
if len(columns) == 0 {
return db // fallback to all if invalid
}
return db.Select(columns)
}
In my food delivery project, I reduced the payload size by 40% just by adding field selection for the mobile client. On 3G, 40% is significant.
Important Order: Filter → Sort → Paginate
When combining all three:
GET /api/products?category=tea&price_gte=30000&sort=price&order=asc&page=1&limit=20
Backend flow:
func ListProducts(ctx context.Context, q url.Values) ([Product], int64, error) {
db := db.WithContext(ctx).Model(&Product{})
db = applyFilters(db, q) // 1. Filter
db = applySort(db, q) // 2. Sort
// 3. Paginate (AFTER filter+sort)
var total int64
db.Count(&total)
var products [Product]
offset, limit := parsePagination(q)
db.Offset(offset).Limit(limit).Find(&products)
return products, total, nil
}
Don’t paginate before filtering - you’ll be missing data and the total count will be incorrect. I’ve seen this error in 2/3 of junior codebases during review.
Bonus: Test your filter immediately
func TestListProducts_FilterByStatus(t *testing.T) {
// seed 3 active + 2 inactive
seedProducts(t, 3, "active")
seedProducts(t, 2, "inactive")
req, _ := http.NewRequest("GET", "/api/products?status=active", nil)
products, _, err := handler.ListProducts(req.Context(), req.URL.Query())
require.NoError(t, err)
assert.Len(t, products, 3)
}
Bottom line
A good API is not the one that returns the most data - but the one that provides the client with enough and correct data. Filtering, sorting, and field selection take 30 minutes to implement but save hours of debugging, reduce payload, and decrease database load. Everyone knows this, but few people do all three.
Have you ever encountered an endpoint that returns a 1MB JSON while the frontend only needs 3 fields? Or are you still maintaining one? Share your story with me - lessons from “fat APIs” are always the most valuable. ☕
