Filters

SELECT
    pdb.agg('{"value_count": {"field": "id"}}')
    FILTER (WHERE category === 'electronics') AS electronics_count,
    pdb.agg('{"value_count": {"field": "id"}}')
    FILTER (WHERE category === 'footwear') AS footwear_count
FROM mock_items;

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

await db
  .select({
    electronicsCount: search
      .agg({ value_count: { field: "id" } })
      .filter(search.term(mockItems.category, "electronics")),
    footwearCount: search
      .agg({ value_count: { field: "id" } })
      .filter(search.term(mockItems.category, "footwear")),
  })
  .from(mockItems);

from django.db.models import Q
from paradedb import Agg, ParadeDB, Term

MockItem.objects.aggregate(
    electronics_count=Agg(
        '{"value_count": {"field": "id"}}',
        filter=Q(category=ParadeDB(Term('electronics'))),
    ),
    footwear_count=Agg(
        '{"value_count": {"field": "id"}}',
        filter=Q(category=ParadeDB(Term('footwear'))),
    ),
)

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"))
    .filter(search.term(MockItem.category, "electronics"))
    .label("electronics_count"),
    pdb.agg(facets.value_count(field="id"))
    .filter(search.term(MockItem.category, "footwear"))
    .label("footwear_count"),
).select_from(MockItem)

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

result = MockItem.facets_agg(
  electronics_count: ParadeDB::Aggregations.filtered(
    ParadeDB::Aggregations.value_count(:id),
    field: :category,
    term: "electronics"
  ),
  footwear_count: ParadeDB::Aggregations.filtered(
    ParadeDB::Aggregations.value_count(:id),
    field: :category,
    term: "footwear"
  )
)

await dbContext
    .MockItems.Select(item => new
    {
        ElectronicsCount = EF.Functions.AggFilter(
            new { value_count = new { field = "id" } },
            EF.Functions.Term(item.Category, "electronics")
        ),
        FootwearCount = EF.Functions.AggFilter(
            new { value_count = new { field = "id" } },
            EF.Functions.Term(item.Category, "footwear")
        )
    })
    .ToListAsync();