SQL Aggregations and Translate

[aklys]

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.

[Andrei_Stefan]

@aklys not all the operations that SQL is doing are possible in the generated sql query. Some of them are done post-ES query. With translate you get the actual query that SQL will send to Elasticsearch, but it doesn't show you other operations its performing to get you the result.

Many times, the query sent to ES is the one that gets all the needed results, but there are some exceptions. We are aiming to improve the translate API response with https://github.com/elastic/elasticsearch/issues/41856 but we are not there yet.