Advanced Query Guide

Note: Deep-dive and patterns. For parameter-by-parameter syntax, see Item Query Reference Guide.

Query Syntax Overview
Filter Operations
Advanced Filtering
Column-to-Column Comparisons
Aggregation and Grouping
Relational Queries
Array Operations
Spatial Queries (PostGIS)
Full-Text Search
Query Performance Optimization
Complete Examples

Query Syntax Overview

BAASIX uses a flexible JSON-based query syntax that supports complex filtering, sorting, pagination, aggregation, and relationships. All queries follow the same structure whether via GET parameters or POST body.

Basic Query Structure

{
  "fields": ["id", "name", "email"], // Field selection
  "filter": { /* filter conditions */ }, // Where conditions
  "sort": { "createdAt": "desc" }, // Sorting
  "limit": 20, // Pagination limit
  "page": 1, // Page number
  "search": "search term", // Full-text search
  "searchFields": ["title", "content"], // Search specific fields
  "aggregate": { /* aggregation functions */ }, // Aggregations
  "groupBy": ["category"] // Group by fields
}

URL Parameter Format

For GET requests, complex objects should be JSON-encoded:

# Simple filter
GET /items/posts?filter={"status":{"eq":"published"}}

# Complex nested filter
GET /items/posts?filter={"AND":[{"status":{"eq":"published"}},{"views":{"gt":100}}]}

# Multiple parameters
GET /items/posts?fields=id,title,author.name&sort={"createdAt":"desc"}&limit=10

Filter Operations

Basic Operators

Operator	Description	Example
`eq`	Equal	`{"status": {"eq": "published"}}`
`ne`	Not equal	`{"status": {"ne": "draft"}}`
`gt`	Greater than	`{"price": {"gt": 100}}`
`gte`	Greater than or equal	`{"price": {"gte": 100}}`
`lt`	Less than	`{"views": {"lt": 1000}}`
`lte`	Less than or equal	`{"views": {"lte": 1000}}`
`in`	In array	`{"status": {"in": ["published", "featured"]}}`
`notIn`	Not in array	`{"category": {"notIn": ["spam", "deleted"]}}`
`like`	SQL LIKE pattern (auto-adds %)	`{"title": {"like": "BAASIX"}}`
`iLike`	Case-insensitive LIKE (auto-adds %)	`{"email": {"iLike": "gmail.com"}}`
`notLike`	NOT LIKE pattern (auto-adds %)	`{"title": {"notLike": "spam"}}`
`notILike`	Case-insensitive NOT LIKE (auto-adds %)	`{"email": {"notILike": "temp"}}`
`startsWith`	Starts with (case-insensitive)	`{"name": {"startsWith": "John"}}`
`endsWith`	Ends with (case-insensitive)	`{"email": {"endsWith": ".edu"}}`
`not`	Negation	`{"active": {"not": true}}`
`is`	Identity check	`{"value": {"is": null}}`
`between`	Between range	`{"age": {"between": [18, 65]}}`
`notBetween`	Not between range	`{"score": {"notBetween": [0, 50]}}`
`isNull`	Is null	`{"deletedAt": {"isNull": true}}`
`isNotNull`	Is not null	`{"publishedAt": {"isNotNull": true}}`

Date and Time Operators

// Date comparisons
{
  "createdAt": {
    "gt": "2025-01-01T00:00:00Z",
    "lt": "2025-12-31T23:59:59Z"
  }
}

// Type casting to extract date/time parts
{
  "createdAt": {"eq": "2025-01-15", "cast": "date"},
  "workStartTime": {"between": ["08:00:00", "18:00:00"], "cast": "time"}
}

// Relative dates using dynamic variables
{
  "updatedAt": {"gt": "$NOW-DAYS_7"},
  "publishedAt": {"lte": "$NOW"},
  "scheduledAt": {"between": ["$NOW+HOURS_1", "$NOW+DAYS_30"]}
}

Advanced Filtering

Logical Operators (AND, OR)

// AND condition (implicit)
{
  "status": {"eq": "published"},
  "views": {"gt": 100}
}

// Explicit AND
{
  "AND": [
    {"status": {"eq": "published"}},
    {"views": {"gt": 100}}
  ]
}

// OR condition
{
  "OR": [
    {"status": {"eq": "featured"}},
    {"views": {"gt": 1000}}
  ]
}

// Complex nested logic
{
  "AND": [
    {
      "OR": [
        {"status": {"eq": "published"}},
        {"status": {"eq": "featured"}}
      ]
    },
    {
      "AND": [
        {"views": {"gt": 100}},
        {"createdAt": {"gt": "2025-01-01"}}
      ]
    }
  ]
}

