FT.AGGREGATE order of operations

Order of operations for the FT.AGGREGATE command

Overview

FT.AGGREGATE is a powerful Redis Query Engine (RQE) 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 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.

Syntax and expression ordering

The FT.AGGREGATE command processes multiple expressions in a pipeline. Below is the recommended order:

  1. index – the name of your index, which must be the first argument.
  2. query – your query, which must be the second argument.
  3. FILTER – filters raw documents before transformations or aggregation.
  4. LOAD – loads document fields.
  5. APPLY – applies transformations on fields.
  6. GROUPBY – groups results by specific fields.
  7. REDUCE – performs aggregations. For example, SUM, COUNT, and AVG.
  8. SORTBY – orders the results based on specified fields.
  9. LIMIT – restricts the number of results returned.
  10. 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.

When to use @

You must add @ at the start of a field name in the following circumstances:

  • When referencing fields loaded from documents. In the following example, 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"  .  .  .
  • When referencing fields inside a 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"  .  .  .
  • When referencing fields inside 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"  .  .  .
  • When referencing fields created by 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"  .  .  .
  • When referencing fields created by 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"  .  .  .
  • When referencing fields created by 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"  .  .  .

GROUPBY with multiple REDUCE operations

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

Multiple APPLY operations followed by GROUPBY and REDUCE

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

FILTER and PARAMS

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

Placement of FILTER before and after GROUPBY/APPLY

  • Before GROUPBY: Removes records before aggregation.
  • After GROUPBY: Filters based on aggregated results.

LOAD after GROUPBY/REDUCE

This is not allowed and you'll get a syntax error.

Placement rules for specific parameters

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.

LIMIT and WITHCURSOR used together

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