Elasticsearch like select ..在哪里. .按…分组拥有

Question

在elasticsearch中，如何实现这样的功能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;

到目前为止，我只做了这个:

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

就像我刚刚完成了这个:

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;

d、拥有条件、sum(c) asc的额外顺序和偏移量5怎么办?

很遗憾，我发现具有子句目前不支持。

Answer 1

这是在5.2中作为桶选择器聚合实现的，例如

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