Skip to content
TypeGraph
Blog

Aggregate

TypeGraph supports SQL-style aggregations for analytics and reporting. Group nodes by properties, compute aggregates like COUNT and SUM, and filter groups with HAVING clauses.

Call asRelation() on an aggregate query to filter its completed output or aggregate those results again. Both expression aggregates and compatibility aggregates support prepared execution and one-statement batching through the shared relation API.

Typed expression callbacks are recommended for new queries. They provide schema-checked operands, computed aggregate arguments, and inferred nullable result types:

import { expr } from "@nicia-ai/typegraph";


const companySizes = await store
  .query()
  .from("Person", "p")
  .groupBy((e) => [e.p.department])
  .having((e) => expr.gt(expr.count(e.p.id), expr.literal(5)))
  .aggregate((e) => ({
    department: e.p.department,
    employees: expr.count(e.p.id),
    payroll: expr.sum(e.p.salary),
  }))
  .execute();

The string helpers below remain supported as compatibility adapters. See Database Expressions for arithmetic, conditions, projection, and scope safety.

When to Use Aggregations

Section titled “When to Use Aggregations”

Aggregations are useful for:

  • Analytics dashboards: Employee counts by department, revenue by region
  • Reporting: Average order value, total sales by product category
  • Data exploration: Find groups meeting certain criteria
  • Metrics: Count active users, sum transaction amounts

Basic Aggregation

Section titled “Basic Aggregation”

Use groupBy() and aggregate() with aggregate helper functions:

import { count, field } from "@nicia-ai/typegraph";


const companySizes = await store
  .query()
  .from("Person", "p")
  .traverse("worksAt", "e")
  .to("Company", "c")
  .groupBy("c", "name")                    // Group by company name
  .aggregate({
    companyName: field("c", "name"),       // Include the grouped field
    employeeCount: count("p"),             // Count people in each group
  })
  .execute();


// Result: [{ companyName: "Acme Corp", employeeCount: 42 }, ...]

Aggregate Functions

Section titled “Aggregate Functions”

Import aggregate functions from @nicia-ai/typegraph:

import { count, countDistinct, sum, avg, min, max, field } from "@nicia-ai/typegraph";

count

Section titled “count”

Count rows in each group:

count("p")              // COUNT(p.id) - count all nodes
count("p", "department") // COUNT(p.props.department) - count non-null values

countDistinct

Section titled “countDistinct”

Count unique values:

countDistinct("p")              // COUNT(DISTINCT p.id)
countDistinct("p", "department") // COUNT(DISTINCT p.props.department)

Distinct counts support string, number, Boolean, and date fields. Structured JSON, array, embedding, and unresolved dynamic fields are refused so SQLite and PostgreSQL cannot disagree about value equality.

sum

Section titled “sum”

Sum numeric values:

sum("p", "salary")      // SUM(p.props.salary)

avg

Section titled “avg”

Average of numeric values:

avg("p", "age")         // AVG(p.props.age)

min / max

Section titled “min / max”

Minimum and maximum values:

min("p", "hireDate")    // MIN(p.props.hireDate)
max("p", "salary")      // MAX(p.props.salary)

Aggregate results follow JavaScript value conventions. count() and countDistinct() always return a number, including 0 for an empty input. sum(), avg(), min(), and max() return undefined when SQL produces NULL, such as an aggregate over an empty input. Minimum and maximum preserve the schema field’s scalar type, so string results remain strings and date results are decoded as Date values. Minimum and maximum support string, number, and date fields; schema-known boolean and structured operands are refused. Sum and average require numeric fields.

field

Section titled “field”

Include a grouped field in the output:

field("p", "department") // The grouped field value
field("c", "id")         // Node ID
field("c", "name")       // Property value

Multiple Aggregations

Section titled “Multiple Aggregations”

Combine multiple aggregates in one query:

import { count, countDistinct, sum, avg, min, max, field } from "@nicia-ai/typegraph";


const departmentStats = await store
  .query()
  .from("Employee", "e")
  .groupBy("e", "department")
  .aggregate({
    department: field("e", "department"),
    headcount: count("e"),
    uniqueRoles: countDistinct("e", "role"),
    avgSalary: avg("e", "salary"),
    minSalary: min("e", "salary"),
    maxSalary: max("e", "salary"),
    totalPayroll: sum("e", "salary"),
  })
  .execute();

Grouping by Multiple Fields

Section titled “Grouping by Multiple Fields”

Chain groupBy() calls for multi-column grouping:

const breakdown = await store
  .query()
  .from("Employee", "e")
  .groupBy("e", "department")
  .groupBy("e", "level")
  .aggregate({
    department: field("e", "department"),
    level: field("e", "level"),
    count: count("e"),
    avgSalary: avg("e", "salary"),
  })
  .execute();


// Result: [
//   { department: "Engineering", level: "Senior", count: 15, avgSalary: 150000 },
//   { department: "Engineering", level: "Junior", count: 8, avgSalary: 80000 },
//   { department: "Sales", level: "Senior", count: 5, avgSalary: 120000 },
//   ...
// ]

