Implement powerful, flexible filtering for the Content API with full support for complex queries, AND/OR logic, sorting, pagination, and JSON field access. ## Features Added ### Query Filter Builder (src/utils/query-filter.ts) - 14 filter operators: equals, not_equals, greater_than, greater_than_equal, less_than, less_than_equal, like, contains, in, not_in, all, exists - Spatial operators documented (near, within, intersects) but not implemented (requires extensions) - AND/OR boolean logic for complex queries - Multi-field sorting with ASC/DESC - Pagination with LIMIT/OFFSET - JSON field queries using dot notation (e.g., data.author) - SQL injection protection via parameterized queries - Field name sanitization - NULL value handling - Type-safe TypeScript implementation ### Enhanced API Endpoints (src/routes/api.ts) - Updated /api/content to accept filter parameters - Updated /api/collections/:collection/content with filtering - Filter parameters: where (JSON), sort (JSON), limit, offset - Response includes filter metadata and generated SQL for transparency - Automatic caching of filtered results - Backward compatible - existing calls work unchanged ### Comprehensive Testing (src/tests/query-filter.test.ts) - 29 test cases covering all operators - AND/OR logic testing - NULL handling tests - Sorting and pagination tests - JSON field access tests - Query string parsing tests - Error handling tests - Real-world scenario tests - All 712 tests passing ✅ ### Complete Documentation - API Filtering Guide (docs/api-filtering.md): Complete reference with examples - JavaScript Examples (docs/api-filtering-examples.js): 12 ready-to-use functions - System README (src/utils/README-query-filter.md): Technical documentation - Summary Document (FILTERING-SUMMARY.md): Overview and implementation details ## Usage Examples Simple filter: GET /api/content?where={"and":[{"field":"status","operator":"equals","value":"published"}]} Complex filter with sorting: { "where": { "and": [ {"field":"status","operator":"equals","value":"published"}, {"field":"views","operator":"greater_than","value":100} ], "or": [ {"field":"featured","operator":"equals","value":true} ] }, "sort": [{"field":"views","order":"desc"}], "limit": 10 } ## Security - Parameterized queries prevent SQL injection - Field name sanitization - Type validation for all operators - Maximum result limit enforcement (1000) ## Performance - Efficient query building - Automatic result caching - Optimized IN/NOT IN handling - Database query plan caching ## Breaking Changes None - Fully backward compatible 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <noreply@anthropic.com>
13 KiB
API Filtering Guide
SonicJS AI provides a powerful and flexible filtering system for querying content through the API. This guide covers all available filter operators and provides comprehensive examples.
Table of Contents
Basic Usage
Filters are passed as query parameters to the API endpoints. The main filter parameter is where, which accepts a JSON-encoded filter object.
Simple Example
GET /api/content?where={"and":[{"field":"status","operator":"equals","value":"published"}]}
With URL Encoding
GET /api/content?where=%7B%22and%22%3A%5B%7B%22field%22%3A%22status%22%2C%22operator%22%3A%22equals%22%2C%22value%22%3A%22published%22%7D%5D%7D
Filter Operators
equals
The value must be exactly equal.
{
"where": {
"and": [
{
"field": "status",
"operator": "equals",
"value": "published"
}
]
}
}
NULL handling:
{
"where": {
"and": [
{
"field": "deleted_at",
"operator": "equals",
"value": null
}
]
}
}
not_equals
The query will return all documents where the value is not equal.
{
"where": {
"and": [
{
"field": "status",
"operator": "not_equals",
"value": "draft"
}
]
}
}
greater_than
For numeric or date-based fields.
{
"where": {
"and": [
{
"field": "views",
"operator": "greater_than",
"value": 1000
}
]
}
}
Date example:
{
"where": {
"and": [
{
"field": "created_at",
"operator": "greater_than",
"value": 1640995200000
}
]
}
}
greater_than_equal
For numeric or date-based fields.
{
"where": {
"and": [
{
"field": "price",
"operator": "greater_than_equal",
"value": 99.99
}
]
}
}
less_than
For numeric or date-based fields.
{
"where": {
"and": [
{
"field": "stock",
"operator": "less_than",
"value": 10
}
]
}
}
less_than_equal
For numeric or date-based fields.
{
"where": {
"and": [
{
"field": "priority",
"operator": "less_than_equal",
"value": 5
}
]
}
}
like
Case-insensitive string search. If the value contains multiple words, all words must be present in any order.
{
"where": {
"and": [
{
"field": "title",
"operator": "like",
"value": "getting started tutorial"
}
]
}
}
This will match:
- "Getting Started with Vue Tutorial"
- "Tutorial: Getting Started"
- "Started Getting Tutorial"
contains
Must contain the value entered, case-insensitive. Simpler than like - just checks if the string is present.
{
"where": {
"and": [
{
"field": "description",
"operator": "contains",
"value": "javascript"
}
]
}
}
in
The value must be found within the provided comma-delimited list of values.
Array format:
{
"where": {
"and": [
{
"field": "status",
"operator": "in",
"value": ["published", "review", "scheduled"]
}
]
}
}
Comma-delimited string:
{
"where": {
"and": [
{
"field": "category",
"operator": "in",
"value": "tech,science,education"
}
]
}
}
not_in
The value must NOT be within the provided comma-delimited list of values.
{
"where": {
"and": [
{
"field": "status",
"operator": "not_in",
"value": ["deleted", "archived", "spam"]
}
]
}
}
all
The value must contain all values provided in the comma-delimited list. Useful for tags or multi-value fields.
{
"where": {
"and": [
{
"field": "tags",
"operator": "all",
"value": "javascript,typescript,react"
}
]
}
}
This will only match documents that have ALL three tags.
exists
Only return documents where the value either exists (true) or does not exist (false).
Field exists:
{
"where": {
"and": [
{
"field": "thumbnail",
"operator": "exists",
"value": true
}
]
}
}
Field does not exist:
{
"where": {
"and": [
{
"field": "thumbnail",
"operator": "exists",
"value": false
}
]
}
}
near, within, intersects
Note: These spatial/geospatial operators are not currently supported in the SQLite/D1 implementation. For spatial queries, consider:
- Using a spatial database extension
- Implementing application-level filtering
- Using a dedicated geospatial service
AND/OR Logic
Filters support both AND and OR logic, which can be combined for complex queries.
AND Logic
All conditions must be true.
{
"where": {
"and": [
{
"field": "status",
"operator": "equals",
"value": "published"
},
{
"field": "views",
"operator": "greater_than",
"value": 100
},
{
"field": "category",
"operator": "equals",
"value": "tech"
}
]
}
}
OR Logic
At least one condition must be true.
{
"where": {
"or": [
{
"field": "status",
"operator": "equals",
"value": "published"
},
{
"field": "status",
"operator": "equals",
"value": "review"
}
]
}
}
Combined AND/OR
{
"where": {
"and": [
{
"field": "category",
"operator": "equals",
"value": "tech"
},
{
"field": "views",
"operator": "greater_than",
"value": 50
}
],
"or": [
{
"field": "featured",
"operator": "equals",
"value": true
},
{
"field": "promoted",
"operator": "equals",
"value": true
}
]
}
}
This translates to: (category = 'tech' AND views > 50) AND (featured = true OR promoted = true)
Sorting
Sort results by one or more fields.
Single Field
{
"sort": [
{
"field": "created_at",
"order": "desc"
}
]
}
URL:
GET /api/content?sort=[{"field":"created_at","order":"desc"}]
Multiple Fields
{
"sort": [
{
"field": "priority",
"order": "desc"
},
{
"field": "created_at",
"order": "asc"
}
]
}
Pagination
Control the number of results and pagination.
Limit
GET /api/content?limit=25
Maximum limit: 1000 results
Offset
GET /api/content?limit=25&offset=50
This returns results 51-75.
Combined with Filters
GET /api/content?where={"and":[{"field":"status","operator":"equals","value":"published"}]}&limit=10&offset=20
JSON Field Queries
Query nested fields within JSON data using dot notation.
Basic JSON Field
{
"where": {
"and": [
{
"field": "data.author",
"operator": "equals",
"value": "John Doe"
}
]
}
}
Nested JSON Field
{
"where": {
"and": [
{
"field": "data.meta.seoTitle",
"operator": "contains",
"value": "Tutorial"
}
]
}
}
Complete Examples
Example 1: Blog Posts
Get all published tech blog posts with more than 100 views, sorted by views:
curl -X GET "https://your-api.com/api/content?where=%7B%22and%22%3A%5B%7B%22field%22%3A%22status%22%2C%22operator%22%3A%22equals%22%2C%22value%22%3A%22published%22%7D%2C%7B%22field%22%3A%22category%22%2C%22operator%22%3A%22equals%22%2C%22value%22%3A%22tech%22%7D%2C%7B%22field%22%3A%22views%22%2C%22operator%22%3A%22greater_than%22%2C%22value%22%3A100%7D%5D%7D&sort=%5B%7B%22field%22%3A%22views%22%2C%22order%22%3A%22desc%22%7D%5D&limit=10"
Decoded filter:
{
"where": {
"and": [
{
"field": "status",
"operator": "equals",
"value": "published"
},
{
"field": "category",
"operator": "equals",
"value": "tech"
},
{
"field": "views",
"operator": "greater_than",
"value": 100
}
]
},
"sort": [
{
"field": "views",
"order": "desc"
}
],
"limit": 10
}
Example 2: Product Search
Find products in specific categories, within a price range, with available stock:
{
"where": {
"and": [
{
"field": "category",
"operator": "in",
"value": "electronics,computers,gaming"
},
{
"field": "price",
"operator": "greater_than_equal",
"value": 50
},
{
"field": "price",
"operator": "less_than_equal",
"value": 500
},
{
"field": "stock",
"operator": "greater_than",
"value": 0
}
]
},
"sort": [
{
"field": "price",
"order": "asc"
}
],
"limit": 50
}
Example 3: Recent Activity
Get recently created or updated content:
{
"where": {
"or": [
{
"field": "created_at",
"operator": "greater_than",
"value": 1704067200000
},
{
"field": "updated_at",
"operator": "greater_than",
"value": 1704067200000
}
]
},
"sort": [
{
"field": "updated_at",
"order": "desc"
}
],
"limit": 20
}
Example 4: Tagged Content
Find content with specific tags:
{
"where": {
"and": [
{
"field": "status",
"operator": "equals",
"value": "published"
},
{
"field": "tags",
"operator": "all",
"value": "javascript,tutorial,beginner"
}
]
},
"sort": [
{
"field": "created_at",
"order": "desc"
}
]
}
Example 5: Complex Search
Multi-criteria search with fallback:
{
"where": {
"and": [
{
"field": "status",
"operator": "in",
"value": ["published", "review"]
}
],
"or": [
{
"field": "title",
"operator": "like",
"value": "javascript react tutorial"
},
{
"field": "description",
"operator": "contains",
"value": "react"
},
{
"field": "tags",
"operator": "contains",
"value": "react"
}
]
},
"sort": [
{
"field": "views",
"order": "desc"
},
{
"field": "created_at",
"order": "desc"
}
],
"limit": 25
}
Response Format
The API returns filtered results with metadata:
{
"data": [
{
"id": "abc123",
"title": "Getting Started with React",
"slug": "getting-started-react",
"status": "published",
"collectionId": "blog",
"data": {},
"created_at": 1704067200000,
"updated_at": 1704153600000
}
],
"meta": {
"count": 1,
"timestamp": "2025-01-08T00:00:00.000Z",
"filter": {
"where": {},
"limit": 50
},
"query": {
"sql": "SELECT * FROM content WHERE status = ? LIMIT ?",
"params": ["published", 50]
},
"cache": {
"hit": false,
"source": "database"
},
"timing": {
"total": 45,
"execution": 12,
"unit": "ms"
}
}
}
Best Practices
- Use specific filters: The more specific your filters, the faster the query
- Limit results: Always use
limitto prevent large result sets - Index fields: Ensure frequently filtered fields are indexed in the database
- Cache results: The API automatically caches results, but you can also cache on your end
- Combine operators wisely: Use AND for narrowing results, OR for broadening
- Test queries: Use the API console to test complex queries before implementing
Error Handling
If a filter contains errors, the API will return a 400 error:
{
"error": "Invalid filter parameters",
"details": [
"'near' operator not supported in SQLite. Use spatial extension or application-level filtering."
]
}
Performance Tips
- Use indexed fields: Filter on fields that have database indexes
- Avoid LIKE on large text fields: Use full-text search instead
- Limit result sets: Always specify a reasonable limit
- Use pagination: Don't fetch all results at once
- Cache when possible: Repeated queries with the same filters are cached automatically