将SQL查询转换为ElasticSearch Query [英] Converting SQL query to ElasticSearch Query

问题描述

我想将以下sql查询转换为Elasticsearch一个。可以任何一个帮助在这个。

I want to convert the following sql query to Elasticsearch one. can any one help in this.

select csgg, sum(amount) from table1
where type in ('a','b','c') and year=2016 and fc="33" group by csgg having sum(amount)=0

我尝试以下方式：在此输入代码

{
  "size": 500,
   "query" : {
      "constant_score" : { 
         "filter" : {
            "bool" : {
              "must" : [
                 {"term" : {"fc" : "33"}},
                 {"term" : {"year" : 2016}} 
              ],
              "should" : [
                {"terms" : {"type" : ["a","b","c"] }}   
              ]
           }
         }
      }
   },
  "aggs": {
    "group_by_csgg": {
      "terms": {
        "field": "csgg"
      },
      "aggs": {
        "sum_amount": {
          "sum": {
            "field": "amount"
          }
        }
      }
    }
  }
}

但不知道如果我正在做正确的，因为它不能验证结果。
似乎是要在内部加入查询。

but not sure if I am doing right as its not validating the results. seems query to be added inside aggregation.

推荐答案

假设您使用Elasticsearch 2.x，可能有具有弹性搜索中的语义
我没有意识到在2.0之前的可能性。

Assuming that you use Elasticsearch 2.x, there is a possibility to have the having-semantics in Elasticsearch. I'm not aware of a possibility prior 2.0.

您可以使用新的管道聚合 Bucket Selector Aggregation ，它只能选择满足特定条件的桶：

You can use the new Pipeline Aggregation Bucket Selector Aggregation, which only selects the buckets, which meet a certain criteria:

POST test/test/_search
{
  "size": 0,
  "query" : {
      "constant_score" : { 
         "filter" : {
            "bool" : {
              "must" : [
                 {"term" : {"fc" : "33"}},
                 {"term" : {"year" : 2016}},
                 {"terms" : {"type" : ["a","b","c"] }}
              ]
           }
         }
      }
   },
   "aggs": {
    "group_by_csgg": {
      "terms": {
        "field": "csgg",
        "size": 100
      },
      "aggs": {
        "sum_amount": {
          "sum": {
            "field": "amount"
          }
        },
        "no_amount_filter": {
          "bucket_selector": {
            "buckets_path": {"sumAmount": "sum_amount"},
            "script": "sumAmount == 0"
          }
        }
      }
    }
  }
}

但是有两个注意事项。根据您的配置，可能需要启用脚本：

However there are two caveats. Depending on your configuration, it might be necessary to enable scripting like that:

script.aggs: true
script.groovy: true

此外，由于它适用于父级桶，因此不能保证您获得所有桶数量= 0.如果术语聚合仅选择总金额！= 0的项，则您将没有结果。

Moreover, as it works on the parent buckets it is not guaranteed that you get all buckets with amount = 0. If the terms aggregation selects only terms with sum amount != 0, you will have no result.

这篇关于将SQL查询转换为ElasticSearch Query的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！