Querying and Filtering Data

Learn how to retrieve, filter, sort, and paginate your data using powerful query capabilities.

Basic Querying

Get All Records

Retrieve all records from a table:

GET /api/apps/{app-slug}/datatables/{table-name}/data/

Example:

GET /api/apps/blog-app/datatables/users/data/

Response:

{
  "data": [
    {
      "id": 1,
      "username": "john_doe",
      "email": "[email protected]",
      "created_at": "2024-01-15T10:30:00Z"
    },
    {
      "id": 2,
      "username": "jane_smith",
      "email": "[email protected]",
      "created_at": "2024-01-16T14:20:00Z"
    }
  ],
  "total": 2
}

Get Single Record

Retrieve a specific record by ID:

GET /api/apps/{app-slug}/datatables/{table-name}/data/{id}/

Example:

GET /api/apps/blog-app/datatables/users/data/1/

Response:

{
  "data": {
    "id": 1,
    "username": "john_doe",
    "email": "[email protected]",
    "bio": "Software developer",
    "created_at": "2024-01-15T10:30:00Z"
  }
}

Filtering

Filter records using query parameters with powerful operators.

Exact Match

GET /api/apps/blog-app/datatables/posts/data/?status=published

Comparison Operators

Operator	Description	Example
`_gt`	Greater than	`?views_gt=100`
`_gte`	Greater than or equal	`?score_gte=80`
`_lt`	Less than	`?age_lt=30`
`_lte`	Less than or equal	`?price_lte=50`
`_ne`	Not equal	`?status_ne=draft`

Examples:

# Posts with more than 100 views
GET /api/apps/blog-app/datatables/posts/data/?views_gt=100

# Products under $50
GET /api/apps/shop-app/datatables/products/data/?price_lte=50

# Users not from USA
GET /api/apps/crm-app/datatables/users/data/?country_ne=USA

IN Operators

Operator	Description	Case Sensitivity
`_in`	Value in list	Case-sensitive
`_nin`	Value not in list	Case-sensitive
`_ina`	Value in list	Case-insensitive
`_nina`	Value not in list	Case-insensitive

Examples:

# Posts with specific statuses
GET /api/apps/blog-app/datatables/posts/data/?status_in=published,featured

# Exclude certain categories
GET /api/apps/blog-app/datatables/posts/data/?category_nin=spam,archived

# Case-insensitive search
GET /api/apps/blog-app/datatables/posts/data/?tag_ina=javascript,PYTHON,Ruby

String Matching

Contains

Operator	Description	Case Sensitivity
`_contains`	Contains substring	Case-insensitive
`_ncontains`	Does not contain	Case-insensitive
`_containss`	Contains substring	Case-sensitive
`_ncontainss`	Does not contain	Case-sensitive

Examples:

# Titles containing "django"
GET /api/apps/blog-app/datatables/posts/data/?title_contains=django

# Emails not containing "spam"
GET /api/apps/crm-app/datatables/users/data/?email_ncontains=spam

# Case-sensitive search
GET /api/apps/docs-app/datatables/articles/data/?content_containss=API

Starts With

Operator	Description	Case Sensitivity
`_startswith`	Starts with	Case-insensitive
`_nstartswith`	Does not start with	Case-insensitive
`_startswiths`	Starts with	Case-sensitive
`_nstartswiths`	Does not start with	Case-sensitive

Examples:

# Usernames starting with "admin"
GET /api/apps/blog-app/datatables/users/data/?username_startswith=admin

# Products not starting with "test"
GET /api/apps/shop-app/datatables/products/data/?sku_nstartswith=test

Ends With

Operator	Description	Case Sensitivity
`_endswith`	Ends with	Case-insensitive
`_nendswith`	Does not end with	Case-insensitive
`_endswiths`	Ends with	Case-sensitive
`_nendswiths`	Does not end with	Case-sensitive

Examples:

# Emails ending with specific domain
GET /api/apps/crm-app/datatables/users/data/?email_endswith=company.com

# Files not ending with .tmp
GET /api/apps/docs-app/datatables/files/data/?filename_nendswith=.tmp

Range Queries

Operator	Description	Format
`_between`	Value between range	`?field_between=min,max`
`_nbetween`	Value not in range	`?field_nbetween=min,max`

Examples:

# Posts created in January 2024
GET /api/apps/blog-app/datatables/posts/data/?created_at_between=2024-01-01,2024-01-31

# Products outside price range
GET /api/apps/shop-app/datatables/products/data/?price_nbetween=10,20

Null Checks

Operator	Description	Value
`_null`	Is null/not null	`true` or `false`
`_nnull`	Is not null/null	`true` or `false`

Examples:

# Users without bio
GET /api/apps/blog-app/datatables/users/data/?bio_null=true

# Posts with published date
GET /api/apps/blog-app/datatables/posts/data/?published_at_nnull=true

JSONB Field Querying

For tables using the JSONB provider, query nested fields using JSON path syntax:

# Query JSONB field properties
GET /api/apps/shop-app/datatables/products/data/?metadata__color=red

# Query nested properties
GET /api/apps/shop-app/datatables/users/data/?settings__notifications__email=true

# JSONB contains (PostgreSQL @> operator)
GET /api/apps/blog-app/datatables/posts/data/?tags__contains=["python","django"]

JSONB Operators:

field__property: Access nested property
field__contains: JSONB contains (exact match)
field__contained_by: JSONB is contained by
field__has_key: JSONB has top-level key
field__has_any_keys: JSONB has any of keys
field__has_all_keys: JSONB has all keys

Example with nested data:

{
  "id": 1,
  "name": "Premium Widget",
  "metadata": {
    "color": "red",
    "specs": {
      "weight": 150,
      "dimensions": {"width": 10, "height": 20}
    },
    "tags": ["electronics", "featured"]
  }
}

# Query nested specs
GET /data/?metadata__specs__weight_gt=100

# Query array elements
GET /data/?metadata__tags__contains=["featured"]

Case-Sensitive Filtering

Most string operators have both case-insensitive (default) and case-sensitive variants:

Pattern: Add s suffix for case-sensitive matching

Operation	Case-Insensitive	Case-Sensitive
Contains	`_contains`	`_containss`
Starts with	`_startswith`	`_startswiths`
Ends with	`_endswith`	`_endswiths`
In list	`_in`	`_ins`
In array	`_ina`	(case-insensitive only)

Examples:

# Case-insensitive: matches "Python", "python", "PYTHON"
GET /data/?title_contains=python

# Case-sensitive: matches only "Python" (exact case)
GET /data/?title_containss=Python

# Case-insensitive list matching
GET /data/?tag_ina=JavaScript,python,Ruby

When to use case-sensitive:

Matching code identifiers (className, functionName)
Exact acronyms (API, REST, HTTP)
File extensions (.PDF vs .pdf)
Programming language keywords

When to use case-insensitive (default):

User search queries
Email addresses
Natural language content
Most user-facing filters

Full-Text Search

For advanced text search across multiple fields:

# Search across indexed fields
GET /api/apps/blog-app/datatables/posts/data/?search=django tutorial

# Combine with filters
GET /api/apps/blog-app/datatables/posts/data/?search=python&status=published

Requirements:

Table must have search_vector field configured
GIN index recommended for performance
See Indexes Guide for setup

Search Features:

Multi-word queries
Relevance ranking
Stemming support (e.g., "running" matches "run", "runs")
Stop word filtering
AND/OR logic

Example with ranking:

# Returns results sorted by relevance
GET /api/apps/docs-app/datatables/articles/data/?search=django rest api

Response includes relevance score:

{
  "data": [
    {
      "id": 1,
      "title": "Django REST Framework Tutorial",
      "rank": 0.95,
      "excerpt": "Learn how to build REST APIs with Django..."
    },
    {
      "id": 2,
      "title": "Building APIs with Django",
      "rank": 0.72,
      "excerpt": "A comprehensive guide to Django API development..."
    }
  ]
}

Advanced search syntax:

# Phrase search (exact match)
GET /data/?search="django rest framework"

# Exclude terms with minus
GET /data/?search=python -flask

# OR operator
GET /data/?search=django OR flask

Sorting

Sort results by one or more fields.

Single Field

# Sort by username (ascending)
GET /api/apps/blog-app/datatables/users/data/?_sort=username

# Sort by created date (descending)
GET /api/apps/blog-app/datatables/posts/data/?_sort=created_at&_order=desc

Multiple Fields

# Sort by status, then by created_at descending
GET /api/apps/blog-app/datatables/posts/data/?_sort=status,created_at&_order=asc,desc

Parameters:

_sort: Comma-separated list of field names
_order: Comma-separated list of asc or desc (default: asc)

Pagination

Control the number of results returned.

Basic Pagination

# Get first 10 records
GET /api/apps/blog-app/datatables/posts/data/?_start=0&_end=10

# Get records 10-20
GET /api/apps/blog-app/datatables/posts/data/?_start=10&_end=20

# Get 50 records starting from 100
GET /api/apps/blog-app/datatables/posts/data/?_start=100&_end=150

Parameters:

_start: Starting index (0-based)
_end: Ending index (exclusive)

Response Format: The response includes total count and meta with pagination information:

{
  "data": [...],
  "total": 250,
  "meta": {
    "offset": 0,
    "limit": 10,
    "count": 10,
    "has_more": true
  }
}

Calculating Pages

const pageSize = 20;
const page = 1; // First page

const start = (page - 1) * pageSize;
const end = page * pageSize;

const url = `/api/apps/blog-app/datatables/posts/data/?_start=${start}&_end=${end}`;

Combining Filters

You can combine multiple filters, sorting, and pagination:

GET /api/apps/blog-app/datatables/posts/data/?\
  status=published&\
  views_gt=100&\
  category_in=tech,programming&\
  title_contains=python&\
  _sort=views,created_at&\
  _order=desc,desc&\
  _start=0&\
  _end=20

This query:

✅ Filters published posts
✅ With more than 100 views
✅ In tech or programming categories
✅ Title containing "python"
✅ Sorted by views (high to low), then by date (newest first)
✅ Returns first 20 results

Response Format

List Response

All list endpoints return a standardized response with data, total, and meta:

{
  "data": [
    {
      "id": 1,
      "title": "Getting Started with Python",
      "status": "published",
      "views": 1523,
      "category": "programming",
      "created_at": "2024-01-15T10:30:00Z"
    },
    {
      "id": 5,
      "title": "Advanced Python Techniques",
      "status": "published",
      "views": 856,
      "category": "tech",
      "created_at": "2024-01-20T14:20:00Z"
    }
  ],
  "total": 42,
  "meta": {
    "offset": 0,
    "limit": 20,
    "count": 2,
    "has_more": true
  }
}

Response Fields

data: Array of records matching the query
total: Total count of records matching filters (not just current page)
meta: Pagination metadata
- offset: Starting position of current page
- limit: Maximum records per page
- count: Number of records in current response
- has_more: Whether there are more records available

Use total and meta to implement pagination UI.

Aggregations

Perform SQL-like aggregations directly in the database for analytics and reporting.

Basic Aggregates

Count all records:

GET /api/apps/blog-app/datatables/posts/data/?_aggregate=count(*)

Response:

{
  "data": [],
  "aggregates": {
    "count": 150
  }
}

Sum, Average, Min, Max:

# Total revenue
GET /api/apps/shop-app/datatables/orders/data/?_aggregate=sum(total)

# Average rating
GET /api/apps/blog-app/datatables/posts/data/?_aggregate=avg(rating)