Conditional Filters with Dynamic Variables

// User-based filtering (uses current user context)
{
  "author_id": {"eq": "$CURRENT_USER"}
}

// Tenant-based filtering (multi-tenant)
{
  "tenant_Id": {"eq": "$CURRENT_TENANT"}
}

// Role-based filtering
{
  "visibility": {"eq": "$USER_ROLE"}
}

// Current date/time
{
  "publishedAt": {"lte": "$NOW"}
}

Column-to-Column Comparisons

Compare field values against other field values in the same record using $COL() syntax:

// Basic column comparison
{
  "actualCost": {"gt": "$COL(estimatedCost)"}
}

// With type casting for precise comparisons
{
  "startTime": {
    "gt": "$COL(endTime)",
    "cast": "time"  // Apply time casting to both sides
  }
}

// PostgreSQL casting syntax in column reference
{
  "workStartTime": {
    "gt": "$COL(workEndTime::time)",
    "cast": "time"
  }
}

// Relational column comparisons
{
  "salary": {"gt": "$COL(department.minSalary)"}
}

// Complex business rules validation
{
  "AND": [
    {"endDate": {"gte": "$COL(startDate)"}},  // End after start
    {"actualHours": {"lte": "$COL(maxHours)"}},  // Within limits
    {"budget": {"gte": "$COL(estimatedCost)"}}   // Budget sufficient
  ]
}

Aggregation and Grouping

Available Aggregate Functions

Function	Description	Example
`count`	Count records	`{"totalPosts": {"function": "count", "field": "id"}}`
`sum`	Sum values	`{"totalViews": {"function": "sum", "field": "views"}}`
`avg`	Average value	`{"avgRating": {"function": "avg", "field": "rating"}}`
`min`	Minimum value	`{"minPrice": {"function": "min", "field": "price"}}`
`max`	Maximum value	`{"maxPrice": {"function": "max", "field": "price"}}`
`distinct`	Count distinct	`{"uniqueAuthors": {"function": "distinct", "field": "author_id"}}`
`array_agg`	Array aggregation	`{"allTags": {"function": "array_agg", "field": "tags"}}`

Group By Operations

// Simple grouping
{
  "groupBy": ["category"],
  "aggregate": {
    "postCount": {"function": "count", "field": "id"},
    "avgViews": {"function": "avg", "field": "views"}
  }
}

// Multiple group fields
{
  "groupBy": ["category", "status"],
  "aggregate": {
    "totalPosts": {"function": "count", "field": "id"}
  }
}

// Date-based grouping
{
  "groupBy": ["date:year:createdAt"],
  "aggregate": {
    "yearlyPosts": {"function": "count", "field": "id"}
  }
}

// Date extraction options
{
  "groupBy": [
    "date:year:createdAt",      // Extract year
    "date:month:createdAt",     // Extract month
    "date:day:createdAt",       // Extract day
    "date:hour:createdAt",      // Extract hour
    "date:dow:createdAt"        // Day of week
  ]
}

Relational Queries

Basic Relationship Queries

// Include related data
{
  "fields": [
    "id",
    "title",
    "author.name",
    "author.email",
    "comments.content",
    "comments.user.name"
  ]
}

// Filter by related fields
{
  "filter": {
    "author.status": {"eq": "active"},
    "comments.approved": {"eq": true}
  }
}

Complex Relational Filtering

// HasMany relationship filtering
{
  "filter": {
    "comments": {
      "approved": {"eq": true},
      "createdAt": {"gt": "2025-01-01"}
    }
  }
}

// BelongsToMany (Many-to-Many) filtering
{
  "filter": {
    "tags.name": {"in": ["javascript", "nodejs"]}
  }
}

// Polymorphic relationship filtering (M2A)
{
  "filter": {
    "attachments": {
      "related_collection": {"eq": "posts"},
      "file.type": {"eq": "image"}
    }
  }
}

// Count-based filtering
{
  "filter": {
    "comments.$count": {"gt": 5}
  }
}

// Existence filtering
{
  "filter": {
    "comments.$exists": {"eq": true}  // Has any comments
  }
}

Nested Relationship Queries

// Deep nesting (3+ levels)
{
  "fields": [
    "id",
    "title",
    "author.profile.company.name",
    "comments.user.profile.avatar",
    "category.parent.name"
  ]
}

// Complex nested filtering
{
  "filter": {
    "AND": [
      {"author.profile.verified": {"eq": true}},
      {"comments.user.status": {"eq": "active"}},
      {"category.parent.status": {"eq": "active"}}
    ]
  }
}

Array Operations

Array Field Filtering

// Array contains value (PostgreSQL @> operator)
{
  "tags": {"arraycontains": ["javascript"]}
}

