带有子查询的ElasticSearch查询

nszi6y05  于 2022-11-02  发布在  ElasticSearch
关注(0)|答案(1)|浏览(233)

我对elasticsearch比较陌生。我可以在开发工具中进行简单的查询。我需要一个关于将下面的sql转换为es查询的帮助

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)

索引如下所示。
| 对话ID|类型|
| - -|- -|
| 一个|起动机|
| 2个|起动机|
| 一个|结束|
| 三个|起动机|
如果我执行上面的查询,我将得到以下结果。
对话ID
2
3

lpwwtiir

lpwwtiir1#

我使用了以下

  1. Terms aggregation
  2. Bucket Selector

查询

{
  "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"
          }
        }
      }
    }
  }
}

结果

"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" : [ ]
          }
        }
      ]
    }
  }

相关问题