I'm relatively new to elasticsearch. I can able to make simple query in dev tools. I need a help on converting the following sql into es query

select c.conversationid from conversations c
where c.conversationid not in
 (select s.conversationid from conversations s 
where s.type='end' and s.conversationid=c.conversationid)

Index looks like below.

If I execute above query I will get the following results.

conversationid

2

3

CodePudding user response：

I have used following

1. Terms aggregation
2. Bucket Selector

Query

{
  "aggs": {
    "conversations": {
      "terms": {
        "field": "conversationid",
        "size": 10
      },
      "aggs": {  --> subaggregation where type == end
        "types": {
          "terms": {
            "field": "type.keyword",
            "include": [
              "end"
            ],
            "size": 10
          }
        },
        "select": {   --> select those terms where there is no bucket for "end"
          "bucket_selector": { 
            "buckets_path": {
              "path": "types._bucket_count"
            },
            "script": "params.path==0"
          }
        }
      }
    }
  }
}

Result

"aggregations" : {
    "conversations" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 2,
          "doc_count" : 1,
          "types" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        },
        {
          "key" : 3,
          "doc_count" : 1,
          "types" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      ]
    }
  }