--- 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.