# Elasticsearch SQL sub query: IN query not supported yet

**URL:** <https://discuss.elastic.co/t/elasticsearch-sql-sub-query-in-query-not-supported-yet/299864>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [March 16, 2022, 1:42pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-sub-query-in-query-not-supported-yet/299864 "2022-03-16T13:42:14Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![tg295](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tg295/32/84798_2.png) [@tg295](https://discuss.elastic.co/u/tg295)\
**Post date:** [March 16, 2022, 1:42pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-sub-query-in-query-not-supported-yet/299864/1 "2022-03-16T13:42:14Z")

</div>

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:

```auto
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?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 13, 2022, 1:42pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-sub-query-in-query-not-supported-yet/299864/2 "2022-04-13T13:42:54Z")

</div>

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