logowebsideproject

Aggregations

Nuxt Auto API provides powerful aggregation capabilities for analyzing your data, including simple aggregates on list endpoints and complex grouped aggregations.

Aggregations

Nuxt Auto API provides powerful aggregation capabilities for analyzing your data, including simple aggregates on list endpoints and complex grouped aggregations.

Simple Aggregations

Add aggregate functions to list requests using the aggregate query parameter:

// GET /api/orders?aggregate=count

{
  "data": [/* order records */],
  "meta": {
    "total": 150,
    "limit": 20,
    "page": 1,
    "aggregates": {
      "count": 150
    }
  }
}

Multiple Aggregates

Combine multiple aggregate functions:

// GET /api/orders?aggregate=count,sum(amount),avg(amount)

{
  "data": [/* order records */],
  "meta": {
    "aggregates": {
      "count": 150,
      "sum_amount": 45000.00,
      "avg_amount": 300.00
    }
  }
}

Available Functions

FunctionDescriptionExample
countCount recordsaggregate=count
sum(field)Sum of field valuesaggregate=sum(amount)
avg(field)Average of field valuesaggregate=avg(price)
min(field)Minimum valueaggregate=min(price)
max(field)Maximum valueaggregate=max(price)

Aggregates with Filters

Combine aggregations with filters:

// GET /api/orders?aggregate=sum(amount),count&filter[status]=completed

{
  "data": [/* completed orders */],
  "meta": {
    "aggregates": {
      "count": 100,
      "sum_amount": 30000.00
    }
  }
}

Complex Aggregations (Grouped)

Use the dedicated /aggregate endpoint for complex grouped aggregations:

// GET /api/orders/aggregate?aggregate=count,sum(amount)&groupBy=status

{
  "data": [
    {
      "group": { "status": "pending" },
      "count": 50,
      "sum_amount": 15000.00
    },
    {
      "group": { "status": "completed" },
      "count": 100,
      "sum_amount": 30000.00
    }
  ],
  "meta": {
    "total": 2
  }
}

Multiple Group By Fields

// GET /api/orders/aggregate?aggregate=count,sum(amount)&groupBy=status,paymentMethod

{
  "data": [
    {
      "group": {
        "status": "completed",
        "paymentMethod": "credit_card"
      },
      "count": 60,
      "sum_amount": 18000.00
    },
    {
      "group": {
        "status": "completed",
        "paymentMethod": "paypal"
      },
      "count": 40,
      "sum_amount": 12000.00
    }
  ]
}

Having Clause

Filter groups using the having parameter:

// GET /api/orders/aggregate?aggregate=count,sum(amount)&groupBy=userId&having={"count":{"$gt":5}}

{
  "data": [
    {
      "group": { "userId": 1 },
      "count": 10,
      "sum_amount": 3000.00
    },
    {
      "group": { "userId": 2 },
      "count": 7,
      "sum_amount": 2100.00
    }
    // Only groups with count > 5
  ]
}

Having Operators

OperatorDescriptionExample
$gtGreater than{"count":{"$gt":10}}
$gteGreater than or equal{"sum_amount":{"$gte":1000}}
$ltLess than{"avg_price":{"$lt":50}}
$lteLess than or equal{"count":{"$lte":5}}
$eqEqual{"count":{"$eq":0}}
$neNot equal{"count":{"$ne":0}}

Filters with Aggregations

Apply filters before aggregation using the filter parameter:

// GET /api/orders/aggregate?aggregate=count,sum(amount)&groupBy=status&filter[createdAt][$gte]=2024-01-01

{
  "data": [
    {
      "group": { "status": "completed" },
      "count": 50,
      "sum_amount": 15000.00
    }
    // Only orders created after 2024-01-01
  ]
}

Frontend Usage

With Composables

// Simple aggregations on list
const { data } = await useAutoApiFetch('orders', {
  query: {
    aggregate: 'count,sum(amount),avg(amount)',
    filter: {
      status: 'completed'
    }
  }
})

console.log(data.value.meta.aggregates)
// { count: 100, sum_amount: 30000, avg_amount: 300 }

