ALIAS and Complicated Search Query


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.

Example -

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???

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.