Hi everyone,
assume we are modeling restaurants and each restaurant has and id and a nested array of employees. An employee has a name and a salary.
I want to formulate a query which returns all restaurants where the sum of the salaries of the employees exceeds a certain threshold.
I tried experimenting with aggregations only and came up with this:
{
"size": 0,
"query": {
"match_all": {
"boost": 1
}
},
"sort": [],
"aggs": {
"test": {
"terms": {
"field": "id"
},
"aggregations": {
"nested_employeesSalary": {
"nested": {
"path": "employees"
},
"aggregations": {
"employeesSalary": {
"sum": {
"field": "employees.salary"
}
}
}
},
"sales_bucket_filter": {
"bucket_selector": {
"buckets_path": {
"totalSalaries": "nested_employeesSalary>employeesSalary"
},
"script": "params.totalSalaries > 10000"
}
}
}
}
}
}
This query returns one bucket per restaurant (as I grouped by the restaurant id). The problem is that I am not really interested in the buckets themselved, but in the document (i,e. the restaurant), which is in the bucket.
Now my questions:
- Is there a way to get the document defining each bucket in the query above?
- Is there a better way to formulate the query, I want to have?
Thanks in advance!