# Aggregation

Source: https://developers.swell.is/backend-api/aggregation

Swell offers native support for the MongoDB Aggregation Pipeline; a framework for data aggregation allowing you to summarize data for reporting and other purposes.

### Aggregating with the group parameter

The simplest way to aggregate is the `group` parameter, accepted by every list endpoint. Combine it with `where` to filter records first. Each key of `group` either computes a value with an accumulator operator, buckets results with a date operator, or—when set to `true`—groups results by that field. Field names are prefixed with `$` automatically.

- Accumulators: `$sum`, `$avg`, `$min`, `$max`, `$first`, `$last`, `$push`, `$addToSet`
- Date grouping: `$year`, `$month`, `$dayOfMonth`, `$dayOfWeek`, `$dayOfYear`, `$hour`, `$minute`, `$second`, `$millisecond`

Using only accumulators returns a single result object inside `results`:

```javascript
// Total revenue and order count for paid orders
await swell.get('/orders', {
  where: { paid: true },
  group: {
    orders: { $sum: 1 },
    revenue: { $sum: 'grand_total' },
    avg_order: { $avg: 'grand_total' }
  }
});
```

```json
{
  "results": [
    {
      "orders": 167,
      "revenue": 18962.75,
      "avg_order": 113.55
    }
  ]
}
```

Adding a date operator or a field key groups the results—the response then includes `count`, the number of groups returned. For example, monthly revenue:

```javascript
// Revenue by month
await swell.get('/orders', {
  where: { paid: true },
  group: {
    orders: { $sum: 1 },
    revenue: { $sum: 'grand_total' },
    month: { $month: 'date_created' },
    year: { $year: 'date_created' }
  }
});

// Order count and total per customer
await swell.get('/orders', {
  group: {
    account_id: true,
    orders: { $sum: 1 },
    total: { $sum: 'grand_total' }
  }
});
```

```json
{
  "count": 3,
  "results": [
    { "orders": 74, "revenue": 8214.50, "month": 1, "year": 2025 },
    { "orders": 92, "revenue": 10467.25, "month": 2, "year": 2025 },
    { "orders": 88, "revenue": 9806.00, "month": 3, "year": 2025 }
  ]
}
```

### Custom pipelines with the aggregate parameter

For anything beyond grouping—multi-stage pipelines, `$unwind`, `$project`, and so on—use the `aggregate` parameter to construct a pipeline according to this Mongo reference. See the example alongside this article. A few behaviors to know:

- Passing an array replaces the entire pipeline—`where` filters are ignored, so include your own `$match` stage.
- Passing an object of stages appends them after the `$match` stage generated from `where`.
- Inside a pipeline, use MongoDB syntax directly, including `$`-prefixed field references such as `$grand_total`—unlike the `group` parameter, nothing is prefixed for you.
- The response contains `count` and `results`, where `count` is the number of aggregated rows. Keys grouped under `_id` are flattened onto each result.
- Inside a pipeline's `$match`, `id` is rewritten to `_id` and valid ID strings are converted to object IDs automatically.
- Pagination is not applied to aggregations—`limit` and `page` are ignored, so cap and order results with your own `$sort` and `$limit` stages.
- The `fields`, `expand`, and `include` parameters don't apply to aggregate results—use a `$project` stage to shape output. Each `$lookup` stage adds to the request's rate-limit weight.

### Common recipes

The following pipelines answer common reporting questions using order data. Each includes its own `$match` stage, and uses `$sort` and `$limit` to keep results manageable.

Best-selling products—unwind order line items and group by product to find which products have sold the most units:

```javascript
// Best-selling products by quantity
await swell.get('/orders', {
  aggregate: [
    { $match: { paid: true, canceled: { $ne: true } } },
    { $unwind: '$items' },
    {
      $group: {
        _id: { product_id: '$items.product_id' },
        product_name: { $first: '$items.product_name' },
        quantity_sold: { $sum: '$items.quantity' }
      }
    },
    { $sort: { quantity_sold: -1 } },
    { $limit: 3 }
  ]
});
```

```json
{
  "count": 3,
  "results": [
    {
      "product_id": "5c15505ad2f52a4f6f2fd5f2",
      "product_name": "Organic Cotton Tee",
      "quantity_sold": 482
    },
    {
      "product_id": "5c3a80f7b281a12e6a3c8d91",
      "product_name": "Canvas Tote Bag",
      "quantity_sold": 317
    },
    {
      "product_id": "5d9e12c4a7b3f05e8c1d2e73",
      "product_name": "Enamel Camp Mug",
      "quantity_sold": 264
    }
  ]
}
```

Revenue by shipping country—group paid orders by the shipping address country:

```javascript
// Revenue by shipping country
await swell.get('/orders', {
  aggregate: [
    { $match: { paid: true, canceled: { $ne: true } } },
    {
      $group: {
        _id: { country: '$shipping.country' },
        revenue: { $sum: '$grand_total' },
        orders: { $sum: 1 }
      }
    },
    { $sort: { revenue: -1 } }
  ]
});
```

