[try:/devdoc/test-query|Test Queries|play|right]

Overview

Our query language is GraphQL-inspired but designed for SQL-based backends and REST API adaptation. It uses a familiar, nested, and expressive syntax that eliminates the N+1 problem through intelligent batching and query optimization.

card: GraphQL-Inspired
Familiar nested syntax with entity selection, field specification, and parameter support
card: REST Compatible
Can translate queries to REST endpoints for compatibility with existing services
card: Performance Optimized
Eliminates N+1 SQL problems through automatic batching and query optimization

Query Examples

Basic Query

Fetch all fields from an entity [badge:Basic|info|right]

products {
    id
    name
    price
    description
}

Query with Parameters

Use parameters for filtering and pagination [badge:Parameters|info|right]

products(
    where: "status = 'active'",
    limit: 10,
    orderBy: [name@asc]
) {
    id
    name
    price
}

Nested Relations

Fetch related entities in a single query [badge:Relations|info|right]

products {
    id
    name
    category {
        id
        name
    }
    variants(limit: 10) {
        id
        price
    }
}

Query with Aggregations

Include aggregation functions in your query [badge:Aggregation|info|right]

products(
    where: "price > 100"
) {
    id
    name
    @count as total_products
    @sum(price) as total_price
}

Field Functions

Apply functions to fields for transformations [badge:Functions|info|right]

categories() {
    name,
    length(name) as name_length,
    isnull(description) as has_no_description,
    upper(code) as code_upper
}

Date Functions

Extract date/time components from fields [badge:Functions|info|right]

orders() {
    id,
    order_date,
    year(order_date) as order_year,
    month(order_date) as order_month,
    quarter(order_date) as order_quarter,
    dayofweek(order_date) as weekday
}

Date Range Functions

Get precise time period boundaries for analytics [badge:Functions|info|right]

analytics() {
    id,
    event_date,
    startOfYear(event_date) as year_start,
    endOfYear(event_date) as year_end,
    startOfMonth(event_date) as month_start,
    endOfMonth(event_date) as month_end,
    startOfHour(event_date) as hour_start,
    endOfHour(event_date) as hour_end
}

GROUP BY with Functions

Use functions in aggregation grouping [badge:Aggregation|info|right]

sales(
    @count as total_sales,
    @sum(amount) as total_revenue,
    @groupBy(year(sale_date), month(sale_date))
) { * }

Gap-Fill for Time Series Charts

Fill missing periods in grouped results for continuous charts [badge:Aggregation|info|right]

orders(
    @count as order_count,
    @sum(total) as monthly_revenue,
    @groupBy(yearmonth(created_at)),
    @fillGaps(yearmonth(created_at))
) { }

With an explicit date range (extend beyond data boundaries):

orders(
    @count as order_count,
    @sum(total) as monthly_revenue,
    @groupBy(yearmonth(created_at)),
    @fillGaps(yearmonth(created_at), from: "2024-01", to: "2024-12")
) { }

Complex Query

Combine multiple features in one query [badge:Advanced|info|right]

orders(limit: 10) {
    id
    total
    user {
        id
        name
    }
    items {
        id
        quantity
        product {
            id
            name
            price
        }
    }
}

Translatable Fields

Query multilingual fields with language-specific selection [badge:Advanced|info|right]

products(limit: 10) {
    id
    code
    name              // Returns {"en": "English", "cs": "Czech"}
    name:cs           // Returns Czech translation only
    name:en           // Returns English translation only
    description:cs as czech_desc
    category {
        name:cs
        name:en
    }
}

Historical Data Query

Query historical versions from archive tables combined with current row [badge:Advanced|info|right]

products(
    @history
    where: "id = 123"
) {
    id
    name
    price
    _deleted
    _deletedAt
    _archiveValidSince
    _archiveValidTo
}

Point-in-Time History Query

Query data as it existed at a specific timestamp plus current row [badge:Advanced|info|right]

