---
title: Facets
description: Compute a Top K and aggregate in one query
canonical: https://www.paradedb.com/docs/reference/aggregates/facets
---
A common pattern in search is to query for both an aggregate and a set of search results. For example, "find the top 10
results, and also count the total number of results."
Instead of issuing two separate queries -- one for the search results, and another for the aggregate -- `pdb.agg` allows for
these results to be returned in a single "faceted" query. This can significantly improve read throughput, since issuing a single
query uses less CPU and disk I/O.
For example, this query returns the top 3 search results alongside the total number of results found.
```sql SQL
SELECT
id, description, rating,
pdb.agg('{"filter": "*"}') OVER ()
FROM mock_items
WHERE category === 'electronics'
ORDER BY rating DESC
LIMIT 3;
```
```ts Drizzle
import { and, desc } from "drizzle-orm";
import { search } from "@paradedb/drizzle-paradedb";
await db
.select({
id: mockItems.id,
description: mockItems.description,
rating: mockItems.rating,
agg: search.agg({ filter: "*" }).over(),
})
.from(mockItems)
.where(
and(
search.all(mockItems.id),
search.term(mockItems.category, "electronics"),
),
)
.orderBy(desc(mockItems.rating))
.limit(3);
```
```python Django
from django.db.models import Window
from paradedb import Agg, ParadeDB, Term
MockItem.objects.filter(
category=ParadeDB(Term('electronics'))
).values(
'id', 'description', 'rating'
).annotate(
agg=Window(expression=Agg('{"filter": "*"}'))
).order_by('-rating')[:3]
```
```python SQLAlchemy
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, search
base = (
select(MockItem.id, MockItem.description, MockItem.rating)
.where(
search.all(MockItem.id),
search.term(MockItem.category, "electronics"),
)
.order_by(MockItem.rating.desc())
.limit(3)
)
stmt = facets.with_rows(base, agg={"filter": "*"}, field=MockItem.id)
with Session(engine) as session:
rows = session.execute(stmt).all()
facets.extract(rows)
```
```ruby Rails
relation = MockItem.search(:category)
.term("electronics")
.with_agg(agg: { filter: "*" })
.select(:id, :description, :rating)
.order(rating: :desc)
.limit(3)
rows = relation.to_a
aggregates = relation.aggregates
```
```cs EF Core
await dbContext
.MockItems.Where(item => EF.Functions.Term(item.Category, "electronics"))
.OrderByDescending(item => item.Rating)
.Select(item => new
{
item.Id,
item.Description,
item.Rating,
Agg = EF.Functions.AggOver(new { filter = "*" })
})
.Take(3)
.ToListAsync();
```
```ini Expected Response
id | description | rating | agg
----+-----------------------------+--------+----------------
12 | Innovative wireless earbuds | 5 | {"doc_count": 5}
1 | Ergonomic metal keyboard | 4 | {"doc_count": 5}
2 | Plastic Keyboard | 4 | {"doc_count": 5}
(3 rows)
```
Faceted queries require that `pdb.agg` be used as a window function:
`pdb.agg() OVER ()` (or `pdb.combine_agg(pdb.agg(...)) OVER ()` in `GROUP BY`
queries).
## Facets with `GROUP BY`
The grouped facet below uses `value_count` because `pdb.combine_agg` runs on the aggregation path that does not support `filter` JSON aggregates. Since `id` is a non-null scalar field, its value count equals the row count, at the cost of reading its columnar data.
In a `GROUP BY` query, a bare `pdb.agg(...)` computes an aggregation per group. To compute a single facet across all matching rows alongside grouped results, wrap `pdb.agg(...)` in `pdb.combine_agg(...) OVER ()`:
```sql SQL
SELECT
in_stock,
COUNT(*) AS group_rows,
pdb.combine_agg(pdb.agg('{"value_count": {"field": "id"}}')) OVER () AS total
FROM mock_items
WHERE description ||| 'shoes'
GROUP BY in_stock
ORDER BY in_stock;
```
```ts Drizzle
import { sql } from "drizzle-orm";
import { search } from "@paradedb/drizzle-paradedb";
await db
.select({
inStock: mockItems.inStock,
groupRows: sql`count(*)::int`,
total: search.combineAgg({ value_count: { field: "id" } }),
})
.from(mockItems)
.where(search.matchAny(mockItems.description, "shoes"))
.groupBy(mockItems.inStock)
.orderBy(mockItems.inStock);
```
```python Django
from django.db.models import Count, F, Window
from paradedb import Agg, CombineAgg, MatchAny, ParadeDB
list(
MockItem.objects.filter(description=ParadeDB(MatchAny('shoes')))
.values(stock=F('in_stock'))
.annotate(
group_rows=Count('id'),
total=Window(CombineAgg(Agg('{"value_count": {"field": "id"}}'))),
)
.order_by('stock')
)
```
```python SQLAlchemy
from sqlalchemy import func, select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, pdb, search
stmt = (
select(
MockItem.in_stock,
func.count().label("group_rows"),
pdb.combine_agg(pdb.agg(facets.value_count(field="id"))).over().label("total"),
)
.where(search.match_any(MockItem.description, "shoes"))
.group_by(MockItem.in_stock)
.order_by(MockItem.in_stock)
)
with Session(engine) as session:
session.execute(stmt).all()
```
```ruby Rails
scope = MockItem.search(:description).match_any("shoes")
builder = scope.builder
total = builder.combine_agg(builder.agg('{"value_count":{"field":"id"}}')).over.as("total")
scope.select(:in_stock, Arel.sql("COUNT(*) AS group_rows"), total)
.group(:in_stock)
.order(:in_stock)
.to_a
```
```cs EF Core
await dbContext.MockItems
.Where(item => EF.Functions.MatchAny(item.Description, "shoes"))
.GroupBy(item => item.InStock)
.Select(group => new
{
InStock = group.Key,
GroupRows = group.Count(),
Total = EF.Functions.CombineAggOver(new { value_count = new { field = "id" } })
})
.OrderBy(row => row.InStock)
.ToListAsync();
```
Standard `OVER ()` window aggregates (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`) over group columns or group aggregates are pushed down alongside it. These cross-group facet helpers require ParadeDB 0.26.2 or newer.