Converting SQL query to ElasticSearch Query

Question

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

I tried following way:enter code here

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

Answer 1

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.

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

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.