4

I have a scenario where I need to search for some documents. Values of one of the search field is result of another search query. In typical SQL scenario it looks something like this

Select * from table1 where column1 IN( select columnnx from table2 where date 'sampleDate')

One work around for this I found is to cache this columnx from document2(table2) and use it in termQuerys

boolQuery.must(QueryBuilders.termsQuery(fieldName, cachedList);

However I'm concerned about the performance of this as my cache can grow and have more than 100000 items. So if there is any alternate solution please suggest.

Thanks in advance. P.S . I'm new to elasticsearch.

Sarfaraz Khan
  • 2,166
  • 2
  • 14
  • 29
  • You can break it in two different queries . First find the ids and then pass to `termsQuery`. – Richa Feb 16 '16 at 15:58
  • 1
    @Richa thats what I have explained above I'm running a query and keeping it's result in a cache and feeding it to the other query. As my cache can grow huge,nearly half million of values so I'm just wondering if there is any other better way of managing this. – Sarfaraz Khan Feb 16 '16 at 18:56
  • this question is answered here. https://stackoverflow.com/questions/28734436/what-is-the-elasticsearch-equivalent-for-an-sql-subquery – damjad May 09 '18 at 06:41
  • Note: you could test if your SQL query could run directly on your Elasticsearch instance, since the [Elasticsearch 6.3 release now (June 2018) comes with native SQL support](https://stackoverflow.com/a/50889538/6309). – VonC Jun 16 '18 at 16:13

0 Answers0