```json
{
  "count": 3,
  "results": [
    { "country": "US", "revenue": 148230.75, "orders": 1642 },
    { "country": "CA", "revenue": 32410.50, "orders": 388 },
    { "country": "GB", "revenue": 18754.20, "orders": 205 }
  ]
}
```

Top customers by lifetime value—sum every paid order per account:

```javascript
// Top customers by lifetime value
await swell.get('/orders', {
  aggregate: [
    { $match: { paid: true, canceled: { $ne: true } } },
    {
      $group: {
        _id: { account_id: '$account_id' },
        lifetime_value: { $sum: '$grand_total' },
        order_count: { $sum: 1 }
      }
    },
    { $sort: { lifetime_value: -1 } },
    { $limit: 3 }
  ]
});
```

```json
{
  "count": 3,
  "results": [
    {
      "account_id": "5b4e6a2fd3c1a94b2e7f8a10",
      "lifetime_value": 4821.40,
      "order_count": 27
    },
    {
      "account_id": "5c92d1e8b47f302a1d6c4b55",
      "lifetime_value": 3956.15,
      "order_count": 19
    },
    {
      "account_id": "5e07f3a9c25d816e4b9a2c38",
      "lifetime_value": 3110.00,
      "order_count": 22
    }
  ]
}
```

Average order value by coupon code—measure how each promotion affects basket size:

```javascript
// Average order value by coupon code
await swell.get('/orders', {
  aggregate: [
    { $match: { paid: true, coupon_code: { $ne: null } } },
    {
      $group: {
        _id: { coupon_code: '$coupon_code' },
        avg_order_value: { $avg: '$grand_total' },
        orders: { $sum: 1 }
      }
    },
    { $sort: { avg_order_value: -1 } }
  ]
});
```

```json
{
  "count": 3,
  "results": [
    { "coupon_code": "VIP20", "avg_order_value": 112.40, "orders": 96 },
    { "coupon_code": "SUMMER10", "avg_order_value": 84.75, "orders": 241 },
    { "coupon_code": "WELCOME5", "avg_order_value": 52.30, "orders": 418 }
  ]
}
```

### Shortcut endpoints

For common aggregate results, every collection also supports the shortcut endpoints `/:count`, `/:first`, `/:last`, and `/:group` — see the [Advanced queries guide](https://developers.swell.is/guides/advanced-queries) for worked examples.

Unlike the `group` parameter, the `/:group` endpoint returns the aggregation result directly, without the `count` and `results` envelope.

## Example request

**Example aggregation query**

**cURL**

```bash
$ curl https://api.swell.store/orders?\
aggregate[0][$match][$and][0][date_created][$gte]=2025-01-01T00:00:00Z&\
aggregate[0][$match][$and][1][date_created][$lte]=2026-01-01T00:00:00Z&\
aggregate[1][$group][_id][day][$dayOfMonth]=$date_created&\
aggregate[1][$group][_id][month][$month]=$date_created&\
aggregate[1][$group][_id][year][$year]=$date_created&\
aggregate[1][$group][count][$sum]=1&\
aggregate[1][$group][sub_total][$sum]=$sub_total&\
aggregate[1][$group][avg_total][$avg]=$sub_total \
-u store-id:secret-key
```

**Node**

```javascript
const { swell } = require('swell-node');
swell.init('store-id', 'secret-key');

// Daily order totals for 2025
await swell.get('/orders', {
  aggregate: [
    {
      $match: {
        $and: [
          { date_created: { $gte: '2025-01-01T00:00:00Z' } },
          { date_created: { $lte: '2026-01-01T00:00:00Z' } }
        ]
      }
    },
    {
      $group: {
        _id: {
          day: { $dayOfMonth: '$date_created' },
          month: { $month: '$date_created' },
          year: { $year: '$date_created' }
        },
        count: { $sum: 1 },
        sub_total: { $sum: '$sub_total' },
        avg_total: { $avg: '$sub_total' }
      }
    }
  ]
});
```

**PHP**

```php
<?php
$swell = new \Swell\Client('store-id', 'secret-key');

$query = [
    'aggregate' => [
        [
            '$match' => [
                '$and' => [
                    ['date_created' => ['$gte' => '2025-01-01T00:00:00Z']],
                    ['date_created' => ['$lte' => '2026-01-01T00:00:00Z']]
                ]
            ]
        ],
        [
            '$group' => [
                '_id' => [
                    'day' => ['$dayOfMonth' => '$date_created'],
                    'month' => ['$month' => '$date_created'],
                    'year' => ['$year' => '$date_created']
                ],
                'count' => ['$sum' => 1],
                'sub_total' => ['$sum' => '$sub_total'],
                'avg_total' => ['$avg' => '$sub_total']
            ]
        ]
    ]
];

$response = $swell->get('/orders', $query);
?>
```