# Price range
GET /api/apps/shop-app/datatables/products/data/?_aggregate=min(price),max(price)

GROUP BY

Group results and aggregate per group:

# Count posts by category
GET /api/apps/blog-app/datatables/posts/data/?_aggregate=count(*)&_group_by=category

# Total sales by product
GET /api/apps/shop-app/datatables/orders/data/?_aggregate=sum(amount)&_group_by=product_id

Response:

{
  "data": [
    {"category": "tutorial", "count": 25},
    {"category": "news", "count": 15},
    {"category": "review", "count": 10}
  ],
  "total": 3
}

HAVING Clause

Filter aggregated results:

# Categories with more than 10 posts
GET /data/?_aggregate=count(*)&_group_by=category&_having=count__gt=10

# Products with high sales
GET /data/?_aggregate=sum(amount)&_group_by=product_id&_having=sum_amount__gte=10000

Multiple Aggregates

Combine multiple aggregate functions:

GET /data/?_aggregate=count(*),sum(price),avg(price),min(price),max(price)

Response:

{
  "aggregates": {
    "count": 50,
    "sum_price": 5000.00,
    "avg_price": 100.00,
    "min_price": 10.00,
    "max_price": 500.00
  }
}

Combining with Filters

Apply WHERE filters before aggregation:

# Published posts only, grouped by author
GET /data/?status=published&_aggregate=count(*)&_group_by=author_id

# Sales this month
GET /data/?created_at__gte=2024-03-01&_aggregate=sum(amount)

For complete aggregation documentation, see Aggregations Guide which covers:

All aggregate functions (count, sum, avg, min, max, array_agg, string_agg, json_agg, stddev, variance)
Advanced GROUP BY with multiple fields
Date/time grouping with date_trunc
HAVING clause operators
Complex analytics queries
Performance optimization

Examples by Use Case

Search Functionality

# Search users by name or email
GET /api/apps/crm-app/datatables/users/data/?\
  name_contains=john&\
  [email protected]

Recent Items

# Last 10 posts
GET /api/apps/blog-app/datatables/posts/data/?\
  _sort=created_at&\
  _order=desc&\
  _start=0&\
  _end=10

Active Users

# Users who logged in recently
GET /api/apps/crm-app/datatables/users/data/?\
  last_login_nnull=true&\
  _sort=last_login&\
  _order=desc

Status Filtering

# Pending orders
GET /api/apps/shop-app/datatables/orders/data/?\
  status=pending&\
  _sort=created_at&\
  _order=asc

Client Libraries

JavaScript/TypeScript

const fetchUsers = async (page = 1, pageSize = 20) => {
  const start = (page - 1) * pageSize;
  const end = page * pageSize;

  const params = new URLSearchParams({
    status: 'active',
    _sort: 'created_at',
    _order: 'desc',
    _start: start.toString(),
    _end: end.toString(),
  });

  const response = await fetch(
    `/api/apps/blog-app/datatables/users/data/?${params}`,
    {
      headers: {
        'Authorization': `Bearer ${token}`,
      },
    }
  );

  const result = await response.json();

  return {
    data: result.data,
    total: result.total,
    totalPages: Math.ceil(result.total / pageSize),
    meta: result.meta,
    hasMore: result.meta.has_more,
  };
};

Python

import requests

