#Aggregations and joins

An aggregation pipeline turns documents into a result through a sequence of stages. Use it for reports, calculated fields, grouping, and joins between collections.

Silt compiles the whole pipeline to SQL. SQLite performs the matching, grouping, joins, and result assembly; source collections are not fetched into JavaScript to calculate the answer.

#Set up an example

The examples on this page share these two collections. The database is temporary so you can rerun the snippets without managing a file.

import { openDatabase } from '@silt-db/sqlite';

const db = await openDatabase(':memory:');
const customers = db.collection('customers');
const orders = db.collection('orders');

await customers.insertMany([
  { _id: 'alice', name: 'Alice', country: 'RO', minimumAmount: 30 },
  { _id: 'bob', name: 'Bob', country: 'UA', minimumAmount: 20 },
  { _id: 'carol', name: 'Carol', country: 'RO', minimumAmount: 50 },
]);

await orders.insertMany([
  { _id: 'o1', customerId: 'alice', amount: 25, paid: true },
  { _id: 'o2', customerId: 'alice', amount: 40, paid: true },
  { _id: 'o3', customerId: 'bob', amount: 30, paid: true },
  { _id: 'o4', customerId: 'carol', amount: 99, paid: false },
]);

#Group and calculate totals

Start with $match to restrict the input, then $group to combine documents sharing a key. In expressions, a string such as '$customerId' reads a field from the current document.

const totals = await orders
  .aggregate([
    { $match: { paid: true } },
    {
      $group: {
        _id: '$customerId',
        revenue: { $sum: '$amount' },
        orderCount: { $count: {} },
      },
    },
    { $sort: { revenue: -1, _id: 1 } },
  ])
  .toArray();

console.log(totals);
// [
//   { _id: 'alice', revenue: 65, orderCount: 2 },
//   { _id: 'bob', revenue: 30, orderCount: 1 }
// ]

The group's _id is the grouping key. Use _id: null for one group over the whole input. $sum, $avg, $min, and $max are common accumulators; the aggregation reference lists the implemented stages and operand restrictions.

Stage order matters. A $limit before $group limits input documents; a $limit after $group limits the resulting groups. Use an explicit $sort before order-sensitive operations such as selecting the first result.

#Join another collection with $lookup

This report joins each paid order to its customer, then calculates revenue by the customer's country.

const byCountry = await orders
  .aggregate([
    { $match: { paid: true } },
    {
      $lookup: {
        from: 'customers',
        localField: 'customerId',
        foreignField: '_id',
        as: 'customer',
      },
    },
    { $unwind: '$customer' },
    { $group: { _id: '$customer.country', revenue: { $sum: '$amount' } } },
    { $sort: { revenue: -1, _id: 1 } },
  ])
  .toArray();

console.log(byCountry);
// [{ _id: 'RO', revenue: 65 }, { _id: 'UA', revenue: 30 }]

$lookup adds an array named by as, even when there is only one match. No match produces an empty array. $unwind emits one document per array element; its default behavior removes documents with an empty joined array. To retain unmatched documents, use { $unwind: { path: '$customer', preserveNullAndEmptyArrays: true } }.

Silt supports equality joins, correlated pipeline joins, combined equality-plus-pipeline joins, and nested lookups. The join and its output array are evaluated inside SQLite.

#Filter each joined result

Use a foreign pipeline to shape joined data. Variables declared by let are read as $$variableName; $expr compares those values with fields in the foreign document.

The next query finds each customer's largest paid order above that customer's threshold. The $limit applies separately to each customer.

await orders.createIndex({ customerId: 1 });

const customerOrders = await customers
  .aggregate([
    { $sort: { _id: 1 } },
    {
      $lookup: {
        from: 'orders',
        localField: '_id',
        foreignField: 'customerId',
        let: { minimum: '$minimumAmount' },
        pipeline: [
          {
            $match: {
              paid: true,
              $expr: { $gte: ['$amount', '$$minimum'] },
            },
          },
          { $sort: { amount: -1, _id: 1 } },
          { $limit: 1 },
          { $project: { _id: 1, amount: 1 } },
        ],
        as: 'largestOrder',
      },
    },
    { $project: { _id: 1, largestOrder: 1 } },
  ])
  .toArray();

console.log(customerOrders);
// [
//   { _id: 'alice', largestOrder: [{ _id: 'o2', amount: 40 }] },
//   { _id: 'bob', largestOrder: [{ _id: 'o3', amount: 30 }] },
//   { _id: 'carol', largestOrder: [] }
// ]

The equality condition on customerId narrows the foreign input before the pipeline runs. The index gives eligible lookups a selective SQL access path. More general correlated comparisons can still require a foreign scan per input document; see indexes and inspect the actual plan when joins grow.

This example sorts the customers before joining because their IDs are already available. Some composed lookup pipelines become expensive when a sort follows the join; benchmark the complete pipeline, even if it returns only a few documents.

For a pipeline-only lookup, omit localField and foreignField, then express the relationship with let and $expr in the foreign pipeline. No JavaScript callback is involved in either form.

#Return several views with $facet

$facet runs named subpipelines over the same input and returns their results as arrays in one document.

const dashboard = await orders
  .aggregate([
    { $match: { paid: true } },
    {
      $facet: {
        summary: [{ $group: { _id: null, revenue: { $sum: '$amount' } } }],
        largest: [
          { $sort: { amount: -1, _id: 1 } },
          { $limit: 2 },
          { $project: { _id: 1, amount: 1 } },
        ],
      },
    },
  ])
  .toArray();

console.log(dashboard[0].summary); // [{ _id: null, revenue: 95 }]

Keep these arrays bounded when possible. Streaming the outer cursor cannot stream elements inside a single joined array or facet document; SQLite must construct that value before your application receives it.

#Inspect a pipeline

An aggregation cursor supports the same asynchronous iteration and .toArray() as a find cursor. explain() returns the generated SQL, parameters, and SQLite query plan without executing the result query.

const plan = await orders
  .aggregate([
    { $match: { customerId: 'alice' } },
    { $group: { _id: null, revenue: { $sum: '$amount' } } },
  ])
  .explain();

console.log(plan.sql);
console.log(plan.queryPlan);

await db.close();

Not every MongoDB stage is implemented. $graphLookup, $setWindowFields, $out, and $merge are among the current omissions. Exact ordering of nested arrays and objects can also be expensive. Check the aggregation reference and compatibility contract for the supported profile and performance limits.