products(
    @history(from: "2025-01-01T00:00:00")
    where: "price > 100"
    limit: 10
) {
    id
    name
    price
    _archiveValidSince
    _archiveValidTo
}

Syntax Rules

Field Functions

Transform and analyze field values directly in your queries

Field functions allow you to apply transformations and checks to fields in the select clause. Functions can be used standalone or with aliases using the as keyword.

String Functions

Date / Time Functions

Date Range Functions

Usage Examples

String transformation functions:

users() {
    name,
    lower(name) as name_lower,
    upper(code) as code_upper,
    length(description) as desc_length
}

Date/time extraction functions:

orders() {
    id,
    order_date,
    year(order_date) as order_year,
    month(order_date) as order_month,
    dayofweek(order_date) as weekday
}

Mixed function usage:

products() {
    name,
    isnull(description) as has_no_description,
    length(name) as name_length,
    yearmonth(created_at) as creation_month
}

Date range functions for time period analysis:

analytics() {
    id,
    event_date,
    startOfYear(event_date) as year_start,
    endOfYear(event_date) as year_end,
    startOfMonth(event_date) as month_start,
    endOfMonth(event_date) as month_end,
    startOfDay(event_date) as day_start,
    endOfDay(event_date) as day_end,
    startOfHour(event_date) as hour_start,
    endOfHour(event_date) as hour_end
}

Functions in GROUP BY aggregations:

orders(
    @count as order_count,
    @sum(total) as total_revenue,
    @groupBy(year(created_at), month(created_at))
) { * }

// Date range functions in GROUP BY
events(
    @count as event_count,
    @sum(value) as total_value,
    @groupBy(startOfYear(event_date), startOfMonth(event_date))
) { * }

**Notes**
- Functions can be aliased using the `as` keyword
- All function names are case-insensitive
- String functions work with any field that can be converted to string
- Date/time functions work with DATE and DATETIME field types
- Date range functions return precise timestamps using PostgreSQL's DATE_TRUNC and INTERVAL operations
- Functions can be used in both field selection and @groupBy aggregations
- `dayofweek()` returns 0-6 where 0=Sunday, 6=Saturday
- `quarter()` returns 1-4 for Q1-Q4
- `yearmonth()` returns format 'YYYY-MM' (e.g., '2023-12')
- `yearweek()` returns format 'YYYY-WW' (e.g., '2023-52')
- `yearquarter()` returns format 'YYYY-QN' (e.g., '2023-Q4')
- Date range functions provide microsecond precision for accurate time period boundaries

Advanced Features

Field Functions

Apply transformations and checks directly in queries

lower(name), year(created_at), length(description)

Multiple ID Queries

Fetch by one or more IDs using arrays and mixed types

products(id: ['id1', "uuid", 123]) { * }

Deep Nesting

Access deeply nested relations without performance penalties

orders { user { profile { preferences } } }

Comments Support

Add comments to your queries for documentation

// Single line comment
/* Multi-line comment */
products(limit: 10) {
    id
    name // inline comment
}

Translatable Fields

Translatable (multilingual) fields can be queried in multiple ways:

Without language suffix:

products { name, description }

Returns all filled languages as JSON object: {"en": "English", "cs": "Czech"}

With language suffix:

products {
    name:cs,    // Czech only
    name:en,    // English only
    name:de     // German only
}

Returns only the specific language as string. Supports: cs, en, de, sk, pl, hu, fr, es

With aliases:

products {
    name:cs as czech_name,
    name:en as english_name
}

Use aliases to rename fields with language suffixes

**Language Selection:**
- Language is selected using the Accept-Language HTTP header. 
- All translatable fields in query results return all the filled languages by default (without language suffix). 
- Use fieldName:languageCode syntax to get only specific language. 
- Language suffix works both in WHERE filters and SELECT fields. 
- If the requested language is not available, the field may be empty or null. 
- Example: `Accept-Language: en-US, cs-CZ;q=0.9`

