> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ondb.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Materialized Views

> Pre-computed views with JOINs and aggregations for instant query performance

Create pre-computed views with JOINs and aggregations for instant query performance. Data is pre-computed and updates automatically when source data changes.

## Creating Views

### Simple View

```typescript theme={null}
// Get database manager
const db = client.database('your-app-id');

// Create a materialized view with SDK
await db.createView('topSellers', ['products', 'orders'], {
  select: ['id', 'name', 'price', 'salesCount'],
  where: { status: 'active' },
  orderBy: { salesCount: 'desc' },
  limit: 100
});
```

### View with JOINs

```typescript theme={null}
// Build complex view with JOINs using query builder
const viewQuery = client.queryBuilder()
  .collection('user_top_tracks')
  .joinOne('user', 'users')
    .onField('user_id').equals('$data.user_id')
    .selectFields(['country'])
    .build()
  .orderBy('playcount')
  .selectAll()
  .limit(10000)
  .getQueryRequest();

await db.createView(
  'top_tracks_with_countries',
  ['user_top_tracks', 'users'],
  viewQuery
);
```

## Aggregated Views

Create views with GROUP BY for dashboard analytics:

```typescript theme={null}
// View with aggregation
const aggregatedView = {
  name: 'plays_by_country',
  source_collections: ['top_tracks_with_countries'],
  query: {
    find: {},
    select: {},
    group_by: ['country'],
    aggregate: {
      total_plays: { '$sum': 'playcount' },
      unique_tracks: { '$countDistinct': 'track_name' },
      unique_artists: { '$countDistinct': 'artist_name' }
    },
    sort_by: ['total_plays'],
    limit: 100
  }
};

// Create via API
await db.createView(
  aggregatedView.name,
  aggregatedView.source_collections,
  aggregatedView.query
);
```

## Managing Views

```typescript theme={null}
const db = client.database('your-app-id');

// List all views
const views = await db.listViews();

// Get specific view
const view = await db.getView('topSellers');

// Refresh view data
await db.refreshView('topSellers');

// Delete view
await db.deleteView('topSellers');
```

### SQL-Based Views

Create views using SQL syntax with configurable refresh modes:

```typescript theme={null}
const db = client.database('your-app-id');

// Create a live view (refreshes automatically)
await db.createViewSql(
  'CREATE VIEW active_users AS SELECT id, name, email FROM your-app-id::users WHERE active = true',
  'live'
);

// Create a lazy view (refresh manually)
await db.createViewSql(
  'CREATE VIEW monthly_summary AS SELECT status, COUNT(*) as total FROM your-app-id::orders GROUP BY status',
  'lazy'
);

// Query a view
const results = await db.queryView('active_users');

// Count view records
const count = await db.countView('active_users');

// Refresh a lazy view manually
await db.refreshView('monthly_summary');
```

## Querying Views

Query materialized views like regular collections - data is pre-computed and instant:

```typescript theme={null}
// Query the view (data is pre-computed)
const results = await client.queryBuilder()
  .collection('top_tracks_with_countries')
  .selectAll()
  .limit(1000)
  .execute();

// View results already include JOINed data
results.records.forEach(record => {
  console.log(`Track: ${record.track_name}`);
  console.log(`Country: ${record.user.country}`);
  console.log(`Plays: ${record.playcount}`);
});
```

## Aggregation Operators

| Operator         | Description                       |
| ---------------- | --------------------------------- |
| `$sum`           | Sum values across grouped records |
| `$avg`           | Average values                    |
| `$count`         | Count records in group            |
| `$countDistinct` | Count unique values               |
| `$min`           | Minimum value in group            |
| `$max`           | Maximum value in group            |

## Use Cases

### Analytics Dashboard

```typescript theme={null}
// Create a view for dashboard metrics
await db.createView('daily_metrics', ['orders'], {
  find: {},
  select: {},
  group_by: ['date'],
  aggregate: {
    total_orders: { '$count': '*' },
    total_revenue: { '$sum': 'amount' },
    avg_order_value: { '$avg': 'amount' },
    unique_customers: { '$countDistinct': 'customer_id' }
  },
  sort_by: ['date'],
  limit: 365
});

// Query instantly
const metrics = await db.queryView('daily_metrics', {
  limit: 30 // Last 30 days
});
```

### Leaderboard

```typescript theme={null}
// Create leaderboard view
await db.createView('user_leaderboard', ['scores'], {
  find: {},
  select: {},
  group_by: ['user_id'],
  aggregate: {
    total_score: { '$sum': 'points' },
    games_played: { '$count': '*' },
    highest_score: { '$max': 'points' }
  },
  sort_by: ['total_score'],
  limit: 100
});
```

### Denormalized Feed

```typescript theme={null}
// Create a denormalized feed with user info
const feedQuery = client.queryBuilder()
  .collection('posts')
  .joinOne('author', 'users')
    .onField('id').equals('$data.author_id')
    .selectFields(['name', 'avatar', 'verified'])
    .build()
  .joinMany('recent_comments', 'comments')
    .onField('post_id').equals('$data.id')
    .selectFields(['id', 'content', 'author_name'])
    .build()
  .orderBy('created_at', 'DESC')
  .selectAll()
  .limit(1000)
  .getQueryRequest();

await db.createView('feed', ['posts', 'users', 'comments'], feedQuery);
```

## Performance Benefits

| Aspect        | Regular Query         | Materialized View |
| ------------- | --------------------- | ----------------- |
| Complex JOINs | Computed per request  | Pre-computed      |
| Aggregations  | Full table scan       | Pre-calculated    |
| Response time | Variable              | Instant           |
| Cost          | Per-query computation | Storage only      |

## When to Use Views

**Use materialized views when:**

* Queries are complex (multiple JOINs, aggregations)
* Data is queried frequently
* Real-time freshness is not critical
* Dashboard or analytics scenarios

**Use regular queries when:**

* Data must be real-time fresh
* Queries are simple
* Data changes frequently and views would need constant refresh

## Next Steps

<CardGroup cols={2}>
  <Card title="CRUD Operations" icon="database" href="/crud/overview">
    Document operations
  </Card>

  <Card title="Query Builder" icon="magnifying-glass" href="/querying/query-builder">
    Build complex queries
  </Card>
</CardGroup>