sonicjs/docs/api-filtering.md
Lane Campbell e73ef201ea feat: add comprehensive API filtering system with 14 operators
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>
2025-10-08 16:44:31 -07:00

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:

  1. Using a spatial database extension
  2. Implementing application-level filtering
  3. 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
}

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"
    }
  ]
}

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

  1. Use specific filters: The more specific your filters, the faster the query
  2. Limit results: Always use limit to prevent large result sets
  3. Index fields: Ensure frequently filtered fields are indexed in the database
  4. Cache results: The API automatically caches results, but you can also cache on your end
  5. Combine operators wisely: Use AND for narrowing results, OR for broadening
  6. 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