Agfdhyk

I have an ES index with such kind of documents:

from_1,to_1,timestamp_1

from_1,to_1,timestamp_2

from_1,to_2,timestamp_3

from_2,to_3,timestamp_4

from_1,to_2,timestamp_5

from_2,to_3,timestamp_6

from_1,to_1,timestamp_7

from_2,to_4,timestamp_8

I need a query that would return a document only if its combination of from and to values is different than the previous seen document with the same from value.

So with the provided sample above:

1. document with timestamp_1 should be in the result because there is no earlier document with from_1+to_1 combination


2. document with timestamp_2 must be skipped because its from+to combination is exactly the same as the last seen document with from = from_1
   


3. document with timestamp_3 should be in the result because its to field (to_2) is different than the value of the last seen with the same from (to_1 in document with timestamp_1
   


4. document with timestamp_4 should be in the result


5. document with timestamp_5 must not be in the result because it has the same combination of from+to as the last seen with from_1 (document with timestamp_3)


6. document with timestamp_6 must not be in the result because it has the same combination of from+to as the last seen with from_2 (document with timestamp_4)


7. document with timestamp_7 should be in the result because it has the different combination of from+to to the last seen with from_1 (document with timestamp_3)


8. document with timestamp_8 should be in the result because its combination is completely new so far

I need to fetch all such "semi-unique" documents from the index, so it would be nice if it possible to use scroll request or after_key if an aggregation is used.

Any ideas how to approach it?

The closest thing I could come up with is the following (let me know if it does not work with your data).

{

  "size": 0,

  "aggs": {

    "from_and_to": {

      "composite" : {

        "size": 5,

        "sources": [

          {

            "from_to_collected":{

              "terms": {

                "script": {

                  "lang": "painless",

                  "source": "doc['from'].value + '_' + doc['to'].value"

                }

              }

            }

          }]

      },

      "aggs": {

        "top_from_and_to_hits": {

          "top_hits": {

            "size": 1,

            "sort": [{"timestamp":{"order":"asc"}}],

            "_source": {"includes": ["_id"]}

          }

        }

      }

    }

  }

}

Keep in mind that the terms aggregations is probabilistic.

This will allow you to scroll to the next set of buckets over the from_to_collected key.