I have a requirement to query on elasticsearch data and my operation is dependent on two indices at a time.
select x.date,distinct(y.number) from index1 x,index2 y where x.id1=y.id1 and id2 in (select id2 from index1 having count(distinct(id1))>=10) and a.date between 'FROM' and 'TO' order by number
So as can be seen above, my current SQL query gives me two columns date and number, while having followed some conditions.
id2 column is present only in index1. The inner query results in a limited set of entries of index1 to be queried further.
My actual query is on that limited set of index1 and where the id1 value matches from both index and there is some restriction on date...
Could you suggest me the best possible way to achieve this as i am clueless to proceed!
I have tried SQL of elasticsearch but it does not have distinct functionality, it does not have current date function etc.