docs/documentation/aggregates/overview.mdx
The pdb.agg function accepts an Elasticsearch-compatible JSON aggregate query string. It executes the aggregate using the
columnar 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'; ```from paradedb import Agg, ParadeDB, Term
MockItem.objects.filter(
category=ParadeDB(Term('electronics'))
).aggregate(agg=Agg('{"value_count": {"field": "id"}}'))
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()
MockItem.search(:category)
.term("electronics")
.facets_agg(agg: ParadeDB::Aggregations.value_count(:id))
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; ```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]
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()
MockItem.search(:category)
.term("electronics")
.aggregate_by(
:rating,
agg: ParadeDB::Aggregations.value_count(:id)
)
.order(:rating)
.limit(5)
rating | agg
--------+----------------
3 | {"value": 1.0}
4 | {"value": 3.0}
5 | {"value": 1.0}
(3 rows)
To compute multiple aggregations at once, simply include multiple pdb.agg functions in the target list:
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"}}'),
)
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()
MockItem.search(:category)
.term("electronics")
.facets_agg(
avg_rating: ParadeDB::Aggregations.avg(:rating),
count: ParadeDB::Aggregations.value_count(:id)
)
avg_rating | count
----------------+----------------
{"value": 4.0} | {"value": 5.0}
(1 row)
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.
from paradedb import Agg, Match, ParadeDB
MockItem.objects.filter(
description=ParadeDB(Match('running shoes', operator='OR'))
).aggregate(
agg=Agg('{"value_count": {"field": "id"}}', exact=False)
)
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()
MockItem.search(:description)
.matching_any("running shoes")
.facets_agg(exact: false, agg: ParadeDB::Aggregations.value_count(:id))
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>If metadata is a JSON field with key color, use metadata.color as the field name:
from paradedb import Agg, All, ParadeDB
MockItem.objects.filter(
id=ParadeDB(All())
).aggregate(agg=Agg('{"terms": {"field": "metadata.color"}}'))
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()
MockItem.search(:id)
.match_all
.facets_agg(agg: ParadeDB::Aggregations.terms("metadata.color"))