Elasticsearch SQL sub query: IN query not supported yet

Elastic Stack Elasticsearch
1

I'm trying to learn to learn how to use Elasticsearch SQL for use in the Kibana canvas.

What I would like to do is aggregate sales by some metric over country and date, but filter those results so i'm looking at two specific products (identified by some identifier), and in the top 20 countries according to the sum of this given metric.

I gather this should maybe involve a sub query, something like this below where I find the top 20 countries as a sub query which is passed into the main query:

SELECT date, 
       country, 
       sum(metric)
FROM sales 
WHERE 
  (identifier IN ('<identifier1>','<identifier2>')) 
  AND 
  (country IN 
     (SELECT country 
      FROM sales 
      WHERE identifier IN ('<identifier1>','<identifier2>') 
      GROUP BY country 
      ORDER BY sum(metric) DESC 
      LIMIT 20)
   ) 
GROUP BY date, country 
ORDER BY date, streams DESC

However, I am receiving a request error of the sort: "IN query not supported yet", which leads me to think this could be an issue on the Elasticsearch side rather than the SQL syntax. When hardcoding the 20 countries, the query works. Is there a simpler way I can construct this query or is it just not possible in Elasticsearch at this time?

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.