Search code examples
elasticsearchelasticsearch-dsl

Elasticsearch find all messages that were 'delivered' but not 'opened'

I have the following elasticsearch index data:

id|message_id|   action|
 1|         1|delivered|
 2|         1|   opened|
 3|         2|delivered|
 4|         3|delivered|
 5|         4|delivered|
 6|         5|   opened|

How to find all 'not opened' messages? Expected result:

id|message_id|   action|
 3|         2|delivered|
 4|         3|delivered|
Solution

  • That's going to be difficult with a single query because your data is normalized and thus would require a join on itself which AFAIK is not really possible in ES. I think there are two solutions:

    1. De-normalize your data using a nested field for action.
    2. Use two queries: In the first query you retrieve all messages that were delivered. For the second query you use the message_ids from the first query and use a bool filter to combine an ids filter and a term filter to get the ones not opened.