aggregation.md

Aggregation Rules

Guide for building ObjectStack aggregation queries.

Aggregation Functions

Function	SQL Equivalent	Purpose	Requires field
count	COUNT(*) / COUNT(field)	Count rows	Optional
sum	SUM(field)	Sum numeric values	Yes
avg	AVG(field)	Average numeric values	Yes
min	MIN(field)	Minimum value	Yes
max	MAX(field)	Maximum value	Yes
count_distinct	COUNT(DISTINCT field)	Count unique values	Yes
array_agg	ARRAY_AGG(field)	Collect values into array	Yes
string_agg	STRING_AGG(field, ',')	Concatenate string values	Yes

⚠️ Driver support varies. On SQL datasources the driver executes only count / sum / avg / min / max and throws (Unsupported aggregate function) on count_distinct, array_agg, and string_agg; the per-aggregation distinct: true flag is also ignored there. The in-memory aggregation path (driver-rest, driver-memory, timezone/ date-bucket fallbacks) supports all 8 functions plus distinct. For portable queries, stick to the first five.

Basic Aggregation

// SQL: SELECT COUNT(*) AS total_orders FROM order
{
  object: 'order',
  aggregations: [
    { function: 'count', alias: 'total_orders' }
  ]
}

Aggregation with GROUP BY

// SQL: SELECT region, SUM(amount) AS total, AVG(amount) AS average
//      FROM sale GROUP BY region
{
  object: 'sale',
  fields: ['region'],
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total' },
    { function: 'avg', field: 'amount', alias: 'average' }
  ],
  groupBy: ['region']
}

Note: you do NOT need to repeat groupBy fields in fields — drivers auto-select every grouped field into the result rows. Listing them in fields (as above) is a readability convention, not a requirement.

Date-Bucketed Grouping (dateGranularity)

groupBy entries can be structured objects that bucket a date/timestamp field into uniform periods — this is the supported way to build time-series aggregations (never bucket by hand in app code):

// Revenue per quarter per region
{
  object: 'deal',
  groupBy: [
    'region',
    { field: 'closed_at', dateGranularity: 'quarter' }
  ],
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'revenue' }
  ]
}
// Result rows: { region: 'us', closed_at: '2025-Q1', revenue: 42000 }, ...

• Granularities: day, week, month, quarter, year (weeks are ISO-8601, starting Monday).
• Optional alias renames the projected group value: { field: 'closed_at', dateGranularity: 'quarter', alias: 'quarter' }.
• The engine pushes bucketing down to the driver (DATE_TRUNC etc.) when the dialect supports that granularity, and transparently falls back to in-memory bucketing otherwise — results are correct either way.

HAVING Clause

⚠️ Schema-reserved — NOT executed by the engine yet. having validates against QuerySchema, but EngineAggregateOptions has no having property and nothing implements it — the clause is silently dropped and every group is returned. Working alternative: post-filter the aggregated rows in application code:

// ❌ having is silently ignored
// { ..., having: { order_count: { $gt: 5 } } }

// ✅ Aggregate, then filter the result rows in app code
const rows = await engine.aggregate('order', {
  groupBy: ['customer_id'],
  aggregations: [{ function: 'count', alias: 'order_count' }],
});
const frequentCustomers = rows.filter((r) => r.order_count > 5);

Aggregated result sets are one row per group — usually small enough that an app-side filter is cheap. Use where to shrink the input rows first.

Filtered Aggregation (FILTER WHERE)

⚠️ Per-aggregation filter is schema-reserved — NOT executed by the engine yet. The SQL driver never reads it and the in-memory path ignores it, so the aggregation returns the unfiltered number — silently wrong results. Working alternative: one aggregate call per condition, with the condition in the query-level where:

// ❌ filter on the aggregation is silently ignored — active_count
//    would equal total!
// { function: 'count', alias: 'active_count', filter: { status: 'active' } }

