API Filtering & Sorting

Overview

Build flexible filtering and sorting systems that handle complex queries efficiently with proper validation, security, and performance optimization.

When to Use

Building search and filter interfaces
Implementing advanced query capabilities
Creating flexible data retrieval endpoints
Optimizing query performance
Validating user input for queries
Supporting complex filtering logic

Instructions

1. Query Parameter Filtering

javascript

// Node.js filtering implementation
app.get('/api/products', async (req, res) => {
  const filters = {};
  const sortOptions = {};

  // Parse filtering parameters
  const allowedFilters = ['category', 'minPrice', 'maxPrice', 'inStock', 'rating'];
  for (const key of allowedFilters) {
    if (req.query[key]) {
      filters[key] = req.query[key];
    }
  }

  // Build MongoDB query
  const mongoQuery = {};

  if (filters.category) {
    mongoQuery.category = filters.category;
  }

  if (filters.minPrice || filters.maxPrice) {
    mongoQuery.price = {};
    if (filters.minPrice) {
      mongoQuery.price.$gte = parseFloat(filters.minPrice);
    }
    if (filters.maxPrice) {
      mongoQuery.price.$lte = parseFloat(filters.maxPrice);
    }
  }

  if (filters.inStock !== undefined) {
    mongoQuery.stock = { $gt: filters.inStock === 'true' ? 0 : -1 };
  }

  if (filters.rating) {
    mongoQuery.rating = { $gte: parseFloat(filters.rating) };
  }

  // Parse sorting
  const sortField = req.query.sort || 'createdAt';
  const sortOrder = req.query.order === 'asc' ? 1 : -1;

  const validSortFields = ['price', 'rating', 'createdAt', 'popularity'];
  if (!validSortFields.includes(sortField)) {
    return res.status(400).json({ error: 'Invalid sort field' });
  }

  const page = parseInt(req.query.page) || 1;
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const offset = (page - 1) * limit;

  try {
    const [products, total] = await Promise.all([
      Product.find(mongoQuery)
        .sort({ [sortField]: sortOrder })
        .skip(offset)
        .limit(limit),
      Product.countDocuments(mongoQuery)
    ]);

    res.json({
      data: products,
      filters: {
        applied: filters,
        available: {
          categories: await getAvailableCategories(),
          priceRange: await getPriceRange(),
          ratings: [1, 2, 3, 4, 5]
        }
      },
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit)
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

2. Advanced Filter Parser

javascript

// Parse complex filter queries
class FilterParser {
  static parse(queryString) {
    const filters = {};
    const params = new URLSearchParams(queryString);

    params.forEach((value, key) => {
      // Handle nested filters (e.g., user.email, address.city)
      if (key.includes('.')) {
        this.setNested(filters, key, value);
      } else {
        filters[key] = this.parseValue(value);
      }
    });

    return filters;
  }

  static setNested(obj, path, value) {
    const keys = path.split('.');
    let current = obj;

    for (let i = 0; i < keys.length - 1; i++) {
      const key = keys[i];
      if (!current[key]) current[key] = {};
      current = current[key];
    }

    current[keys[keys.length - 1]] = this.parseValue(value);
  }

  static parseValue(value) {
    // Handle operator syntax: gt:100, lt:200, in:a,b,c
    if (typeof value !== 'string') return value;

    const operatorMatch = value.match(/^(eq|ne|gt|gte|lt|lte|in|nin|exists|regex):(.+)$/);
    if (operatorMatch) {
      const [, operator, operandValue] = operatorMatch;

      const operators = {
        eq: { $eq: operandValue },
        ne: { $ne: operandValue },
        gt: { $gt: parseFloat(operandValue) },
        gte: { $gte: parseFloat(operandValue) },
        lt: { $lt: parseFloat(operandValue) },
        lte: { $lte: parseFloat(operandValue) },
        in: { $in: operandValue.split(',') },
        nin: { $nin: operandValue.split(',') },
        exists: { $exists: operandValue === 'true' },
        regex: { $regex: operandValue, $options: 'i' }
      };

      return operators[operator];
    }

    // Parse booleans
    if (value === 'true') return true;
    if (value === 'false') return false;

    // Parse numbers
    if (!isNaN(value)) return parseFloat(value);

    return value;
  }
}

// Usage
app.get('/api/advanced-search', async (req, res) => {
  const filters = FilterParser.parse(req.url.split('?')[1]);

  const products = await Product.find(filters);
  res.json({ data: products });
});

// Example queries:
// /api/advanced-search?price=gte:100&price=lt:500&category=electronics
// /api/advanced-search?rating=gte:4&inStock=exists:true
// /api/advanced-search?tags=in:new,featured&name=regex:laptop

3. Filter Builder Pattern

javascript

// Fluent filter builder
class QueryBuilder {
  constructor(model) {
    this.model = model;
    this.query = {};
    this.sortBy = {};
    this.pageSize = 20;
    this.pageNum = 1;
  }

  filter(field, operator, value) {
    const operators = {
      '=': '$eq',
      '!=': '$ne',
      '>': '$gt',
      '>=': '$gte',
      '<': '$lt',
      '<=': '$lte',
      'in': '$in',
      'regex': '$regex'
    };

    const mongoOp = operators[operator];
    if (!mongoOp) throw new Error(`Invalid operator: ${operator}`);

    this.query[field] = { [mongoOp]: value };
    return this;
  }

  range(field, min, max) {
    this.query[field] = { $gte: min, $lte: max };
    return this;
  }

  search(text, fields) {
    this.query.$or = fields.map(field => ({
      [field]: { $regex: text, $options: 'i' }
    }));
    return this;
  }

  sort(field, direction = 'asc') {
    this.sortBy[field] = direction === 'asc' ? 1 : -1;
    return this;
  }

  pagination(page = 1, limit = 20) {
    this.pageNum = page;
    this.pageSize = Math.min(limit, 100);
    return this;
  }

  async execute() {
    const offset = (this.pageNum - 1) * this.pageSize;

    const [data, total] = await Promise.all([
      this.model.find(this.query)
        .sort(this.sortBy)
        .skip(offset)
        .limit(this.pageSize),
      this.model.countDocuments(this.query)
    ]);

    return {
      data,
      pagination: {
        page: this.pageNum,
        limit: this.pageSize,
        total,
        totalPages: Math.ceil(total / this.pageSize)
      }
    };
  }
}

// Usage
const results = await new QueryBuilder(Product)
  .filter('category', '=', 'electronics')
  .range('price', 100, 500)
  .filter('inStock', '=', true)
  .sort('price', 'asc')
  .pagination(1, 20)
  .execute();

4. Python Filtering (SQLAlchemy)

python

from sqlalchemy import and_, or_, func
from sqlalchemy.orm import Query

class FilterSpecification:
    def __init__(self, field, operator, value):
        self.field = field
        self.operator = operator
        self.value = value

    def to_sql(self, model):
        column = getattr(model, self.field)
        operators = {
            'eq': lambda c, v: c == v,
            'ne': lambda c, v: c != v,
            'gt': lambda c, v: c > v,
            'gte': lambda c, v: c >= v,
            'lt': lambda c, v: c < v,
            'lte': lambda c, v: c <= v,
            'in': lambda c, v: c.in_(v),
            'like': lambda c, v: c.ilike(f'%{v}%'),
            'between': lambda c, v: c.between(v[0], v[1])
        }

        operation = operators.get(self.operator)
        if not operation:
            raise ValueError(f'Invalid operator: {self.operator}')

        return operation(column, self.value)

@app.route('/api/products', methods=['GET'])
def list_products():
    category = request.args.get('category')
    min_price = request.args.get('minPrice', type=float)
    max_price = request.args.get('maxPrice', type=float)
    sort_by = request.args.get('sort', 'created_at')
    sort_order = request.args.get('order', 'desc')
    page = request.args.get('page', 1, type=int)
    per_page = min(request.args.get('limit', 20, type=int), 100)

    query = Product.query

    # Apply filters
    if category:
        query = query.filter(Product.category == category)

    if min_price:
        query = query.filter(Product.price >= min_price)

    if max_price:
        query = query.filter(Product.price <= max_price)

    # Apply sorting
    sort_field = getattr(Product, sort_by, Product.created_at)
    if sort_order == 'asc':
        query = query.order_by(sort_field.asc())
    else:
        query = query.order_by(sort_field.desc())

    # Paginate
    pagination = query.paginate(page=page, per_page=per_page)

    return jsonify({
        'data': [p.to_dict() for p in pagination.items],
        'pagination': {
            'page': page,
            'per_page': per_page,
            'total': pagination.total,
            'pages': pagination.pages
        }
    }), 200

5. Elasticsearch Filtering

javascript

async function searchWithFilters(searchQuery, filters, sort, page = 1, limit = 20) {
  const from = (page - 1) * limit;

  const must = [];
  const should = [];

  // Full-text search
  if (searchQuery) {
    must.push({
      multi_match: {
        query: searchQuery,
        fields: ['name^2', 'description', 'category']
      }
    });
  }

  // Apply filters
  if (filters.category) {
    must.push({ term: { 'category.keyword': filters.category } });
  }

  if (filters.minPrice || filters.maxPrice) {
    const range = {};
    if (filters.minPrice) range.gte = filters.minPrice;
    if (filters.maxPrice) range.lte = filters.maxPrice;
    must.push({ range: { price: range } });
  }

  if (filters.tags) {
    should.push({
      terms: { 'tags.keyword': filters.tags }
    });
  }

  const response = await esClient.search({
    index: 'products',
    body: {
      from,
      size: limit,
      query: {
        bool: {
          must,
          ...(should.length && { should, minimum_should_match: 1 })
        }
      },
      sort: sort ? [sort] : ['_score', { createdAt: 'desc' }],
      aggs: {
        categories: {
          terms: { field: 'category.keyword', size: 50 }
        },
        priceRange: {
          stats: { field: 'price' }
        }
      }
    }
  });

  return {
    results: response.hits.hits.map(hit => hit._source),
    total: response.hits.total.value,
    facets: {
      categories: response.aggregations.categories.buckets,
      priceRange: response.aggregations.priceRange
    }
  };
}

6. Query Validation

javascript

// Prevent injection and invalid queries
const validateFilter = (field, value) => {
  const allowedFields = ['category', 'price', 'rating', 'inStock'];

  if (!allowedFields.includes(field)) {
    throw new Error(`Field ${field} is not filterable`);
  }

  // Validate field-specific values
  const validations = {
    category: (v) => typeof v === 'string' && v.length <= 50,
    price: (v) => !isNaN(v) && v >= 0,
    rating: (v) => !isNaN(v) && v >= 0 && v <= 5,
    inStock: (v) => v === 'true' || v === 'false'
  };

  if (!validations[field](value)) {
    throw new Error(`Invalid value for ${field}`);
  }

  return true;
};

Best Practices

✅ DO

Whitelist allowed filter fields
Validate all input parameters
Index fields used for filtering
Support common operators
Provide faceted navigation
Cache filter options
Limit filter complexity
Document filter syntax
Use database-native operators
Optimize queries with indexes

❌ DON'T

Allow arbitrary field filtering
Support unlimited operators
Ignore SQL injection risks
Create complex filter logic
Expose internal field names
Filter on unindexed fields
Allow deeply nested filters
Skip input validation
Combine all filters with OR
Ignore performance impact

Performance Optimization

Create composite indexes for common filters
Use query hints in databases
Cache frequent filter combinations
Limit aggregation complexity
Monitor query performance
Use database statistics
Consider denormalization
Implement query result caching

api-filtering-sorting

NPX Install

Tags

SKILL.md Content

API Filtering & Sorting

Overview

When to Use

Instructions

1. Query Parameter Filtering

2. Advanced Filter Parser

3. Filter Builder Pattern

4. Python Filtering (SQLAlchemy)

5. Elasticsearch Filtering

6. Query Validation

Best Practices

✅ DO

❌ DON'T

Performance Optimization