Lookups and External IDs

You can reference entities using lookup identifiers instead of numeric/UUID IDs. This is especially useful when working with data from external systems like ABRA Flexi/FlexiBee.

Standard Lookups

Use field-based identifiers like code:, ean:, or guid:

Using code lookup:

products(id: "code:ABC123") { id, name, code }

Using lookup in WHERE clause:

products(where: "category = 'code:ELEC'") { id, name }

Multiple IDs with lookups:

products(id: ["code:ABC", "ean:123456", "uuid-value"]) { * }

External ID Lookups

External IDs (ext:) allow you to reference entities using identifiers from external systems. These IDs are stored in a dedicated extIds table and support multi-part keys with colons.

Using external ID in query:

products(id: "ext:SYSTEM:PRODUCT:12345") { id, name, code }

Using external ID in WHERE clause:

products(where: "category = 'ext:SYSTEM:CATEGORY:ELEC'") { id, name }

Mixed identifiers (standard + external):

products(id: ["code:ABC", "ext:SYSTEM:PRODUCT:789"]) { * }

External ID in relation fields:

orders(where: "customer = 'ext:CRM:CUSTOMER:C001'") {
    id
    customer {
        id
        name
    }
}

Multi-part external IDs (with colons):

products(id: "ext:SYSTEM:NAMESPACE:TYPE:ID:123") { id, name }

- External IDs are automatically created when importing data with `ext:` identifiers
- The same entity can have multiple external IDs from different systems
- External IDs work everywhere regular IDs work (queries, WHERE clauses, relation fields)
- Everything after `ext:` is treated as the external identifier
- External IDs are case-insensitive when matching
- Stored in dedicated `extIds` table with fields: code, related_id, related_entity

API Endpoint: /api/query

The /api/query endpoint accepts POST requests with query arrays and returns streaming JSON responses for optimal performance. Multiple queries are processed in parallel and results are pushed as soon as available.

Required Headers

[navigate:/apiKeys|Manage API Keys|key]

Request Format

POST /api/query
Content-Type: application/json

[
  {
    "requestId": "products",
    "query": "products(where: \"category.name = ${categoryName}\", limit: 5) { id, name, price }",
    "params": {"categoryName": "Electronics"}
  },
  {
    "requestId": "categories",
    "query": "categories { id, name }",
    "params": {}
  }
]

Success Response

[
  {
    "requestId": "products",
    "success": true,
    "data": [
      {"id": 1, "name": "Product 1", "price": 100},
      {"id": 2, "name": "Product 2", "price": 150}
    ]
  }
]

Error Response

[
  {
    "requestId": "products",
    "success": false,
    "error": "Invalid where clause syntax",
    "errorCode": "QUERY_ERROR"
  }
]

Responses are streamed part by part for optimal performance. Multiple queries are processed in parallel, and results may arrive in different order than requested. Use a partial JSON parser that can handle incomplete JSON when processing the response stream.

Gap-Filling for Time Series (@fillGaps)

Fill missing periods in grouped results for smooth, continuous charts

When building charts or reports over time, grouped results often have gaps — months, weeks, or years where no data exists. @fillGaps automatically inserts rows for those missing values so the result set is continuous.

Use @fillGaps together with @groupBy on the same field. It runs as a post-processing step and does not modify your data.

How It Works

• Missing intervals are detected by comparing the grouped field values against the expected range.
• Range auto-detection: by default, the range spans from the smallest to the largest value in the actual data.
• Explicit range: use from and/or to to extend the range beyond what the data contains.
• @count and @sum aggregations are filled with 0 for missing rows.
• @avg, @min, @max aggregations are filled with null for missing rows.

Supported Field Types

Monthly trend with auto-detected range

orders(
    @count as order_count,
    @sum(total) as monthly_revenue,
    @groupBy(yearmonth(created_at)),
    @fillGaps(yearmonth(created_at))
) { }

