Redis Search

# Average price per brand for mountain bicycles, top 5 brands
FT.AGGREGATE idx:bicycle "@type:{mountain}"
    LOAD 3 @brand @price @condition
    APPLY "@price * 0.9" AS sale_price
    FILTER "@condition == 'new'"
    GROUPBY 1 @brand
        REDUCE COUNT 0 AS bike_count
        REDUCE AVG 1 @price AS avg_price
        REDUCE AVG 1 @sale_price AS avg_sale_price
    SORTBY 2 @avg_price DESC
    LIMIT 0 5
    DIALECT 2

REDUCE COUNT 0 AS n                          # count rows in group
REDUCE COUNT_DISTINCT 1 @user_id AS uniq     # distinct values of @user_id
REDUCE SUM 1 @price AS total
REDUCE AVG 1 @price AS mean
REDUCE MIN 1 @price AS lo
REDUCE MAX 1 @price AS hi
REDUCE QUANTILE 2 @price 0.95 AS p95
REDUCE TOLIST 1 @model AS models             # collect into a list
REDUCE FIRST_VALUE 1 @model BY @price DESC AS top_model

# Bad: missing @ on pipeline field references
GROUPBY 1 category                       # "Unknown property 'category'. Did you mean '@category'?"
APPLY substr(date, 0, 4) AS year         # "Unknown symbol 'date'"
FILTER "date >= '2022-01'"               # "Unknown symbol 'date'"

# Good
GROUPBY 1 @category
APPLY substr(@date, 0, 4) AS year
FILTER "@date >= '2022-01'"

# Bad: REDUCE without a preceding GROUPBY — "Unknown argument 'REDUCE' at position 1"
FT.AGGREGATE idx:bicycle "@type:{mountain}" REDUCE AVG 1 @price AS avg_price DIALECT 2

# Good: single-row aggregate over all matched docs
FT.AGGREGATE idx:bicycle "@type:{mountain}"
    GROUPBY 0
        REDUCE AVG 1 @price AS avg_price
    DIALECT 2

# Bad: "Bad arguments for COUNT: could not convert ..."
GROUPBY 0 REDUCE COUNT AS count

# Good
GROUPBY 0 REDUCE COUNT 0 AS count

# Bad: nargs=1 but the LOAD has 3 tokens — "Unknown argument 'AS' at position 4"
LOAD 1 $.beers[?(@.abv >= 0.07)] AS match
LOAD 1 $.brewery_id AS brewery_id

# Good: 1 aliased path = 3 tokens
LOAD 3 $.beers[?(@.abv >= 0.07)] AS match

# Good: 2 unaliased + 1 aliased = 2 + 3 = 5 tokens
LOAD 5 @date @subject $.event.ts AS ts

# Bad: SORTBY 1 @count DESC — Redis consumes 1 token, then "Unknown argument 'DESC'"
SORTBY 1 @count DESC

# Bad: SORTBY 2 @brand DESC @price ASC — said 2, gave 4, trailing tokens become unknown args
SORTBY 2 @brand DESC @price ASC

# Good: one sort entry = 2 tokens
SORTBY 2 @count DESC

# Good: two sort entries = 4 tokens
SORTBY 4 @count DESC @brand ASC

# Good (preferred): in-sort limit
SORTBY 2 @count DESC MAX 5

# Equivalent but less efficient
SORTBY 2 @count DESC
    LIMIT 0 5

# Bad: combining MAX and LIMIT — redundant; pick one
SORTBY 2 @count DESC MAX 5 LIMIT 0 5

# Bad: FILTER on a TEXT field that wasn't loaded — "Unknown symbol 'date'"
FT.AGGREGATE idx:observations "@code:\"heart rate\""
    GROUPBY 0 REDUCE COUNT 0 AS count
    FILTER "@date >= '2023-01-01'"
    DIALECT 2

# Good: express the date filter in the query string (it hits the index directly)
FT.AGGREGATE idx:observations "@date:2023* @code:\"heart rate\""
    GROUPBY 0 REDUCE COUNT 0 AS count
    DIALECT 2

# Also valid: LOAD the field first, then FILTER on it in the pipeline
FT.AGGREGATE idx:observations "@code:\"heart rate\""
    LOAD 1 @date
    FILTER "@date >= '2023-01-01'"
    GROUPBY 0 REDUCE COUNT 0 AS count
    DIALECT 2

# Bad: loaded $.subject, then referenced @timestamp
#       → "Property '@timestamp' not loaded nor in schema"
LOAD 1 $.subject
SORTBY 2 @timestamp DESC

# Good: load every alias you'll reference; rename with AS when needed
LOAD 2 @date @subject
SORTBY 2 @date DESC

# Good: explicit AS for a nested JSONPath
LOAD 2 $.subject AS subject $.event.ts AS ts
SORTBY 2 @ts DESC

# How many breweries have at least one beer with ABV >= 0.07?
FT.AGGREGATE idx:breweries "*"
    LOAD 1 $.beers[?(@.abv >= 0.07)] AS match
    FILTER "exists(@match)"
    GROUPBY 0 REDUCE COUNT 0 AS qualifying_breweries
    DIALECT 2