// ✅ Separate aggregate calls, condition in `where`
const [totals] = await engine.aggregate('user', {
  aggregations: [{ function: 'count', alias: 'total' }],
});
const [active] = await engine.aggregate('user', {
  where: { status: 'active' },
  aggregations: [{ function: 'count', alias: 'active_count' }],
});

DISTINCT Aggregation

⚠️ Not available on SQL datasources. count_distinct throws on the SQL driver, and the distinct: true flag is silently ignored there (see the driver-support caveat above). Both forms work only on the in-memory aggregation path. On SQL, get a distinct count by grouping on the field and counting the result rows in app code: (await engine.aggregate('employee', { groupBy: ['department'], aggregations: [{ function: 'count', alias: 'n' }] })).length.

// In-memory drivers only:
// SQL: SELECT COUNT(DISTINCT department) FROM employee
{
  object: 'employee',
  aggregations: [
    { function: 'count_distinct', field: 'department', alias: 'dept_count' }
  ]
}

// Alternative (also in-memory only): use distinct flag
{
  object: 'employee',
  aggregations: [
    { function: 'count', field: 'department', alias: 'dept_count', distinct: true }
  ]
}

Window Functions

⚠️ Schema-reserved — NOT executed by the engine yet. The QueryAST schema declares windowFunctions (enum: row_number, rank, dense_rank, percent_rank, lag, lead, first_value, last_value, sum, avg, count, min, max), but the engine never routes the property to any driver — it is silently dropped. Even the SQL driver's internal builder drops the field argument (lag(revenue) would render as LAG()). Do not emit windowFunctions in queries.

Working alternatives:

Ranking / Top-N per Group

Model rankings in report/dashboard metadata (groupings + measures + sort), or fetch the ordered rows and rank in app code:

// Top products per category — order the rows, rank in app code
const rows = await engine.find('product', {
  fields: ['name', 'category', 'sales'],
  orderBy: [
    { field: 'category', order: 'asc' },
    { field: 'sales', order: 'desc' },
  ],
});
// Assign category_rank while iterating: reset the counter when category changes.

Running Total

Fetch the ordered rows and accumulate in app code:

const txns = await engine.find('transaction', {
  fields: ['date', 'amount'],
  orderBy: [{ field: 'date', order: 'asc' }],
});
let runningTotal = 0;
const withTotals = txns.map((t) => ({ ...t, running_total: (runningTotal += t.amount) }));

Period-over-Period

For dashboard widgets, use the higher-level compareTo: 'previousPeriod' | 'previousYear' | { offset } field on the widget schema (see objectstack-ui → Period-over-period — compareTo). The renderer issues the shifted query for you and aligns the result bucket-for-bucket with categoryGranularity. For ad-hoc comparisons, run two date-bucketed aggregations (see Date-Bucketed Grouping above) over the two periods and join the buckets in app code.

Common Mistakes

❌ Wrong: Aggregation without alias

// ❌ alias is required
{
  aggregations: [
    { function: 'count' }
  ]
}

// ✅ Always provide alias
{
  aggregations: [
    { function: 'count', alias: 'total' }
  ]
}

❌ Wrong: Using where to filter aggregated results

// ❌ where filters BEFORE aggregation
{
  object: 'order',
  where: { order_count: { $gt: 5 } },  // order_count doesn't exist yet!
  aggregations: [{ function: 'count', alias: 'order_count' }],
  groupBy: ['customer_id']
}

// ❌ Also wrong: having is schema-reserved and silently ignored (see above)
// { ..., having: { order_count: { $gt: 5 } } }

// ✅ Aggregate, then filter the group rows in app code
const rows = await engine.aggregate('order', {
  groupBy: ['customer_id'],
  aggregations: [{ function: 'count', alias: 'order_count' }],
});
const result = rows.filter((r) => r.order_count > 5);

❌ Wrong: sum/avg on non-numeric fields

// ❌ Cannot sum a string field
{
  aggregations: [
    { function: 'sum', field: 'name', alias: 'total' }
  ]
}

// ✅ sum/avg only work on numeric fields
{
  aggregations: [
    { function: 'sum', field: 'amount', alias: 'total' }
  ]
}