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.

SELECT
  id, description, rating,
  pdb.agg('{"value_count": {"field": "id"}}') OVER ()
FROM mock_items
WHERE category === 'electronics'
ORDER BY rating DESC
LIMIT 3;

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({ value_count: { field: "id" } }).over(),
  })
  .from(mockItems)
  .where(
    and(
      search.all(mockItems.id),
      search.term(mockItems.category, "electronics"),
    ),
  )
  .orderBy(desc(mockItems.rating))
  .limit(3);

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('{"value_count": {"field": "id"}}'))
).order_by('-rating')[:3]

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=facets.value_count(field="id"), field=MockItem.id)

with Session(engine) as session:
    rows = session.execute(stmt).all()
    facets.extract(rows)

relation = MockItem.search(:category)
                   .term("electronics")
                   .with_agg(agg: ParadeDB::Aggregations.value_count(:id))
                   .select(:id, :description, :rating)
                   .order(rating: :desc)
                   .limit(3)

rows = relation.to_a
aggregates = relation.aggregates

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 { value_count = new { field = "id" } })
    })
    .Take(3)
    .ToListAsync();

Expected Response

 id |         description         | rating |      agg
----+-----------------------------+--------+----------------
 12 | Innovative wireless earbuds |      5 | {"value": 5.0}
  1 | Ergonomic metal keyboard    |      4 | {"value": 5.0}
  2 | Plastic Keyboard            |      4 | {"value": 5.0}
(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).

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 (). Standard OVER () window aggregates (COUNT, SUM, AVG, MIN, MAX) over group columns or group aggregates are pushed down alongside it:

SELECT
  in_stock,
  COUNT(*) AS group_rows,
  COUNT(*) OVER () AS total_groups,
  pdb.combine_agg(pdb.agg('{"terms": {"field": "rating"}}')) OVER () AS ratings
FROM mock_items
WHERE description ||| 'shoes'
GROUP BY in_stock
ORDER BY in_stock;

Indexing

Full-Text / Lexical Search

Vector / Semantic Search

Hybrid Search

Aggregations

JOINs

Sharding

Cheatsheets

Facets with `GROUP BY`

​Facets with GROUP BY

Facets with `GROUP BY`