// Complex grouped aggregations
const { data } = await $fetch('/api/orders/aggregate', {
  query: {
    aggregate: 'count,sum(amount)',
    groupBy: 'status',
    having: {
      count: { $gt: 10 }
    }
  }
})

console.log(data.data)
// [{ group: { status: 'completed' }, count: 100, sum_amount: 30000 }]

Real-World Examples

Sales Dashboard

// Total sales by month
const salesByMonth = await $fetch('/api/orders/aggregate', {
  query: {
    aggregate: 'sum(amount),count',
    groupBy: 'monthYear',  // Assumes computed field
    filter: {
      createdAt: {
        $gte: '2024-01-01'
      }
    }
  }
})

User Activity Report

// Active users by number of orders
const activeUsers = await $fetch('/api/orders/aggregate', {
  query: {
    aggregate: 'count',
    groupBy: 'userId',
    having: {
      count: { $gte: 5 }
    }
  }
})

Product Performance

// Best-selling products
const topProducts = await $fetch('/api/orderItems/aggregate', {
  query: {
    aggregate: 'sum(quantity),sum(total)',
    groupBy: 'productId',
    filter: {
      createdAt: {
        $gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000).toISOString()
      }
    }
  }
})

Revenue by Payment Method

const revenueByPayment = await $fetch('/api/orders/aggregate', {
  query: {
    aggregate: 'sum(amount),count,avg(amount)',
    groupBy: 'paymentMethod',
    filter: {
      status: 'completed'
    }
  }
})

Configuration

export default defineNuxtConfig({
  autoApi: {
    aggregations: {
      // Enable/disable aggregations (default: true)
      enabled: true,

      // Allow groupBy (default: true)
      allowGroupBy: true,

      // Maximum number of groupBy fields (default: 5)
      maxGroupByFields: 5
    }
  }
})

Authorization

Aggregations require read permission on the resource:

// User must have permission to read orders
GET /api/orders/aggregate?aggregate=count,sum(amount)&groupBy=status

Collection-level authorization applies (not object-level):

  • User needs permission to query the resource
  • Individual records are not checked (aggregates are anonymous)

Performance Considerations

Database Indexes

Create indexes on fields used in:

  • groupBy clauses
  • filter clauses
  • Aggregate functions (for some databases)
-- Index for grouping
CREATE INDEX idx_orders_status ON orders(status);

-- Index for filtering
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Composite index for group + filter
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

Query Optimization

Aggregations can be slow on large tables:

// Good: Filter before aggregating
GET /api/orders/aggregate?
  aggregate=count,sum(amount)&
  groupBy=status&
  filter[createdAt][$gte]=2024-01-01

// Less efficient: Aggregate entire table
GET /api/orders/aggregate?aggregate=count,sum(amount)&groupBy=status

Limit Group Count

Avoid grouping by high-cardinality fields:

// Bad: Too many groups (one per user)
GET /api/orders/aggregate?aggregate=count&groupBy=userId

// Better: Limit with having clause
GET /api/orders/aggregate?
  aggregate=count&
  groupBy=userId&
  having={"count":{"$gte":10}}

Caching

Cache aggregation results for expensive queries:

// Frontend caching with TanStack Query
const { data } = useQuery({
  queryKey: ['orders', 'aggregate', 'by-status'],
  queryFn: () => $fetch('/api/orders/aggregate', {
    query: {
      aggregate: 'count,sum(amount)',
      groupBy: 'status'
    }
  }),
  staleTime: 5 * 60 * 1000,  // 5 minutes
})

Server-side caching:

// server/api/reports/sales-summary.ts
export default defineEventHandler(async (event) => {
  // Cache expensive aggregation
  return await useStorage('cache').getItem('sales-summary', async () => {
    return await $fetch('/api/orders/aggregate', {
      query: {
        aggregate: 'sum(amount),count',
        groupBy: 'status'
      }
    })
  })
})

Common Patterns

Dashboard Stats

