elasticsearch like select ... where ... group by ... having

Question

In elasticsearch, how to achieve the function like this SQL:

SELECT a, sum(b), sum(c), d
FROM TableA 
WHERE a IN (1,2,3,4) AND d = 88 
GROUP BY a 
HAVING sum(b) > 10 
ORDER BY sum(b) desc, sum(c) asc 
LIMIT 10 OFFSET 5;

So far I have only done this :

{
    "size":0,
    "query":{
        "filtered": {
            "filter":{
                "terms":{
                    "a": [1, 2, 3, 4]
                }
            },
            "query":{
                "match": {
                    "d": 8
                }
            }
        }
    },

    "aggs": {
        "group_by_a":{
            "terms":{
                "field": "a",
                "size": 10,
                "order" : { "sum(b)" : "desc" }
            },
            "aggs" : {
                "sum(b)": {"sum": {"field": "b"}},
                "sum(c)": {"sum": {"field": "c"}}
            }
        }
    }
}

Its is like I have just achieved this:

SELECT a, sum(b), sum(c)
FROM TableA 
WHERE a IN (1,2,3,4) AND d = 88 
GROUP BY a 
HAVING sum(b) > 10 
ORDER BY sum(b) desc
LIMIT 10;

And what to do with the extra return field d, the having condition, the extra order by sum(c) asc, and the offset 5 ?

So sad that I found having clause is unsupported currently.

use aggregate functions of elasticsearch, btw how far have you reached ? Show us what have you done so far. Thanks — dark_shadow, Aug 03 '14 at 17:25
@dark_shadow Thank you for your reply, and I have added what I have done. — flowyi, Aug 04 '14 at 04:12
hi @flowyi - fyi: we've just added having support to Crate Data in our new testing release 0.43.0. see https://crate.io/docs/en/0.43.0/sql/queries.html#having — dobe, Sep 12 '14 at 09:41

score 4 · Answer 1 · answered Mar 11 '17 at 00:20

This is implemented in 5.2 as bucket selector aggregation, ex

GET /_search
{
    "size": 0,
    "query": {
        "term": { "code": "16001" }
    },
    "aggs" : {
        "errors_per_week" : {
            "date_histogram" : {
                "field" : "date",
                "interval" : "week"
            },
            "aggs": {
                "total_errors": {
                    "value_count": {
                        "field": "code"
                    }
                },
                "error_bucket_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                          "totalErrors": "total_errors"
                        },
                        "script": "params.totalErrors > 5"
                    }
                }
            }
        }
    }
}

elasticsearch like select ... where ... group by ... having

1 Answers1