I have a SQL that would like to query the same result set in ES. The result set would need to pagination. So I've tried Composite aggregation, it works fine for pagination and order. But I have no idea how to filter with the Having Statement
.
SELECT student_id,COUNT(paid_amount) paid_total
FROM paid_records
GROUP BY student_id
HAVING paid_total > 100
ORDER BY paid_total desc OFFSET 20 LIMIT 10;
Aggs Approach, this approach can't pagination:
{
"size": 0,
"aggs": {
"groupby_student": {
"terms": {
"field": "student_id"
},
"aggs": {
"paid_price_sum": {
"sum": {
"field": "total_paid_price"
}
},
"paid_price_sum_filter": {
"bucket_selector": {
"buckets_path": {
"priceSum": "paid_price_sum"
},
"script": "params.priceSum >= 100"
}
},
"paid_price_sort": {
"bucket_sort": {
"sort": {
"paid_price_sum": "desc"
},
"size": 10
}
}
}
}
}
}
Composite Agg approach, the result set is not correct, with the paid_price_sum_filter
it just filters out the current page.
{
"size": 0,
"aggs": {
"groupBy": {
"composite": {
"size": 10,
"after": {"paid_count": "5c8a1e4eede4790001b1d74b"},
"sources": [
{
"paid_count": {
"terms": {
"field": "student_id"
}
}
}
]
},
"aggs": {
"paid_price_sum": {
"sum": {
"field": "total_paid_price"
}
},
"paid_price_sum_filter": {
"bucket_selector": {
"buckets_path": {
"paid_price_sum": "paid_price_sum"
},
"script": "params.paid_price_sum > 100"
}
}
}
}
}
}