Aggregate Queries

aggregate() computes totals over the rows a query matches, or over each group of them, as SQL’s GROUP BY and HAVING do. The same query runs on every SQL engine and on MongoDB.

You write

import { pool } from './uql.config.js';
import { Order } from './shared/models/index.js';

const results = await pool.aggregate(Order, {
  $where: { amount: { $gt: 0 } }, // filters rows before grouping
  $group: { status: true },
  $select: {
    total: { $sum: { amount: true } },
    count: { $count: '*' },
  },
  $having: { count: { $gt: 5 } }, // filters groups after aggregation
  $sort: { total: -1 },
  $limit: 10,
});
// [{ status: 'paid', total: 5400, count: 31 }, ...]

PostgreSQL

SELECT "status", SUM("amount") "total", COUNT(*) "count"
FROM "Order"
WHERE "amount" > $1
GROUP BY "status"
HAVING COUNT(*) > $2
ORDER BY SUM("amount") DESC
LIMIT 10

MongoDB

[
  { "$match": { "amount": { "$gt": 0 } } },
  {
    "$group": {
      "_id": { "status": "$status" },
      "total": { "$sum": "$amount" },
      "_uql_count_total": {
        "$sum": {
          "$cond": [{ "$eq": [{ "$ifNull": ["$amount", null] }, null] }, 0, 1]
        }
      },
      "count": { "$sum": 1 }
    }
  },
  {
    "$project": {
      "_id": 0,
      "status": "$_id.status",
      "total": {
        "$cond": [{ "$eq": ["$_uql_count_total", 0] }, null, "$total"]
      },
      "count": 1
    }
  },
  { "$match": { "count": { "$gt": 5 } } },
  { "$sort": { "total": -1 } },
  { "$limit": 10 }
]

Each result row holds the $group columns and the $select aliases. $select takes computed columns only: to return a plain column, group by it. Fields are named as keys, as in findMany, which makes a typo a compile error and lets an editor rename reach them. An alias cannot repeat a $group column, since both would come back under one name.

$group and $select are both optional. $select alone gives a grand total:

const [{ revenue }] = await pool.aggregate(Order, {
  $select: { revenue: { $sum: { amount: true } } },
});

$group alone returns each distinct value once, as SELECT DISTINCT does. aggregate() takes no $distinct of its own; for distinct rows of an entity, use it on a findMany:

const names = await pool.findMany(User, {
  $select: { name: true },
  $distinct: true,
});

Aggregate functions

$select entry	SQL	Type
{ $count: '*' }	COUNT(*)	number
{ $count: { field: true } }	COUNT("field")	number
{ $countDistinct: { field: true } }	COUNT(DISTINCT "field")	number
{ $sum: { field: true } }	SUM("field")	the column’s type, or null
{ $avg: { field: true } }	AVG("field")	number, or null
{ $min: { field: true } }	MIN("field")	the column’s type, or null
{ $max: { field: true } }	MAX("field")	the column’s type, or null

COUNT("field") counts non-null values only. $sum and $avg take numeric columns, and a $sum over a bigint column is a bigint. Over no rows, the counts return 0 and the rest return null, hence the | null. MongoDB’s $sum answers 0 there, so UQL also counts the values it summed (_uql_count_total in the pipeline above) and returns null when there were none, as SQL does. Only counting has a distinct variant.

Filtering and sorting groups

$where filters rows before grouping. $having filters the groups afterwards, by any result column, with the comparison operators that column’s type allows. $sort orders by a grouped column or a computed alias, and $skip and $limit page the groups.

Counting only some rows

A $where beside a function aggregates only the rows it matches, which pivots rows into columns in one query. Published posts, drafts and published points per author:

You write

const byAuthor = await pool.aggregate(Post, {
  $group: { authorId: true },
  $select: {
    published: { $count: '*', $where: { published: true } },
    drafts: { $count: '*', $where: { published: false } },
    publishedPoints: { $sum: { points: true }, $where: { published: true } },
  },
});

PostgreSQL

SELECT "authorId", COUNT("published") "published", COUNT("drafts") "drafts", SUM("publishedPoints") "publishedPoints"
FROM (
  SELECT "authorId",
    CASE WHEN "published" = $1 THEN 1 END "published",
    CASE WHEN "published" = $2 THEN 1 END "drafts",
    CASE WHEN "published" = $3 THEN "points" END "publishedPoints"
  FROM "Post"
) "_uql_rows"
GROUP BY "authorId"

The function’s $where takes the entity’s fields and relations, as a query’s does, so { $count: '*', $where: { tags: { $size: { $gte: 2 } } } } counts the posts with two tags or more; the query’s $where still applies first. On MongoDB it takes every operator but $text, which MongoDB runs only as a pipeline’s first stage.

Grouping by a relation’s field

A $group entry can reach a field on a to-one relation, at any depth, under an alias you choose. Posts per country of the author’s company:

You write

const byCountry = await pool.aggregate(Post, {
  $group: { country: { author: { company: { country: true } } } },
  $select: { posts: { $count: '*' }, points: { $sum: { points: true } } },
});
// [{ country: 'CO', posts: 12, points: 340 }, ...]

PostgreSQL

SELECT "author.company"."country" "country", COUNT(*) "posts", SUM("Post"."points") "points"
FROM "Post"
INNER JOIN "User" "author" ON "author"."id" = "Post"."authorId"
INNER JOIN "Company" "author.company" ON "author.company"."id" = "author"."companyId"
GROUP BY "author.company"."country"

Rows stay flat, keyed by the alias, which $having and $sort can use. Only to-one relations are allowed, since a to-many would multiply the totals. The join is inner, so a row whose relation is missing is left out; group by the foreign key ({ authorId: true }) to count it.

A relation aggregate, such as an order’s item count, groups and totals like any other field. UQL reads it in a subquery and groups the result, so an $avg over it averages the per-order values, not the items.

Next steps

• Querier API: find, select, sort and pagination.
• Logical Operators: compose the $where that runs before grouping.
• Counting: counting related rows from a find.