Is SUBQUERY possible?

Hi,

I have an index "inputs", which consists of 2 columns: sessionId (string), timestamp (date).
I want to calculate average session duration and try to add visualization to Canvas.

SQL formula:
SELECT AVG(duration)
FROM
(SELECT MAX(timestamp)-MIN(timestamp) AS duration
FROM inputs
GROUP BY sessionId)

As I see, it would be also needed first to cast timestamp to numeric (BIGINT).
[MAX(timestamp) - MIN(timestamp)] must be [numeric], found value [MAX(timestamp)] type [datetime]

So I changed SQL query to:
SELECT
MAX(time) - MIN(time) as duration
FROM
(SELECT sessionId, CAST(timestamp as BIGINT) AS time
FROM "inputs")
GROUP BY sessionId

Now I'm getting error:
[math] > Failed to execute math expression. Check your column names

As I assume it's not supported within Elasticsearch:
Using sub-selects ( SELECT X FROM (SELECT Y) ) is supported to a small degree : any sub-select that can be "flattened" into a single SELECT is possible with Elasticsearch SQL.
Elasticsearch SQL limitations

Could I get confirmation that it's not possible within Elasticsearch to manipulate index in such a way to visualize average session duration using Kibana Canvas or Kibana Dashboard panel (I'm expected to do it through Kibana Dashboard panel)?

More general question:
As I understand Elasticsearch is not a tool that supports complex queries like average session duration?
Is the nature of Elasticsearch to do calculations based on a single row of index, not based on the whole index (GROUP BY) and that's incorrect use of Elasticsearch (average session duration) query?

I'd be extremely thankful for help,
Kuba

What you're trying to do is to have a 2nd level aggregation (AVG()) on top of the MIN() MAX() aggregations which unfortunately is not currently supported by ES SQL. I'm not sure what can be done in Kibana/Canvas on top of the basic MIN() MAX() query in order to get the AVG of this subtraction that you're looking for, so let's see if someone from Kibana has a workaround.

thx for response :slight_smile:

Hi Marios,

could you ping someone from Kibana?,

Thanks,
Kuba