// Single request for multiple stats
const stats = await Promise.all([
  // Total revenue
  $fetch('/api/orders', {
    query: { aggregate: 'sum(amount)' }
  }),

  // Orders by status
  $fetch('/api/orders/aggregate', {
    query: {
      aggregate: 'count',
      groupBy: 'status'
    }
  }),

  // Top customers
  $fetch('/api/orders/aggregate', {
    query: {
      aggregate: 'sum(amount),count',
      groupBy: 'customerId',
      having: { count: { $gte: 5 } }
    }
  })
])

Time-Series Data

// Group by date (requires date extraction in SQL)
const dailySales = await $fetch('/api/orders/aggregate', {
  query: {
    aggregate: 'sum(amount),count',
    groupBy: 'date',  // DATE(created_at)
    filter: {
      createdAt: {
        $gte: '2024-01-01',
        $lt: '2024-02-01'
      }
    }
  }
})

Conversion Metrics

// Calculate conversion rates
const funnelStats = await $fetch('/api/leads/aggregate', {
  query: {
    aggregate: 'count',
    groupBy: 'stage'
  }
})

const conversionRate = {
  leadToQualified: funnelStats.data.find(s => s.group.stage === 'qualified').count /
                   funnelStats.data.find(s => s.group.stage === 'lead').count,
  qualifiedToCustomer: funnelStats.data.find(s => s.group.stage === 'customer').count /
                       funnelStats.data.find(s => s.group.stage === 'qualified').count
}

Limitations

  1. No nested aggregations: Can't aggregate on computed aggregates
  2. Limited having operators: Only basic comparison operators
  3. No percentile functions: Use custom SQL for advanced statistics
  4. Group limit: Maximum 5 group by fields (configurable)

For complex analytics beyond these limitations, consider:

  • Custom API endpoints with raw SQL
  • Analytics tools (Metabase, Looker)
  • Data warehouse solutions

Error Handling

// Invalid aggregate function
GET /api/orders?aggregate=invalid(amount)
// Response: Warning logged, aggregate ignored

// Unknown field
GET /api/orders?aggregate=sum(nonexistent)
// Response: Warning logged, aggregate ignored

// Too many groupBy fields
GET /api/orders/aggregate?groupBy=a,b,c,d,e,f
// Response: 400 - "Group by limited to 5 fields"

// Aggregations disabled
GET /api/orders?aggregate=count
// Response: 403 - "Aggregations are disabled"

TypeScript Support

interface OrderAggregates {
  count: number
  sum_amount: number
  avg_amount: number
}

const { data } = await useAutoApiFetch('orders', {
  query: {
    aggregate: 'count,sum(amount),avg(amount)'
  }
})

const aggregates = data.value.meta.aggregates as OrderAggregates

Best Practices

  1. Index fields: Always index fields used in groupBy and filters
  2. Filter first: Apply filters to reduce dataset before aggregating
  3. Use having wisely: Filter groups to reduce result set size
  4. Cache results: Cache expensive aggregations with appropriate TTL
  5. Monitor performance: Track slow aggregation queries
  6. Limit groups: Avoid high-cardinality groupBy fields
  7. Use simple aggregates: Prefer simple aggregates on list endpoint for basic stats
  8. Dedicate endpoint: Use /aggregate endpoint for complex grouped queries

Getting Started

Nuxt Auto API automatically generates type-safe REST APIs from your Drizzle ORM schemas with built-in authorization, validation, and multi-tenancy support.

Lifecycle Hooks

Lifecycle hooks allow you to execute custom logic before and after CRUD operations. Perfect for audit logging, notifications, data transformation, and business logic.

Need a Landing Page?

Modern landing pages with optional modules (blog, docs, forms, i18n). Let's discuss your project.

Build Your MVP

Full-stack SaaS development. Expert in database design, multi-tenancy, and scalable architecture.

Deployment Help

Dockerize your backend, set up CI/CD pipelines, deploy to Cloudflare or Hetzner. Early-stage setup.

Suggest a SaaS Tool

Missing a calculator or tool? Suggest what you'd like to see on our site.