Grouping by Node

Section titled “Grouping by Node”

Use groupByNode() to group by unique nodes (by ID):

const projectContributions = await store
  .query()
  .from("Commit", "c")
  .traverse("author", "e")
  .to("Developer", "d")
  .groupByNode("d")                        // Group by developer node
  .aggregate({
    developerId: field("d", "id"),
    developerName: field("d", "name"),
    commitCount: count("c"),
  })
  .execute();

Filtering Groups with HAVING

Section titled “Filtering Groups with HAVING”

Use having() to filter groups based on aggregate values (SQL’s HAVING clause):

import { count, havingGt } from "@nicia-ai/typegraph";


// Only departments with more than 5 employees
const largeDepartments = await store
  .query()
  .from("Employee", "e")
  .groupBy("e", "department")
  .having(havingGt(count("e"), 5))         // HAVING COUNT(e) > 5
  .aggregate({
    department: field("e", "department"),
    headcount: count("e"),
  })
  .execute();

Available HAVING Helpers

Section titled “Available HAVING Helpers”
import {
  having,
  havingGt,
  havingGte,
  havingLt,
  havingLte,
  havingEq,
} from "@nicia-ai/typegraph";


// Comparison helpers
havingGt(aggregate, value)   // >
havingGte(aggregate, value)  // >=
havingLt(aggregate, value)   // <
havingLte(aggregate, value)  // <=
havingEq(aggregate, value)   // =


// Generic comparison (for custom operators)
having(aggregate, "gt", value)

Multiple HAVING Conditions

Section titled “Multiple HAVING Conditions”

Chain multiple having conditions:

const qualifiedDepartments = await store
  .query()
  .from("Employee", "e")
  .groupBy("e", "department")
  .having(havingGte(count("e"), 5))        // At least 5 employees
  .having(havingGte(avg("e", "salary"), 100000)) // Average salary >= 100k
  .aggregate({
    department: field("e", "department"),
    headcount: count("e"),
    avgSalary: avg("e", "salary"),
  })
  .execute();

Aggregations with Traversals

Section titled “Aggregations with Traversals”

Combine graph traversals with aggregations:

const topContributors = await store
  .query()
  .from("PullRequest", "pr")
  .whereNode("pr", (pr) => pr.state.eq("merged"))
  .traverse("targetsRepo", "e1")
  .to("Repository", "repo")
  .traverse("author", "e2", { direction: "in" })
  .to("Developer", "dev")
  .groupBy("repo", "name")
  .groupBy("dev", "name")
  .aggregate({
    repository: field("repo", "name"),
    developer: field("dev", "name"),
    prCount: count("pr"),
    linesChanged: sum("pr", "linesAdded"),
  })
  .limit(50)
  .execute();

Ordering Aggregated Results

Section titled “Ordering Aggregated Results”

Aggregate queries have their own orderBy(key, direction?), called after .aggregate({...}). key is any output name from the fields object — a grouped field or an aggregate alias — so limit() finally means “top N”, not “an arbitrary N”:

const topDepartments = await store
  .query()
  .from("Employee", "e")
  .groupBy("e", "department")
  .aggregate({
    department: field("e", "department"),
    headcount: count("e"),
    totalSalary: sum("e", "salary"),
  })
  .orderBy("totalSalary", "desc")
  .limit(10)
  .execute();

Real-World Example: Team Analytics

Section titled “Real-World Example: Team Analytics”
import { count, countDistinct, sum, avg, field, havingGt } from "@nicia-ai/typegraph";


// 1. Productivity by department
const departmentMetrics = await store
  .query()
  .from("Developer", "dev")
  .traverse("authored", "e")
  .to("PullRequest", "pr")
  .whereNode("pr", (pr) => pr.state.eq("merged"))
  .groupBy("dev", "department")
  .aggregate({
    department: field("dev", "department"),
    developerCount: countDistinct("dev"),
    totalPRs: count("pr"),
    totalLinesAdded: sum("pr", "linesAdded"),
    avgLinesPerPR: avg("pr", "linesAdded"),
  })
  .execute();


// 2. Active reviewers (reviewed > 10 PRs)
const activeReviewers = await store
  .query()
  .from("Developer", "d")
  .traverse("reviewed", "r")
  .to("PullRequest", "pr")
  .groupByNode("d")
  .having(havingGt(count("pr"), 10))
  .aggregate({
    developer: field("d", "name"),
    reviewCount: count("pr"),
  })
  .orderBy("reviewCount", "desc")
  .execute();


// 3. Repository health
const repoHealth = await store
  .query()
  .from("Repository", "r")
  .traverse("contains", "e")
  .to("PullRequest", "pr")
  .groupByNode("r")
  .aggregate({
    repo: field("r", "name"),
    openPRs: count("pr"),
    avgAge: avg("pr", "daysOpen"),
  })
  .execute();

Next Steps

Section titled “Next Steps”
  • Shape - Output transformation with select()
  • Order - Ordering and limiting results
  • Traverse - Graph traversals