Aggregations And Functions Use aggregate fields, analytics directives, SQL functions, full-text search, and database function fields. core reference core/aggregations-functions core/aggregations-functions.md

Aggregations And Functions

Use aggregate fields, analytics directives, SQL functions, full-text search, and database function fields.

Aggregate fields

GraphJin exposes aggregate fields by prefixing a column with the aggregate name.

Field shapeMeaning
count_idCount non-null id values.
sum_priceSum a numeric column.
avg_priceAverage a numeric column.
min_price, max_priceMinimum and maximum values.
GraphQL
query {
  products(where: { id: { lteq: 100 } }) {
    count_id
    min_price
    max_price
    avg_price
  }
}
Verified by Example_queryWithAggregation tests/query_test.go:769

If a select contains only aggregates and no distinct, GraphJin returns a single global aggregate row instead of a page of per-row aggregate values.

Verified by Example_queryWithGlobalAggBrokenMdFix tests/query_test.go:2508

Grouped summaries

Use distinct to group by one or more columns, then request aggregate fields for each group.

GraphQL
query {
  products(
    distinct: [country_code]
    where: { id: { lteq: 100 } }
    order_by: { count_id: desc }
  ) {
    country: country_code
    count_id
    avg_price
  }
}

Grouped queries still follow SQL grouping rules. If a nested join would require a non-grouped foreign key, GraphJin should reject the ambiguous shape instead of emitting broken SQL; root the query at the dimension table when you need a nested object per group.

Verified by TestStage3_DistinctAggregateNestedJoinThroughNonDistinctColumn tests/query_test.go:2548

Expression aggregates

Expression aggregates calculate metrics in the database, not in client code.

GraphQL
query {
  products(where: { id: { lteq: 100 } }) {
    doubled: sum(expr: { mul: [id, 2] })
  }
}

Expressions support arithmetic operators such as add, sub, mul, div, mod, literal values, column references, casts, coalesce, nullif, and case expressions.

Verified by Example_queryWithExprMul tests/query_test.go:2390

Ratio-of-aggregates uses a bare expression field whose nested expression contains aggregate nodes:

GraphQL
query {
  products(where: { id: { lteq: 100 } }) {
    ratio: ratio(expr: {
      div: [
        { sum: { mul: [id, 2] } }
        { sum: id }
      ]
    })
  }
}
Verified by Example_queryWithExprBare tests/query_test.go:2428

Expression arguments go through role allow-list checks. A role that cannot read price cannot leak it through sum(expr: { mul: [price, 2] }).

Verified by Example_queryWithExprRoleAllowlist tests/query_test.go:2468

Analytics

Analytics directives keep rows visible while adding report metrics such as running totals, moving averages, previous/next values, first/last values, and rank within a group.

Use them when you need report-style columns but not a one-row-per-group aggregate result.

GraphQL
query {
  products {
    id
    price
    running_total: price @running(
      aggregate: sum
      by: "user_id"
      orderBy: { created_at: asc }
    )
    moving_avg: price @moving(
      aggregate: avg
      rows: 6
      by: "user_id"
      orderBy: { created_at: asc }
    )
    previous_price: price @previous(by: "user_id", orderBy: { created_at: asc })
    rank_by_price: price @rank(by: "user_id", order: desc)
  }
}
DirectiveUse
@running(aggregate:)Running sum, avg, count, min, or max.
@moving(aggregate:, rows:)Trailing window over the current and previous rows.
@previous, @nextPeriod comparisons with lag and lead.
@first, @lastFirst or last value in an ordered partition.
@rank, @denseRank, @rowNumberRanking and row numbering inside an optional partition.

by accepts a column name or list of column names. orderBy uses the same object ordering shape as normal queries. order is shorthand for ordering by the annotated field. Analytics require a deterministic order.

Verified by TestAnalytics_RendersRunningOverClause core/internal/psql/window_test.go:13
Verified by TestAnalytics_DialectRendering core/internal/psql/window_test.go:56

Full-text search is available through configured full-text columns and supported dialects.

GraphQL
query SearchProducts($query: String!) {
  products(search: $query, limit: 5) {
    id
    name
    search_rank
  }
}
Verified by Example_queryBySearch tests/query_test.go:586

SQL functions

PostgreSQL function fields can expose scalar values, table-returning functions, named args, user-defined return types, and directive behavior.

GraphQL
query {
  hotProducts(where: { productID: { eq: 55 } }, order_by: { productID: desc }) {
    productID
    countryCode
    countProductID
  }
}

Verified by Example_queryWithFunctionFields tests/query_pg_test.go:140
Verified by Example_queryWithFunctionReturingTablesWithNamedArgs tests/query_pg_test.go:270

Functions that return rows of a table, view, or composite type expose that row’s columns. This covers RETURNS SETOF users, RETURNS users, and RETURNS TABLE (LIKE users). The configured blocklist applies to these columns as it does to table columns.

GraphQL
query {
  get_users_up_to(order_by: { id: asc }, args: { max_id: 2 }) {
    id
    full_name
    email
  }
}

Verified by Example_queryWithFunctionReturningTableLike tests/query_pg_test.go:332
Verified by Example_queryWithFunctionReturningSetofTable tests/query_pg_test.go:363

Dialect boundaries

FeatureBoundary
Analytics directivesSupported on modern SQL dialects with window functions. MongoDB and older SQL versions reject them with compile-time errors.
Expression aggregatesSQL path is covered; MongoDB currently rejects expression aggregates as unsupported.
Function fieldsDatabase functions are dialect-specific; PostgreSQL has the broadest tested surface.
Role allow-listsAggregate and expression arguments must use allowed columns.

Verified by TestAnalytics_DialectUnsupported core/internal/psql/window_test.go:106
Verified by Example_queryWithAggregationBlockedColumn tests/query_test.go:794

Docs