ElasticSearch的bucket_selector筛选可以多次使用吗？

Question

示例索引结构：

PUT /demo 
{
    "mappings":{
        "properties":{
            "color":{
                "type":"keyword"
            },
            "size":{
                "type":"keyword"
            },
            "price":{
                "type":"double"
            }
        }
    }
}

测试数据：

POST /demo/_doc
{"color":"red","size":"large","price":100}
POST /demo/_doc
{"color":"blue","size":"large","price":500}
POST /demo/_doc
{"color":"red","size":"small","price":10}
POST /demo/_doc
{"color":"blue","size":"small","price":50}
POST /demo/_doc
{"color":"white","size":"small","price":1}
POST /demo/_doc
{"color":"white","size":"large","price":5}

如果我想要找出每种颜色下面每种尺寸的价格平均值，且过滤平均值价格小于10的数据咋写呢？

用SQL写的话就是：

select avg(price) as avg_price from xxx group by color,size having avg_price > 10

我目前写出来的查询DSL语句

GET /demo/_search
{
    "size": 0,
    "aggregations": {
      "color_group": {
        "terms": {
          "field": "color"
        },
        "aggregations": {
          "size_group": {
            "terms": {
              "field": "size"
            },
            "aggregations": {
              "avg_price": {
                "avg": {
                  "field": "price"
                }
              },
              "price_bucket_filter": {
                "bucket_selector": {
                  "buckets_path": {
                    "avgPrice": "avg_price"
                  },
                  "script": "params.avgPrice > 10"
                }
              }
            }
          }
        }
      }
    }
  } 

得到的结果虽然把价格小于等于10的排除了，但是将会有一个空的分组，请问咋能把这个空的分组排除掉呢？

结果如下：

    "aggregations": {
        "color_group": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": "blue",
                    "doc_count": 2,
                    "size_group": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "large",
                                "doc_count": 1,
                                "avg_price": {
                                    "value": 500.0
                                }
                            },
                            {
                                "key": "small",
                                "doc_count": 1,
                                "avg_price": {
                                    "value": 50.0
                                }
                            }
                        ]
                    }
                },
                {
                    "key": "red",
                    "doc_count": 2,
                    "size_group": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "large",
                                "doc_count": 1,
                                "avg_price": {
                                    "value": 100.0
                                }
                            }
                        ]
                    }
                },
                {
                    "key": "white",  // 希望去掉这个空的结果
                    "doc_count": 2,
                    "size_group": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": []
                    }
                }
            ]
        }
    }
}

Answer 1

  "price_bucket_filter": {
    "bucket_selector": {
      "buckets_path": {
        "avgPrice": "avg_price"
      },
      "script": "params.avgPrice > 10"
    }
  },
  "MIN_BUCKET_SELECTOR": {
    "bucket_selector": {
      "buckets_path": {
          "count": "color._bucket_count"
      },
      "script": {
        "inline": "params.count != 0"
      }
    }
  }

已参与了 SegmentFault 思否社区 10 周年「问答」打卡 ，欢迎正在阅读的你也加入。