# "how many"                  → GROUPBY 0 REDUCE COUNT 0 AS n
# "how many distinct X"       → GROUPBY 1 @x  GROUPBY 0 REDUCE COUNT 0 AS n
#                                (or single-step: REDUCE COUNT_DISTINCT 1 @x AS n)
# "list distinct X"           → GROUPBY 0 REDUCE TOLIST 1 @x AS list
# "sum / total"               → REDUCE SUM 1 @field AS total
# "average per Y"             → GROUPBY 1 @y REDUCE AVG 1 @field AS avg
# "top row by stat"           → REDUCE FIRST_VALUE 4 @other_field BY @stat DESC AS top
# "p95 / quantile"            → REDUCE QUANTILE 2 @field 0.95 AS p95
# "min / max within group"    → REDUCE MIN 1 @field  /  REDUCE MAX 1 @field

# Q: "Top brand per state by bike count"
FT.AGGREGATE idx:bicycle "*"
    GROUPBY 2 @state @brand
        REDUCE COUNT 0 AS cnt
    GROUPBY 1 @state
        REDUCE FIRST_VALUE 4 @brand BY @cnt DESC AS top_brand
        REDUCE MAX 1 @cnt AS top_cnt
    SORTBY 2 @top_cnt DESC
    DIALECT 2

# Q: "How many bikes per category"
FT.AGGREGATE idx:bicycle "*"
    GROUPBY 1 @category
        REDUCE COUNT 0 AS n
    SORTBY 2 @n DESC
    DIALECT 2

# Q: "How many distinct brands sell mountain bikes?"
FT.AGGREGATE idx:bicycle "@type:{mountain}"
    GROUPBY 1 @brand
    GROUPBY 0 REDUCE COUNT 0 AS distinct_brand_count
    DIALECT 2

# Q: "Bites per year"
FT.AGGREGATE idx:bites "*"
    LOAD 1 @DateOfBite
    APPLY year(@DateOfBite) AS year
    GROUPBY 1 @year REDUCE COUNT 0 AS n
    SORTBY 2 @year ASC
    DIALECT 2

# Q: "How many bites in 2016 by breed"
FT.AGGREGATE idx:bites "@Breed:rottweiler"
    LOAD 1 @DateOfBite
    APPLY year(@DateOfBite) AS year
    FILTER "@year == 2016"
    GROUPBY 0 REDUCE COUNT 0 AS n
    DIALECT 2

# Round @weight to 2 decimal places
APPLY floor(@weight * 100) / 100 AS weight_rounded

APPLY substr(@date, 0, 4) AS year      # YYYY
APPLY substr(@date, 5, 2) AS month     # MM
APPLY substr(@date, 8, 2) AS day       # DD
APPLY substr(@date, 0, 7) AS yyyy_mm   # YYYY-MM

# "Cities NOT containing 'ile'"
FT.AGGREGATE idx:cities "*"
    LOAD 1 @city
    FILTER "!contains(@city, 'ile')"
    DIALECT 2

# Bad: FILTER after GROUPBY filters group rows, not source rows.
# Intent was "only new bikes," but here you keep all groups and trim brand rows by mean price.
FT.AGGREGATE idx:bicycle "*"
    GROUPBY 1 @brand REDUCE AVG 1 @price AS avg_price
    FILTER "@condition == 'new'"     # @condition no longer exists post-group!
    DIALECT 2

# Bad: GROUPBY count mismatched — RESP parse error or surprising grouping
FT.AGGREGATE idx:bicycle "*"
    GROUPBY 2 @brand               # said 2 fields but only listed 1
        REDUCE COUNT 0 AS n
    DIALECT 2

# Bad: LOAD * inflates the pipeline payload on every doc
FT.AGGREGATE idx:bicycle "*" LOAD * GROUPBY 1 @brand REDUCE COUNT 0 AS n DIALECT 2

# redis-py — STEP_START aggregate_pipeline
# Mirrors doctests/query_agg.py
from redis import Redis
from redis.commands.search.aggregation import AggregateRequest
from redis.commands.search.reducers import count, avg, sort_by

r = Redis()
req = (
    AggregateRequest("@type:{mountain}")
    .load("@brand", "@price", "@condition")
    .apply(sale_price="@price * 0.9")
    .filter("@condition == 'new'")
    .group_by("@brand", count().alias("bike_count"), avg("@price").alias("avg_price"))
    .sort_by(("@avg_price", "DESC"))
    .limit(0, 5)
    .dialect(2)
)
results = r.ft("idx:bicycle").aggregate(req)
# STEP_END

// Jedis — STEP_START aggregate_pipeline
// Mirrors QueryAggExample.java
import redis.clients.jedis.UnifiedJedis;
import redis.clients.jedis.search.aggr.AggregationBuilder;
import redis.clients.jedis.search.aggr.Reducers;
import redis.clients.jedis.search.aggr.SortedField;

try (UnifiedJedis jedis = new UnifiedJedis("redis://localhost:6379")) {
    AggregationBuilder agg = new AggregationBuilder("@type:{mountain}")
        .load("@brand", "@price", "@condition")
        .apply("@price * 0.9", "sale_price")
        .filter("@condition == 'new'")
        .groupBy("@brand",
            Reducers.count().as("bike_count"),
            Reducers.avg("@price").as("avg_price"))
        .sortBy(SortedField.desc("@avg_price"))
        .limit(0, 5)
        .dialect(2);
    jedis.ftAggregate("idx:bicycle", agg);
}
// STEP_END