Elasticsearch: exclude results if other record exists

Question

I have the following elasticsearch index structure:

id|action|user_id|message_id
 1| click|      1|         1
 2|  open|      1|         1
 3| click|      2|         1
 4|  open|      2|         1
 5| click|      1|         2
 6| click|      1|         2
 7| click|      3|         2

The idea is to insert records with action:'open' for every user_id-message_id that that is missing records with action:'open'. For that purpose I need to get all user_id-message_id associations only if they don't have action:'open' already.

Is it possible to create a query that returns distinct user_id-message_id records excluding user_id-message_id if table also has record with action:open and the same user_id-message_id association?

Expected result:

 5| click|      2|         1
 7| click|      3|         2

possible duplicate of [ElasticSearch returning only documents with distinct value](http://stackoverflow.com/questions/24508191/elasticsearch-returning-only-documents-with-distinct-value) — bittusarkar, Mar 29 '15 at 07:39

score 1 · Answer 1 · answered Mar 29 '15 at 08:29

1

Well , you can achieve this by little scripting in terms query and topN hits aggregation.

{
  "query": {
    "filtered": {
      "filter": {
        "not": {
          "term": {
            "action": "open"
          }
        }
      }
    }
  },
  "aggs": {
    "keys": {
      "terms": {
        "script": "doc['message_id'].value + doc['user_id'].value"
      },
      "aggs": {
        "results": {
          "top_hits": {
            "size": 10
          }
        }
      }
    }
  }
}

answered Mar 29 '15 at 08:29

Vineeth Mohan

18,633
8
63
77

It seems, you just filter all action:'open' records. Maybe my question was not clear. I edited it. I don't want `{action: 'click', user_id: 1, message_id: 1}` in results if I already have `{action:'open', user_id: 1, message_id: 1}` in index. – Anton Mar 29 '15 at 16:02

Elasticsearch: exclude results if other record exists

1 Answers1