I need help on the elastic search query where it can join on the transaction id on same index. I know the joins are available/allowed on elastic search. Below is the example of my elasticsearch index. (Test index)
id | Time | type
732021456 |94 |Service2(capture)
732021456 |156 |Service1
788687662 |312 |Service1
309425654 |189 |Service1
675231293 |334 |Service2(capture)
675231293 |412 |Service1
Now I want to do the query to aggregate the timing (time column) only on the Id's which are matching. These logs are coming from two different log's and I am putting it in the same index. Not sure what would be the best approach to put service1 logs in a different index (let's say index1) and service2 logs in a different index (index2). But I feel even if I put in the different index there will be still a problem with Join i.e. aggregating on time only if id's are same.
It's something like select id, time from test where id=(select id from test where = 'Service2(capture)')
I hope I am able to explain the problem that I am facing.
Can someone please help with this?