--- title: Terms description: Count the number of occurrences for each value in a result set canonical: https://www.paradedb.com/docs/reference/aggregates/bucket/terms --- If a text or JSON field is in the `GROUP BY` or `ORDER BY` clause, it must use the [literal](/reference/tokenizers/available-tokenizers/literal) tokenizer. A terms aggregation counts the number of occurrences for every unique value in a field. For example, the following query groups the `mock_items` table by `rating`, and calculates the number of items for each unique `rating`. ```sql SQL SELECT rating, pdb.agg('{"filter": "*"}') FROM mock_items WHERE id @@@ pdb.all() GROUP BY rating LIMIT 10; ``` ```ts Drizzle import { search } from "@paradedb/drizzle-paradedb"; await db .select({ rating: mockItems.rating, agg: search.agg({ filter: "*" }), }) .from(mockItems) .where(search.all(mockItems.id)) .groupBy(mockItems.rating) .limit(10); ``` ```python Django from paradedb import Agg, All, ParadeDB MockItem.objects.filter( id=ParadeDB(All()) ).values('rating').annotate( agg=Agg('{"filter": "*"}') )[:10] ``` ```python SQLAlchemy from sqlalchemy import select from sqlalchemy.orm import Session from paradedb.sqlalchemy import pdb, search stmt = ( select(MockItem.rating, pdb.agg({"filter": "*"}).label("agg")) .where(search.all(MockItem.id)) .group_by(MockItem.rating) .limit(10) ) with Session(engine) as session: session.execute(stmt).all() ``` ```ruby Rails MockItem.search(:id) .match_all .aggregate_by( :rating, agg: { filter: "*" } ) .limit(10) ``` ```cs EF Core await dbContext .MockItems.Where(item => EF.Functions.All(item.Id)) .GroupBy(item => item.Rating) .Select(group => new { Rating = group.Key, Agg = EF.Functions.Agg(new { filter = "*" }) }) .Take(10) .ToListAsync(); ``` ```ini Expected Response rating | agg --------+------------------ 4 | {"doc_count": 16} 5 | {"doc_count": 12} 3 | {"doc_count": 9} 2 | {"doc_count": 3} 1 | {"doc_count": 1} (5 rows) ``` Ordering by the bucketing field is supported: ```sql SQL SELECT rating, pdb.agg('{"filter": "*"}') FROM mock_items WHERE id @@@ pdb.all() GROUP BY rating ORDER BY rating LIMIT 10; ``` ```ts Drizzle import { search } from "@paradedb/drizzle-paradedb"; await db .select({ rating: mockItems.rating, agg: search.agg({ filter: "*" }), }) .from(mockItems) .where(search.all(mockItems.id)) .groupBy(mockItems.rating) .orderBy(mockItems.rating) .limit(10); ``` ```python Django from paradedb import Agg, All, ParadeDB MockItem.objects.filter( id=ParadeDB(All()) ).values('rating').annotate( agg=Agg('{"filter": "*"}') ).order_by('rating')[:10] ``` ```python SQLAlchemy from sqlalchemy import select from sqlalchemy.orm import Session from paradedb.sqlalchemy import pdb, search stmt = ( select(MockItem.rating, pdb.agg({"filter": "*"}).label("agg")) .where(search.all(MockItem.id)) .group_by(MockItem.rating) .order_by(MockItem.rating) .limit(10) ) with Session(engine) as session: session.execute(stmt).all() ``` ```ruby Rails MockItem.search(:id) .match_all .aggregate_by( :rating, agg: { filter: "*" } ) .order(:rating) .limit(10) ``` ```cs EF Core await dbContext .MockItems.Where(item => EF.Functions.All(item.Id)) .GroupBy(item => item.Rating) .Select(group => new { Rating = group.Key, Agg = EF.Functions.Agg(new { filter = "*" }) }) .OrderBy(result => result.Rating) .Take(10) .ToListAsync(); ``` Ordering by the aggregate value is not yet supported. For performance reasons, we strongly recommend adding a `LIMIT` to the `GROUP BY`. Terms aggregations without a `LIMIT` consume more memory and are slower to execute. If a query does not have a limit and more than `65000` unique values are found in a field, an error will be returned. ## JSON Syntax The SQL `GROUP BY` form above is the recommended way to run a terms aggregation. The same aggregation can also be expressed directly with `pdb.agg`, using the `terms` JSON syntax this page is named after: ```sql SELECT pdb.agg('{"terms": {"field": "rating", "size": 5}}') FROM mock_items WHERE id @@@ pdb.all(); ``` ```ini Expected Response agg -------------------------------------------------------------------------------------------------------------------------------------------------------------- {"buckets": [{"key": 4, "doc_count": 16}, {"key": 5, "doc_count": 12}, {"key": 3, "doc_count": 9}, {"key": 2, "doc_count": 3}, {"key": 1, "doc_count": 1}], "sum_other_doc_count": 0, "doc_count_error_upper_bound": 0} (1 row) ``` Array fields (such as `text[]`) are also supported; each element is counted into its respective bucket. See the [Tantivy documentation](https://docs.rs/tantivy/latest/tantivy/aggregation/bucket/struct.TermsAggregation.html) for all available options.