I am trying to write this query -
search Elasticsearch docs - in the field - foobar - for anything which has the characters "rix" in it -
Then lets assume we get 500 documents with "rix" in foobar field..
Now I want to check each of the 500 returned docs and see if 5 mins before this doc - was there a doc with characters "abh" in the foobar field. If yes I only want to finally retain and view those "rix" documents.
I got 500 documents where there is "rix"in foobar field.
Only for around 100 of these documents - there was an "abh" containing document 5 mins before.
I finally want to view only those 100 "rix" containing docs.
I have the following mySQL query -
SELECT t1.* FROM sumit_test AS t1 WHERE t1.foobar LIKE "%rix%" AND TIMESTAMPDIFF(MINUTE,(SELECT t2.@TIMESTAMP FROM sumit_test AS t2 WHERE t2.foobar LIKE "%abh%"),t1.@TIMESTAMP) < 5
How do I do it in Elasticsearch DSL???