Join query in elasticsearch

[shwesinhan (ssh)]

hi team

can anyone convert this sql query to elasticsearch dsl query or lucene query or painless script?

select (t2.log_timestamp - t1.log_timestamp) as elapsed_time
from tbl_logs t1 inner join tbl_logs t2
on t1.order_id = t2.order_id

plz guide
appreciate!!

[warkolm (Mark Walkom)]

You cannot do joins in Elasticsearch.

[shwesinhan (ssh)]

any option to emulate joining?

[Christian_Dahlqvist]

You can create an entity-centric index with one document per order. As new time stamps and other information arrives you then update the appropriate document instead of storing it separately. This will make it a lot easier to run the type of queries you described.o

[shwesinhan (ssh)]

@Christian_Dahlqvist

you mean to become one doc., right?
but actually my app logs are continuous. how do i create entity-centric index one document per order?
plz guide me.
appreciate!!!

[Christian_Dahlqvist]

Have a look at this Elastic{ON} talk. This video may also be useful.