Hi everyone:
I have an index, invoices
, that I need to aggregate into yearly buckets, then sort.
I have succeeded in using Bucket Sort to sort my buckets by simple sum values (revenue
and tax
). However, I am struggling to sort by more deeply nested doc_count values (status
).
I want to order my buckets not only by revenue
, but also by the number of docs with a status
field equal to 1 for example (that represents an 'approved' invoice).
Doc Shape
The documents in my index looks like this:
"_source": {
"created_at": "2018-07-07T03:11:34.327Z",
"status": 3,
"revenue": 68.474,
"tax": 6.85,
}
The Query
I request my aggregations like this:
const params = {
index: 'invoices',
size: 0,
body: {
aggs: {
sales: {
date_histogram: {
field: 'created_at',
interval: 'year',
},
aggs: {
total_revenue: { sum: { field: 'revenue' } },
total_tax: { sum: { field: 'tax' } },
statuses: {
terms: {
field: 'status',
},
},
approved_invoices: {
filter: {
term: {
status: 1,
},
},
},
sales_bucket_sort: {
bucket_sort: {
sort: [{ total_revenue: { order: 'desc' } }],
},
},
},
},
},
},
}
The Response
The response (truncated) looks like this:
"aggregations": {
"sales": {
"buckets": [
{
"key_as_string": "2016-01-01T00:00:00.000Z",
"key": 1451606400000,
"doc_count": 254,
"total_tax": {
"value": 735.53
},
"approved_invoices": {
"doc_count": 58
},
"statuses": {
"sum_other_doc_count": 0,
"buckets": [
{
"key": 2,
"doc_count": 59
},
{
"key": 1,
"doc_count": 58
},
{
"key": 5,
"doc_count": 57
},
{
"key": 3,
"doc_count": 40
},
{
"key": 4,
"doc_count": 40
}
]
},
"total_revenue": {
"value": 7355.376005351543
}
},
]
}
}
What I Tried
I want to sort by approved_invoices.doc_count. I tried to modify my query like this:
sales_bucket_sort: {
bucket_sort: {
sort: [{ 'approved_invoices.doc_count': { order: 'desc' } }],
},
},
But got this error:
Cannot find an aggregation named [doc_count] in [approved]"
If I try this I get this error:
sales_bucket_sort: {
bucket_sort: {
sort: [{ 'approved_invoices': { order: 'desc' } }],
},
},
buckets_path must reference either a number value or a single value numeric metric aggregation, got: org.elasticsearch.search.aggregations.bucket.filter.InternalFilter
I also tried to sort by the equivalent value in my terms aggregation but it did nothing:
sales_bucket_sort: {
bucket_sort: {
sort: [{ 'statuses.buckets[0]._doc_count': { order: 'asc' } }],
},
},
Thanks in advance for any pointers.