---
title: Aggregate Syntax
description: Accelerate aggregates with the ParadeDB index
canonical: https://docs.paradedb.com/documentation/aggregates/overview
---

The `pdb.agg` function accepts an Elasticsearch-compatible JSON aggregate query string. It executes the aggregate using the
[columnar](/welcome/architecture#columnar-index) portion of the ParadeDB index, which can significantly accelerate performance compared to vanilla Postgres.

For example, the following query counts the total number of results for a search query.

<CodeGroup>
```sql SQL
SELECT pdb.agg('{"value_count": {"field": "id"}}')
FROM mock_items
WHERE category === 'electronics';
```

```ts Drizzle
import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    agg: search.agg({ value_count: { field: "id" } }),
  })
  .from(mockItems)
  .where(search.term(mockItems.category, "electronics"));
```

```python Django
from paradedb import Agg, ParadeDB, Term

MockItem.objects.filter(
    category=ParadeDB(Term('electronics'))
).aggregate(agg=Agg('{"value_count": {"field": "id"}}'))
```

```python SQLAlchemy
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, pdb, search

stmt = (
    select(pdb.agg(facets.value_count(field="id")))
    .select_from(MockItem)
    .where(search.term(MockItem.category, "electronics"))
)

with Session(engine) as session:
    session.execute(stmt).all()
```

```ruby Rails
MockItem.search(:category)
        .term("electronics")
        .facets_agg(agg: ParadeDB::Aggregations.value_count(:id))
```

```cs EF Core
await dbContext
    .MockItems.Where(item => EF.Functions.Term(item.Category, "electronics"))
    .Select(item => EF.Functions.Agg(new { value_count = new { field = "id" } }))
    .ToListAsync();
```

</CodeGroup>

```ini Expected Response
      agg
----------------
 {"value": 5.0}
(1 row)
```

This query counts the number of results for every distinct group:

<CodeGroup>
```sql SQL
SELECT rating, pdb.agg('{"value_count": {"field": "id"}}')
FROM mock_items
WHERE category === 'electronics'
GROUP BY rating
ORDER BY rating
LIMIT 5;
```

```ts Drizzle
import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    rating: mockItems.rating,
    agg: search.agg({ value_count: { field: "id" } }),
  })
  .from(mockItems)
  .where(search.term(mockItems.category, "electronics"))
  .groupBy(mockItems.rating)
  .orderBy(mockItems.rating)
  .limit(5);
```

```python Django
from paradedb import Agg, ParadeDB, Term

MockItem.objects.filter(
    category=ParadeDB(Term('electronics'))
).values('rating').annotate(
    agg=Agg('{"value_count": {"field": "id"}}')
).order_by('rating')[:5]
```

```python SQLAlchemy
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, pdb, search

stmt = (
    select(MockItem.rating, pdb.agg(facets.value_count(field="id")).label("agg"))
    .where(search.term(MockItem.category, "electronics"))
    .group_by(MockItem.rating)
    .order_by(MockItem.rating)
    .limit(5)
)

with Session(engine) as session:
    session.execute(stmt).all()
```

```ruby Rails
MockItem.search(:category)
        .term("electronics")
        .aggregate_by(
          :rating,
          agg: ParadeDB::Aggregations.value_count(:id)
        )
        .order(:rating)
        .limit(5)
```

```cs EF Core
await dbContext
    .MockItems.Where(item => EF.Functions.Term(item.Category, "electronics"))
    .GroupBy(item => item.Rating)
    .Select(group => new
    {
        Rating = group.Key,
        Agg = EF.Functions.Agg(new { value_count = new { field = "id" } })
    })
    .OrderBy(result => result.Rating)
    .Take(5)
    .ToListAsync();
```

</CodeGroup>

```ini Expected Response
 rating |      agg
--------+----------------
      3 | {"value": 1.0}
      4 | {"value": 3.0}
      5 | {"value": 1.0}
(3 rows)
```

## Multiple Aggregations

To compute multiple aggregations at once, simply include multiple `pdb.agg` functions in the target list:

<CodeGroup>
```sql SQL
SELECT
  pdb.agg('{"avg": {"field": "rating"}}') AS avg_rating,
  pdb.agg('{"value_count": {"field": "id"}}') AS count
FROM mock_items
WHERE category === 'electronics';
```

```ts Drizzle
import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    avgRating: search.agg({ avg: { field: "rating" } }),
    count: search.agg({ value_count: { field: "id" } }),
  })
  .from(mockItems)
  .where(search.term(mockItems.category, "electronics"));
```

```python Django
from paradedb import Agg, ParadeDB, Term

MockItem.objects.filter(
    category=ParadeDB(Term('electronics'))
).aggregate(
    avg_rating=Agg('{"avg": {"field": "rating"}}'),
    count=Agg('{"value_count": {"field": "id"}}'),
)
```

```python SQLAlchemy
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, pdb, search

stmt = (
    select(
        pdb.agg(facets.avg(field="rating")).label("avg_rating"),
        pdb.agg(facets.value_count(field="id")).label("count"),
    )
    .select_from(MockItem)
    .where(search.term(MockItem.category, "electronics"))
)

with Session(engine) as session:
    session.execute(stmt).all()
```

```ruby Rails
MockItem.search(:category)
        .term("electronics")
        .facets_agg(
          avg_rating: ParadeDB::Aggregations.avg(:rating),
          count: ParadeDB::Aggregations.value_count(:id)
        )
```

```cs EF Core
await dbContext
    .MockItems.Where(item => EF.Functions.Term(item.Category, "electronics"))
    .Select(item => new
    {
        AvgRating = EF.Functions.Agg(new { avg = new { field = "rating" } }),
        Count = EF.Functions.Agg(new { value_count = new { field = "id" } })
    })
    .ToListAsync();
```

</CodeGroup>

```ini Expected Response
   avg_rating   |     count
----------------+----------------
 {"value": 4.0} | {"value": 5.0}
(1 row)
```

## Performance Optimization

On every query, ParadeDB runs checks to ensure that deleted or updated-away rows are not factored into the result set.

If your table is not frequently updated or you can tolerate an approximate result, the performance of aggregate queries can be improved by disabling these visibility checks.
To do so, set the second argument of `pdb.agg` to `false`.

<CodeGroup>
```sql SQL
SELECT pdb.agg('{"value_count": {"field": "id"}}', false)
FROM mock_items
WHERE description ||| 'running shoes';
```

```ts Drizzle
import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    agg: search.agg({ value_count: { field: "id" } }, false),
  })
  .from(mockItems)
  .where(search.matchAny(mockItems.description, "running shoes"));
```

```python Django
from paradedb import Agg, MatchAny, ParadeDB

MockItem.objects.filter(
    description=ParadeDB(MatchAny('running shoes'))
).aggregate(
    agg=Agg('{"value_count": {"field": "id"}}', exact=False)
)
```

```python SQLAlchemy
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, pdb, search

stmt = (
    select(pdb.agg(facets.value_count(field="id"), approximate=True).label("agg"))
    .where(search.match_any(MockItem.description, "running shoes"))
)

with Session(engine) as session:
    session.execute(stmt).all()
```

```ruby Rails
MockItem.search(:description)
        .matching_any("running shoes")
        .facets_agg(exact: false, agg: ParadeDB::Aggregations.value_count(:id))
```

```cs EF Core
await dbContext
    .MockItems.Where(item => EF.Functions.MatchAny(item.Description, "running shoes"))
    .Select(item => EF.Functions.Agg(new { value_count = new { field = "id" } }, false))
    .ToListAsync();
```

</CodeGroup>

Disabling this check can improve query times by 2-4x in some cases (at the expense of correctness).

<Note>
If a single query contains multiple `pdb.agg` calls, all of them must use the same visibility setting (either all `true` or all `false`).
</Note>

## JSON Fields

If `metadata` is a JSON field with key `color`, use `metadata.color` as the field name:

<CodeGroup>
```sql SQL
SELECT pdb.agg('{"terms": {"field": "metadata.color"}}')
FROM mock_items
WHERE id @@@ pdb.all();
```

```ts Drizzle
import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    agg: search.agg({ terms: { field: "metadata.color" } }),
  })
  .from(mockItems)
  .where(search.all(mockItems.id));
```

```python Django
from paradedb import Agg, All, ParadeDB

MockItem.objects.filter(
    id=ParadeDB(All())
).aggregate(agg=Agg('{"terms": {"field": "metadata.color"}}'))
```

```python SQLAlchemy
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, pdb, search

stmt = (
    select(pdb.agg(facets.terms(field="metadata.color")))
    .select_from(MockItem)
    .where(search.all(MockItem.id))
)

with Session(engine) as session:
    session.execute(stmt).all()
```

```ruby Rails
MockItem.search(:id)
        .match_all
        .facets_agg(agg: ParadeDB::Aggregations.terms("metadata.color"))
```

```cs EF Core
await dbContext
    .MockItems.Where(item => EF.Functions.All(item.Id))
    .Select(item => EF.Functions.Agg(new { terms = new { field = "metadata.color" } }))
    .ToListAsync();
```

</CodeGroup>

<Note>
If a text or JSON field is used inside `pdb.agg`, it must use the [literal](/documentation/tokenizers/available-tokenizers/literal) or
[literal normalized](/documentation/tokenizers/available-tokenizers/literal-normalized) tokenizer.
</Note>