// Array is contained by value (PostgreSQL <@ operator)
{
  "tags": {"arraycontained": ["javascript", "nodejs", "react", "vue"]}
}

// Note: Array length and element access operations are not supported
// through standard filter operators. Use raw SQL in $ field qualifiers if needed.

JSON Field Operations

// JSON field access
{
  "metadata->language": "en",
  "settings->>theme": "dark"
}

// JSON path queries
{
  "metadata": {"jsonPath": "$.user.preferences.language", "eq": "en"}
}

// JSON contains
{
  "metadata": {"jsonContains": {"feature": "enabled"}}
}

Spatial Queries (PostGIS)

Geometric Operations

// Point within distance (PostGIS ST_DWithin)
{
  "location": {
    "dwithin": {
      "geometry": {
        "type": "Point",
        "coordinates": [lng, lat]
      },
      "distance": 1000  // meters
    }
  }
}

// Point within polygon (PostGIS ST_Within)
{
  "location": {
    "within": {
      "type": "Polygon",
      "coordinates": [[[lng1,lat1], [lng2,lat2], [lng3,lat3], [lng1,lat1]]]
    }
  }
}

// Geometry contains another (PostGIS ST_Contains)
{
  "polygon_field": {
    "containsGEO": {
      "type": "Point",
      "coordinates": [lng, lat]
    }
  }
}

// Geometry intersects (PostGIS ST_Intersects)
{
  "location": {
    "intersects": {
      "type": "Polygon",
      "coordinates": [[[lng1,lat1], [lng2,lat2], [lng3,lat3], [lng1,lat1]]]
    }
  }
}

// Sort by distance from a point
{
  "fields": ["id", "name", "location"],
  "sort": {
    "_distance": {
      "target": [lng, lat],
      "column": "location",
      "direction": "ASC"
    }
  }
}

Full-Text Search

Basic Search

{
  "search": "BAASIX tutorial",
  "searchFields": ["title", "content", "tags"]
}

Advanced Search Options

{
  "search": {
    "term": "javascript tutorial",
    "fields": ["title", "content"],
    "operator": "and",  // "and" or "or"
    "fuzziness": 1,     // Edit distance for fuzzy matching
    "boost": {
      "title": 2,       // Boost title matches
      "content": 1
    }
  }
}

Search with Highlighting

{
  "search": "javascript",
  "searchFields": ["title", "content"],
  "highlight": {
    "fields": ["title", "content"],
    "pre_tags": ["<mark>"],
    "post_tags": ["</mark>"]
  }
}

Query Performance Optimization

Efficient Queries

// ✅ Good: Use specific fields
{
  "fields": ["id", "title", "createdAt"],
  "limit": 20
}

// ❌ Bad: Select all fields
{
  "fields": ["*"],
  "limit": 1000
}

// ✅ Good: Use indexes for filtering
{
  "filter": {
    "status": "published",  // Indexed field
    "createdAt": {"gt": "2025-01-01"}  // Indexed field
  }
}

// ❌ Bad: Function calls in filters
{
  "filter": {
    "LOWER(title)": {"like": "%search%"}  // Not indexed
  }
}

Pagination Best Practices

// ✅ Good: Cursor-based pagination for large datasets
{
  "filter": {
    "id": {"gt": "last_id_from_previous_page"}
  },
  "sort": {"id": "asc"},
  "limit": 20
}

// ✅ Good: Offset pagination for small datasets
{
  "page": 1,
  "limit": 20
}

Relationship Loading Strategies

// ✅ Efficient: Load only needed related fields
{
  "fields": [
    "id", "title",
    "author.name",  // Only author name
    "category.name" // Only category name
  ]
}

// ❌ Inefficient: Load all related data
{
  "fields": [
    "*",
    "author.*",
    "comments.*"
  ]
}

Complete Examples

E-commerce Product Catalog

// Complex product search with faceting
POST /items/products
{
  "fields": [
    "id", "name", "price", "image",
    "category.name", "brand.name",
    "reviews.rating", "reviews.count"
  ],
  "filter": {
    "AND": [
      {"status": {"eq": "active"}},
      {"price": {"gte": 10, "lte": 1000}},
      {
        "OR": [
          {"category.slug": {"in": ["electronics", "computers"]}},
          {"tags": {"arraycontains": ["gadget"]}}
        ]
      },
      {"inventory.stock": {"gt": 0}}
    ]
  },
  "search": {
    "term": "wireless headphones",
    "fields": ["name", "description", "tags"],
    "operator": "and"
  },
  "sort": {
    "featured": "desc",
    "reviews.avgRating": "desc",
    "price": "asc"
  },
  "aggregate": {
    "priceMin": {"function": "min", "field": "price"},
    "priceMax": {"function": "max", "field": "price"},
    "totalProducts": {"function": "count", "field": "id"}
  },
  "groupBy": ["category.name"],
  "limit": 24,
  "page": 1
}

