CIFRA Query Language (CQL)

// Equal
GET /api/contacts?filter={"status": "active"}

// Multiple conditions (AND)
GET /api/contacts?filter={"status": "active", "company_id": "uuid-123"}

{
  "amount": {"$gt": 1000}           // Greater than
  "amount": {"$gte": 1000}          // Greater than or equal
  "amount": {"$lt": 1000}           // Less than
  "amount": {"$lte": 1000}          // Less than or equal
  "amount": {"$ne": 0}              // Not equal
  "amount": {"$in": [100, 200, 300]} // In array
  "amount": {"$nin": [0, null]}     // Not in array
  "amount": {"$between": [100, 1000]} // Between
}

// AND (default)
{"status": "active", "amount": {"$gt": 1000}}

// OR
{
  "$or": [
    {"stage": "won"},
    {"stage": "lost"}
  ]
}

// NOT
{
  "$not": {
    "status": "archived"
  }
}

// Complex
{
  "$and": [
    {"status": "active"},
    {
      "$or": [
        {"amount": {"$gt": 10000}},
        {"priority": "high"}
      ]
    }
  ]
}

{
  "email": {"$regex": ".*@gmail.com"}     // Regex
  "name": {"$iregex": "john"}             // Case-insensitive regex
  "email": {"$startswith": "admin@"}      // Starts with
  "email": {"$endswith": "@company.com"}  // Ends with
  "name": {"$contains": "smith"}          // Contains
  "name": {"$icontains": "SMITH"}         // Case-insensitive contains
}

{
  "created_at": {"$date_gt": "2025-01-01"}
  "created_at": {"$date_range": ["2025-01-01", "2025-12-31"]}
  "created_at": {"$days_ago": 7}          // Last 7 days
  "created_at": {"$this_month": true}     // This month
  "created_at": {"$this_year": true}      // This year
}

// Filter by related entity
{
  "company.name": "ACME Corp",
  "company.country": "USA"
}

// Nested
{
  "deals.stage": "won",
  "deals.amount": {"$gt": 10000}
}

GET /api/contacts?search=john+smith
GET /api/contacts?search=john+smith&search_fields=first_name,last_name,email

// Single field ascending
GET /api/contacts?sort=created_at

// Descending
GET /api/contacts?sort=-created_at

// Multiple fields
GET /api/contacts?sort=stage,-amount
// Sort by stage ASC, then amount DESC

// Offset-based
GET /api/contacts?limit=25&offset=50

// Cursor-based (recommended for large datasets)
GET /api/contacts?cursor=eyJpZCI6MTIzfQ==&limit=25

// Select specific fields only
GET /api/contacts?fields=id,email,name

// Exclude fields
GET /api/contacts?exclude=password_hash,internal_notes

// Count
GET /api/deals?aggregate=count

// Sum
GET /api/deals?aggregate=sum&field=amount

// Group by
GET /api/deals?aggregate=group&group_by=stage&metrics=count,sum:amount

// Result:
{
  "lead": {"count": 45, "sum_amount": 125000},
  "won": {"count": 23, "sum_amount": 450000}
}

GET /api/deals?filter={
  "$and": [
    {"stage": {"$in": ["qualified", "proposal"]}},
    {"amount": {"$gt": 10000}},
    {"company.country": "USA"},
    {"created_at": {"$days_ago": 30}}
  ]
}&sort=-amount&limit=50&fields=id,title,amount,stage,company.name