def fetch_posts(
    app_slug='blog-app',
    status='published',
    page=1,
    page_size=20,
    sort_by='created_at',
    order='desc'
):
    start = (page - 1) * page_size
    end = page * page_size

    params = {
        'status': status,
        '_sort': sort_by,
        '_order': order,
        '_start': start,
        '_end': end,
    }

    response = requests.get(
        f'https://api.yourapp.com/api/apps/{app_slug}/datatables/posts/data/',
        params=params,
        headers={'Authorization': f'Bearer {token}'}
    )

    result = response.json()

    return {
        'data': result['data'],
        'total': result['total'],
        'total_pages': -(-result['total'] // page_size),
        'meta': result.get('meta', {}),
        'has_more': result.get('meta', {}).get('has_more', False),
    }

Performance Tips

1. Use Specific Fields

Only select the fields you need (future feature):

# Coming soon: field selection
GET /api/apps/blog-app/datatables/posts/data/?fields=id,title,created_at

2. Limit Page Size

Don't request too many records at once:

# ✅ Good: Reasonable page size
?_start=0&_end=50

# ❌ Bad: Too many records
?_start=0&_end=10000

3. Use Indexes

Ensure your filtered and sorted fields have database indexes. Contact your administrator if queries are slow.

4. Cache Results

Cache frequently accessed data on the client side:

const cache = new Map();

async function fetchWithCache(url) {
  if (cache.has(url)) {
    return cache.get(url);
  }

  const response = await fetch(url);
  const data = await response.json();

  cache.set(url, data);
  setTimeout(() => cache.delete(url), 60000); // Cache for 1 minute

  return data;
}

Next Steps

CRUD Operations: Complete guide to creating, reading, updating, and deleting data
Aggregations: SQL-like GROUP BY, HAVING, and aggregate functions
Relationships: Query related data with populate
Indexes: Create indexes for better query performance
API Endpoints Reference: Complete endpoint listing

Common Patterns

Search with Autocomplete

# Search as user types
GET /api/apps/blog-app/datatables/users/data/?\
  username_startswith=joh&\
  _start=0&\
  _end=10

Infinite Scroll

let page = 1;
const pageSize = 20;

async function loadMore() {
  const start = (page - 1) * pageSize;
  const end = page * pageSize;

  const data = await fetchData({ _start: start, _end: end });
  appendToList(data);
  page++;
}

Faceted Search

# Get counts for each category
GET /api/apps/shop-app/datatables/products/data/?status=active

# Then filter by category
GET /api/apps/shop-app/datatables/products/data/?\
  status=active&\
  category=electronics

Basic Querying​

Get All Records​

Get Single Record​

Filtering​

Exact Match​

Comparison Operators​

IN Operators​

String Matching​

Contains​

Starts With​

Ends With​

Range Queries​

Null Checks​

JSONB Field Querying​

Case-Sensitive Filtering​

Full-Text Search​

Sorting​

Single Field​

Multiple Fields​

Pagination​

Basic Pagination​

Calculating Pages​

Combining Filters​

Response Format​

List Response​

Response Fields​

Aggregations​

Basic Aggregates​

GROUP BY​

HAVING Clause​

Multiple Aggregates​

Combining with Filters​

Examples by Use Case​

Search Functionality​

Recent Items​

Popular Content​

Active Users​

Status Filtering​

Client Libraries​

JavaScript/TypeScript​

Python​

Performance Tips​

1. Use Specific Fields​

2. Limit Page Size​

3. Use Indexes​

4. Cache Results​

Next Steps​

Common Patterns​

Search with Autocomplete​

Infinite Scroll​

Faceted Search​

Basic Querying

Get All Records

Get Single Record

Filtering

Exact Match

Comparison Operators

IN Operators

String Matching

Contains

Starts With

Ends With

Range Queries

Null Checks

JSONB Field Querying

Case-Sensitive Filtering

Full-Text Search

Sorting

Single Field

Multiple Fields

Pagination

Basic Pagination

Calculating Pages

Combining Filters

Response Format

List Response

Response Fields

Aggregations

Basic Aggregates

GROUP BY

HAVING Clause

Multiple Aggregates

Combining with Filters

Examples by Use Case

Search Functionality

Recent Items

Popular Content

Active Users

Status Filtering

Client Libraries

JavaScript/TypeScript

Python

Performance Tips

1. Use Specific Fields

2. Limit Page Size

3. Use Indexes

4. Cache Results

Next Steps

Common Patterns

Search with Autocomplete

Infinite Scroll

Faceted Search