When I run an SQL query such as this via the Elasticsearch API:
{
"query": "SELECT SUM(field1) \/ COUNT(field2) as ratio FROM \"test*\""
}
It will return a nice clean ratio. But when I do a _sql\translate on it I get the following to perform a native Elasticsearch query.
{
"size": 0,
"_source": false,
"stored_fields": "_none_",
"aggregations": {
"groupby": {
"filters": {
"filters": [
{
"match_all": {
"boost": 1.0
}
}
],
"other_bucket": false,
"other_bucket_key": "_other_"
},
"aggregations": {
"7129": {
"filter": {
"exists": {
"field": "field2",
"boost": 1.0
}
}
},
"7198": {
"sum": {
"field": "field1"
}
}
}
}
}
}
This doesn't return a ratio at all and only a sum and count of the fields I want a ratio for.
I'm assuming it is possible to get the ratio returned since the _sql is returning the value, but the translation isn't showing exactly what the system is performing to return it. Does anyone have a suggestion on what needs to be adjusted in the Elasticsearch native query to get the end result. And how you would then display it as a metric in Kibana?
Currently using 7.3.1 and it is a query across two indices and both are not time series based.