Months with no orders get order_count=0 and monthly_revenue=0

Full-year dashboard (explicit range)

orders(
    @count as order_count,
    @avg(total) as avg_order_value,
    @groupBy(yearmonth(created_at)),
    @fillGaps(yearmonth(created_at), from: "2024-01", to: "2024-12")
) { }

Always returns exactly 12 rows, Jan–Dec 2024

Weekly activity with null metrics for empty weeks

events(
    @count as event_count,
    @avg(duration) as avg_duration,
    @groupBy(yearweek(event_date)),
    @fillGaps(yearweek(event_date))
) { }

Empty weeks get event_count=0 and avg_duration=null

Integer field grouping

products(
    @count as product_count,
    @groupBy(month),
    @fillGaps(month, from: 1, to: 12)
) { }

- `@fillGaps` must be used together with `@groupBy` on the same field
- Auto-detection fills from the minimum to the maximum value present in the data
- You can specify `from` only, `to` only, or both for fine-grained control
- Numeric `from`/`to` values are written without quotes; string values (yearmonth, yearweek) use double quotes
- The directive only affects the result set; it does not modify data or run extra SQL queries

History Queries (@history)

Query historical data from archive tables combined with current row

The @history directive allows you to query historical data from archive tables. When entities support archiving, all changes to records are preserved in special archive tables that maintain a complete history of modifications and deletions. The query returns both historical versions and the current state of records.

How It Works

The @history directive uses SQL UNION ALL to combine:

• Archive table rows — historical versions from {entity}_archive
• Current table row — the current state from {entity}

Results are ordered by _archiveValidTo DESC NULLS LAST, meaning historical versions appear first (most recent to oldest), and the current row appears last.

Archive-Specific Fields

Query all historical versions plus current row

products(@history) {
    id
    name
    price
    _deleted
    _deletedAt
    _archiveValidSince
    _archiveValidTo
}

Point-in-time query (data as of a specific date)

products(
    @history(from: "2025-01-01T00:00:00")
    where: "price > 100"
) {
    id
    name
    price
    _archiveValidSince
    _archiveValidTo
}

Find all changes to a specific product

products(
    @history
    where: "id = 123"
) {
    id
    name
    price
    _archiveValidSince
    _archiveValidTo
}

Find deleted records

products(
    @history
    where: "_deleted = true"
) {
    id
    name
    _deletedAt
    _archiveValidSince
    _archiveValidTo
}

Common Use Cases

card: Audit Trail
Track all changes to sensitive data for compliance
card: Time Travel
View data as it existed at any point in the past
card: Change Analysis
Analyze patterns in data modifications over time
card: Deleted Records Recovery
Find and review deleted records alongside current records

- Not all entities support archiving. If you use @history on an unsupported entity, you'll receive an error
- The query always includes the current state of records as the last row(s) in the result set
- Archive tables can contain many versions, so use appropriate where clauses and limit parameters
- Timestamps should be provided in ISO 8601 format (e.g., "2025-01-01T12:00:00")
- For the current row, _archiveValidTo is NULL and _deleted is always false

Filtering with WHERE Clauses

The where parameter uses our powerful Filter System that supports various operators, logical conditions, and relationship filtering. Learn about all available operators and see practical examples.

products(where: "price > 100 and category.name = 'Electronics'") { id, name, price }

[navigate:/devdoc/where|View WHERE Clause Documentation|filter] [navigate:/devdoc/test-query|Test Queries|play]

Comprehensive guide to filtering and search capabilities

Performance & Best Practices

card: N+1 Problem Elimination
The system automatically batches and optimizes nested queries to prevent N+1 SQL problems
card: Selective Field Fetching
Only request the fields you need to minimize data transfer and processing
card: Use Pagination
Always use limit and offset for large datasets to improve response times. Default limit is 1000 rows — use limit: 0 for unlimited results (use with caution).
card: Streaming Results
Multiple queries may return results in optimized order for better throughput