content/develop/ai/search-and-query/advanced-concepts/aggregations-syntax.md
[FT.AGGREGATE]({{< relref "/commands/ft.aggregate" >}}) is a powerful Redis Search command for performing advanced data aggregation, filtering, sorting, and transformations on indexed hash or JSON documents. This reference page provides a structured breakdown of syntax, ordering rules, and best practices.
The [main aggregations page]({{< relref "/develop/ai/search-and-query/advanced-concepts/aggregations" >}}) has a simple diagram showing how FT.AGGREGATE pipelines are constructed, but it doesn't tell the whole story. For example, you can create more complex aggregation pipelines by applying multiple REDUCE functions under a single GROUPBY clause, or you can chain groupings and mix in additional mapping steps:
GROUPBY ... REDUCE ... APPLY ... GROUPBY ... REDUCE
{{< note >}} The examples on this page are based on a hypothetical "products" data set, which you can download here. {{< /note >}}
The FT.AGGREGATE command processes multiple expressions in a pipeline. Below is the recommended order:
index – the name of your index, which must be the first argument.query – your query, which must be the second argument.FILTER – filters raw documents before transformations or aggregation.LOAD – loads document fields.APPLY – applies transformations on fields.GROUPBY – groups results by specific fields.REDUCE – performs aggregations. For example, SUM, COUNT, and AVG.SORTBY – orders the results based on specified fields.LIMIT – restricts the number of results returned.DIALECT 2 - provides for more comprehensive query syntax, for example using parameters in FILTER expressions.Other keywords will be discussed toward the end of this page.
@You must add @ at the start of a field name in the following circumstances:
price is a document field and must be prefixed with @.FT.AGGREGATE products "*"
LOAD 1 @price
APPLY "@price * 1.1" AS adjusted_price
SORTBY 2 @adjusted_price DESC
LIMIT 0 10
1) (integer) 200
2) 1) "price"
2) "623"
3) "adjusted_price"
4) "685.3"
3) 1) "price"
2) "619.75"
3) "adjusted_price"
4) "681.725"
.
.
.
FILTER clause that were loaded from documents.FT.AGGREGATE products "*"
LOAD 1 @rating
FILTER "@rating >= 4.5"
LIMIT 0 10
1) (integer) 5
2) 1) "rating"
2) "4.5"
3) 1) "rating"
2) "4.8"
4) 1) "rating"
2) "4.5"
.
.
.
GROUPBY or REDUCE clauses.FT.AGGREGATE products "*"
GROUPBY 1 @category
REDUCE SUM 1 @price AS total_price
LIMIT 0 10
1) (integer) 6
2) 1) "category"
2) "Toys"
3) "total_price"
4) "9799.25"
3) 1) "category"
2) "Electronics"
3) "total_price"
4) "10683.5"
4) 1) "category"
2) "Apparel"
3) "total_price"
4) "10273.5"
.
.
.
REDUCE in APPLY or FILTER clauses.FT.AGGREGATE products "*"
GROUPBY 1 @category
REDUCE SUM 1 @price AS total_price
APPLY "@total_price * 1.2" AS boosted_price
FILTER "@total_price > 1000"
LIMIT 0 10
1) (integer) 6
2) 1) "category"
2) "Toys"
3) "total_price"
4) "9799.25"
5) "boosted_price"
6) "11759.1"
3) 1) "category"
2) "Electronics"
3) "total_price"
4) "10683.5"
5) "boosted_price"
6) "12820.2"
.
.
.
APPLY in another APPLY or FILTER clause.FT.AGGREGATE products "*"
LOAD 2 @price @discount
APPLY "@price - @discount" AS net_price
APPLY "@net_price * 1.1" AS marked_up
FILTER "@net_price > 200"
LIMIT 0 10
1) (integer) 60
2) 1) "price"
2) "220"
3) "discount"
4) "0"
5) "net_price"
6) "220"
7) "marked_up"
8) "242"
3) 1) "price"
2) "223.25"
3) "discount"
4) "1.5"
5) "net_price"
6) "221.75"
7) "marked_up"
8) "243.925"
.
.
.
APPLY in a SORTBY clause.FT.AGGREGATE products "*"
LOAD 2 @price @discount
APPLY "@price - @discount" AS net_price
SORTBY 2 @net_price DESC
LIMIT 0 10
1) (integer) 200
2) 1) "price"
2) "623"
3) "discount"
4) "6"
5) "net_price"
6) "617"
3) 1) "price"
2) "619.75"
3) "discount"
4) "4.5"
5) "net_price"
6) "615.25"
.
.
.
You can use multiple REDUCE operations after GROUPBY for different aggregations.
FT.AGGREGATE products "*"
GROUPBY 1 @category
REDUCE COUNT 0 AS product_count
REDUCE SUM 1 @price AS total_price
REDUCE AVG 1 @rating AS avg_rating
SORTBY 2 @total_price DESC
LIMIT 0 10
1) (integer) 6
2) 1) "category"
2) "Groceries"
3) "product_count"
4) "44"
5) "total_price"
6) "13495.25"
7) "avg_rating"
8) "3.94090909091"
3) 1) "category"
2) "Home"
3) "product_count"
4) "40"
5) "total_price"
6) "11026.75"
7) "avg_rating"
8) "3.78"
.
.
.
You can use APPLY in various ways before and after GROUPBY and REDUCE.
FT.AGGREGATE products "*"
LOAD 3 @price @discount @quantity
APPLY "@price - @discount" AS final_price
APPLY "@final_price * @quantity" AS total_revenue
GROUPBY 1 @category
REDUCE SUM 1 @total_revenue AS total_category_revenue
SORTBY 2 @total_category_revenue DESC
LIMIT 0 10
1) (integer) 6
2) 1) "category"
2) "Groceries"
3) "total_category_revenue"
4) "81373"
3) 1) "category"
2) "Home"
3) "total_category_revenue"
4) "55797.5"
.
.
.
Use FILTER to remove unwanted records, and PARAMS to pass values to parameterized queries.
FT.AGGREGATE products "*"
LOAD 3 @price @rating @quantity
FILTER "@price >= 500"
FILTER "@rating >= 4.0"
APPLY "@price * @quantity" AS total_value
SORTBY 2 @total_value DESC
LIMIT 0 10
DIALECT 2
1) (integer) 200
2) 1) "price"
2) "606.75"
3) "rating"
4) "4.2"
5) "quantity"
6) "10"
7) "total_value"
8) "6067.5"
3) 1) "price"
2) "541.75"
3) "rating"
4) "4.5"
5) "quantity"
6) "10"
7) "total_value"
8) "5417.5"
.
.
.
This is not allowed and you'll get a syntax error.
| Parameter | Placement |
|---|---|
TIMEOUT | Can be placed anywhere. |
LIMIT | Must be at the end, before DIALECT. |
WITHCURSOR | Must be at the end, before DIALECT. |
SCORER | Must be placed between the query and pipeline operations. |
ADDSCORES | Must be placed between the query and pipeline operations. |
DIALECT | Must be at the end. |
While you wouldn't ordinarily use LIMIT and WITHCURSOR together in the same query, you can do so if necessary.
LIMIT, as the name suggests, will limit the total number of results returned for the given query. WITHCURSOR will paginate the results in chunks of size COUNT. You can use the [cursor API]({{< relref "/develop/ai/search-and-query/advanced-concepts/aggregations/#cursor-api" >}}) to retrieve more results, as shown below.
FT.AGGREGATE products "*"
GROUPBY 1 @category
REDUCE COUNT 0 AS product_count
LIMIT 0 100
WITHCURSOR COUNT 3
1) 1) (integer) 6
2) 1) "category"
2) "Toys"
3) "product_count"
4) "28"
3) 1) "category"
2) "Electronics"
3) "product_count"
4) "31"
4) 1) "category"
2) "Apparel"
3) "product_count"
4) "36"
2) (integer) 89400486
127.0.0.1:6379> FT.CURSOR READ products 89400486 COUNT 3
1) 1) (integer) 0
2) 1) "category"
2) "Home"
3) "product_count"
4) "40"
3) 1) "category"
2) "Groceries"
3) "product_count"
4) "44"
4) 1) "category"
2) "Books"
3) "product_count"
4) "21"
2) (integer) 89400486
.
.
.
See the following resources for more information:
FT.AGGREGATE command page