Blog Analytics Dashboard

// Monthly blog statistics with author performance
POST /items/posts
{
  "filter": {
    "createdAt": {
      "gte": "2025-01-01",
      "lt": "2025-12-31"
    },
    "status": {"eq": "published"}
  },
  "groupBy": [
    "date:month:createdAt",
    "author.id"
  ],
  "aggregate": {
    "monthlyPosts": {"function": "count", "field": "id"},
    "totalViews": {"function": "sum", "field": "views"},
    "avgViews": {"function": "avg", "field": "views"},
    "totalComments": {"function": "sum", "field": "comments_count"},
    "uniqueCategories": {"function": "distinct", "field": "category_id"}
  },
  "fields": [
    "date:month:createdAt as month",
    "author.name",
    "author.email"
  ],
  "sort": {
    "month": "desc",
    "totalViews": "desc"
  }
}

User Activity Report

// User engagement with geographical distribution
POST /items/users
{
  "filter": {
    "lastLoginAt": {"gte": "$NOW-DAYS_30"},
    "status": {"eq": "active"}
  },
  "fields": [
    "id", "email", "name",
    "profile.country", "profile.city",
    "posts.title", "posts.views",
    "comments.content", "comments.createdAt"
  ],
  "aggregate": {
    "activeUsers": {"function": "count", "field": "id"},
    "totalPosts": {"function": "count", "field": "posts.id"},
    "totalComments": {"function": "count", "field": "comments.id"},
    "avgPostsPerUser": {"function": "avg", "field": "posts_count"}
  },
  "groupBy": ["profile.country"],
  "sort": {
    "activeUsers": "desc"
  },
  "limit": 50
}

Multi-Tenant Sales Report

// Tenant-specific sales data with temporal analysis
POST /items/orders
{
  "filter": {
    "tenant_Id": {"eq": "$CURRENT_TENANT"},
    "status": {"eq": "completed"},
    "createdAt": {
      "gte": "2025-01-01",
      "lt": "2025-02-01"
    }
  },
  "fields": [
    "id", "total", "createdAt",
    "customer.name", "customer.email",
    "items.product.name", "items.quantity", "items.price"
  ],
  "aggregate": {
    "totalOrders": {"function": "count", "field": "id"},
    "totalRevenue": {"function": "sum", "field": "total"},
    "avgOrderValue": {"function": "avg", "field": "total"},
    "uniqueCustomers": {"function": "distinct", "field": "customer_id"}
  },
  "groupBy": [
    "date:day:createdAt",
    "items.product.category_id"
  ],
  "sort": {
    "createdAt": "desc"
  }
}

Geospatial Location Search

// Find nearby stores with inventory
POST /items/stores
{
  "filter": {
    "AND": [
      {"status": {"eq": "active"}},
      {
        "location": {
          "dwithin": {
            "geometry": {
              "type": "Point",
              "coordinates": [-74.0060, 40.7128] // NYC coordinates
            },
            "distance": 5000 // 5km radius
          }
        }
      },
      {
        "inventory.products": {
          "product_id": {"eq": "product-uuid-here"},
          "stock": {"gt": 0}
        }
      }
    ]
  },
  "fields": [
    "id", "name", "address",
    "phone", "hours", "location"
  ],
  "sort": {
    "_distance": {
      "target": [-74.0060, 40.7128],
      "column": "location",
      "direction": "ASC"
    }
  },
  "limit": 10
}

Error Handling in Queries

Common Query Errors

// ❌ Invalid field reference
{
  "filter": {"nonexistent_field": "value"}
}
// Error: Field 'nonexistent_field' does not exist

// ❌ Invalid operator
{
  "filter": {"price": {"invalid_op": 100}}
}
// Error: Unknown operator 'invalid_op'

// ❌ Type mismatch
{
  "filter": {"price": "not_a_number"}}
}
// Error: Invalid value type for numeric field

// ❌ Invalid relationship
{
  "fields": ["nonexistent.field"]
}
// Error: Relationship 'nonexistent' not found

Query Validation

All queries are validated before execution:

Field validation: Ensures all referenced fields exist
Type validation: Checks value types match field definitions
Permission validation: Verifies user has access to requested fields
Relationship validation: Confirms relationship paths are valid
Syntax validation: Validates query structure and operators

Item Query Reference Guide - Basic query syntax
Schema Reference Guide - Field types and relationships
Item Routes - API endpoints for querying data

← Back to Documentation Home

On this page