含有exclude的查询语句,结果还是显示了包含exclude条件的数据

Kibana | 作者 sailershen | 发布于2019年07月21日 | 阅读数:301

想查询最近4小时发送过注册消息的数据,要求ouyu-version不包含230和105的,只显示"ouyu-number","ouyu-version"这两个字段,查询代码如下:
GET /bj-sip_register/_search
{
"aggs": {
"today": {
"filter": {
"range": {
"@timestamp" :{
"gte":"now-4h",
"lt":"now"
}
}
},
"aggs": {
"ouyu-version": {
"terms" : {
"field": "ouyu-version.keyword",
"exclude": ["230", "105"],
"size": 10
},
"aggs": {
"ouyu": {
"cardinality" : {
"field" : "ouyu-number.keyword"
}
}
}
}
}
}
},
"_source": ["ouyu-number","ouyu-version"],
"size":10,
"from": 1
}

但是结果显示了ouyu-version字段值为230和105的数据,只在最下面的aggregations里显示了ouyu-version不为230和105的其它值。
不知道查询语句哪里不对。
以下是查询结果:
{
"took" : 874,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 10000,
"relation" : "gte"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "bj-sip_register",
"_type" : "_doc",
"_id" : "bbImE2wBZw_p4NeuVkzG",
"_score" : 1.0,
"_source" : {
"ouyu-number" : "280057",
"ouyu-version" : "105"
}
},
{
"_index" : "bj-sip_register",
"_type" : "_doc",
"_id" : "c7ImE2wBZw_p4NeuV0yG",
"_score" : 1.0,
"_source" : {
"ouyu-number" : "280057",
"ouyu-version" : "105"
}
},
{
"_index" : "bj-sip_register",
"_type" : "_doc",
"_id" : "dbImE2wBZw_p4NeuV0zg",
"_score" : 1.0,
"_source" : {
"ouyu-number" : "280022",
"ouyu-version" : "105"
}
},
{
"_index" : "bj-sip_register",
"_type" : "_doc",
"_id" : "k7ImE2wBZw_p4NeuXkxR",
"_score" : 1.0,
"_source" : {
"ouyu-number" : "280022",
"ouyu-version" : "105"
}
},
{
"_index" : "bj-sip_register",
"_type" : "_doc",
"_id" : "lbImE2wBZw_p4NeuXkyp",
"_score" : 1.0,
"_source" : {
"ouyu-number" : "444166",
"ouyu-version" : "105"
}
},
{
"_index" : "bj-sip_register",
"_type" : "_doc",
"_id" : "prImE2wBZw_p4NeucEyS",
"_score" : 1.0,
"_source" : {
"ouyu-number" : "444166",
"ouyu-version" : "105"
}
},
{
"_index" : "bj-sip_register",
"_type" : "_doc",
"_id" : "qLImE2wBZw_p4NeucEza",
"_score" : 1.0,
"_source" : {
"ouyu-number" : "280023",
"ouyu-version" : "105"
}
},
{
"_index" : "bj-sip_register",
"_type" : "_doc",
"_id" : "sbImE2wBZw_p4Neudkzr",
"_score" : 1.0,
"_source" : {
"ouyu-number" : "280023",
"ouyu-version" : "105"
}
},
{
"_index" : "bj-sip_register",
"_type" : "_doc",
"_id" : "srImE2wBZw_p4Neud0wH",
"_score" : 1.0,
"_source" : {
"ouyu-number" : "18500131732",
"ouyu-version" : "230"
}
},
{
"_index" : "bj-sip_register",
"_type" : "_doc",
"_id" : "s7ImE2wBZw_p4Neud0w7",
"_score" : 1.0,
"_source" : {
"ouyu-number" : "666806",
"ouyu-version" : "230"
}
}
]
},
"aggregations" : {
"today" : {
"doc_count" : 30926,
"ouyu-version" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "229",
"doc_count" : 286,
"ouyu" : {
"value" : 2
}
},
{
"key" : "103",
"doc_count" : 160,
"ouyu" : {
"value" : 1
}
},
{
"key" : "228",
"doc_count" : 160,
"ouyu" : {
"value" : 1
}
}
]
}
}
}
}

 
 
 
已邀请:

fantuan

赞同来自: sailershen

Terms aggregation里的exclude条件不会作用到Query上,要想返回的Hits里面没有230和105, 需要在Query Context里面加上过滤条件:
{
"query": {
"bool": {
"must_not": [
{
"terms": {
"ouyu-version.keyword": [
"230",
"105
]
}
}
]
}
}
}

fantuan

赞同来自: sailershen

首先Aggregation("aggs")是在Query Context("query")之后进行的运算,所以aggs里的查询语句不会作用到query匹配的文档结果上("hits")。
https://www.elastic.co/guide/e ... .html
 其次,你的需求是返回ouyu-number去重的结果,所以你要实现的是用Terms聚合拿到"aggregations"里返回的数据,而不是去拿搜索匹配的文档("hits")。你可以参考下面:
{
"size": 0,
"aggs": {
"ouyu-version-filter": {
"filter": {
"bool": {
"must_not": {
"terms": {
"ouyu-version.keyword": [
"230",
"105"
]
}
}
}
},
"aggs": {
"ouyu-number": {
"terms": {
"field": "ouyu-number.keyword",
"size": 10
}
}
}
}
}
}

sailershen

赞同来自:

感谢fantuan老师的回复,查询语句写成这样:
GET /bj-sip_register/_search
{
  "query": {
    "bool": {
      "must_not": [
        {
          "terms": {
            "ouyu-version.keyword": [
              "230",
              "105"
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "today": {
      "filter": {
        "range": {
          "@timestamp" :{
            "gte":"now-4h",
            "lt":"now"
          }
        }
      },
      "aggs": {
        "ouyu": {
          "cardinality" : {
            "field" : "ouyu-number.keyword"
          }
        }
      }
    }
  },
  "_source": ["ouyu-number","ouyu-version"],
  "size": 20,
  "from": 1
}

输出结果的确是把230和105的去掉了,但是ouyu-number还有是重复的,以上语句中cardinality部分没有生效,不知道该如何去重?谢谢。
 
以下是现在的输出结果,ouyu-number字段有很多是重复的:
{
  "took" : 386,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3863,
      "relation" : "eq"
    },
    "max_score" : 0.0,
    "hits" : [
      {
        "_index" : "bj-sip_register",
        "_type" : "_doc",
        "_id" : "G7ImE2wBZw_p4Neu7lSu",
        "_score" : 0.0,
        "_source" : {
          "ouyu-number" : "666621",
          "ouyu-version" : "103"
        }
      },
      {
        "_index" : "bj-sip_register",
        "_type" : "_doc",
        "_id" : "prInE2wBZw_p4Neuo16L",
        "_score" : 0.0,
        "_source" : {
          "ouyu-number" : "400136",
          "ouyu-version" : "228"
        }
      },
      {
        "_index" : "bj-sip_register",
        "_type" : "_doc",
        "_id" : "srInE2wBZw_p4Neuo178",
        "_score" : 0.0,
        "_source" : {
          "ouyu-number" : "400136",
          "ouyu-version" : "228"
        }
      },
      {
        "_index" : "bj-sip_register",
        "_type" : "_doc",
        "_id" : "nLIoE2wBZw_p4NeuF2Sf",
        "_score" : 0.0,
        "_source" : { }
      },
      {
        "_index" : "bj-sip_register",
        "_type" : "_doc",
        "_id" : "2bIoE2wBZw_p4NeuJGRl",
        "_score" : 0.0,
        "_source" : { }
      },
      {
        "_index" : "bj-sip_register",
        "_type" : "_doc",
        "_id" : "FLIoE2wBZw_p4NeudWb4",
        "_score" : 0.0,
        "_source" : {
          "ouyu-number" : "400135",
          "ouyu-version" : "229"
        }
      },
      {
        "_index" : "bj-sip_register",
        "_type" : "_doc",
        "_id" : "GbIoE2wBZw_p4NeudmYW",
        "_score" : 0.0,
        "_source" : {
          "ouyu-number" : "400135",
          "ouyu-version" : "229"
        }
      },
      {
        "_index" : "bj-sip_register",
        "_type" : "_doc",
        "_id" : "SrIpE2wBZw_p4Neup3bY",
        "_score" : 0.0,
        "_source" : {
          "ouyu-number" : "666621",
          "ouyu-version" : "103"
        }
      },
      {
        "_index" : "bj-sip_register",
        "_type" : "_doc",
        "_id" : "ULIpE2wBZw_p4NeurXZl",
        "_score" : 0.0,
        "_source" : {
          "ouyu-number" : "666621",
          "ouyu-version" : "103"
        }
      },
      {
        "_index" : "bj-sip_register",
        "_type" : "_doc",
        "_id" : "VbIqE2wBZw_p4NeuZ4EG",
        "_score" : 0.0,
        "_source" : {
          "ouyu-number" : "400136",
          "ouyu-version" : "228"
        }
      }
    ]
  },
  "aggregations" : {
    "today" : {
      "doc_count" : 953,
      "ouyu" : {
        "value" : 10
      }
    }
  }
}

 
 
 

sailershen

赞同来自:

感谢fantuan老师的指导,最终查询语句的结果完全符合要求,查出了所有ouyu-version不是230、105、100的结果(100是根据业务需求临时增加的)。
因为最上面增加了size:0,所以最后面的_source和from去掉了。
整个思路我基本理顺了。
GET /bj-sip_register/_search
{
"size": 0,
"aggs": {
"today": {
"filter": {
"range": {
"@timestamp" :{
"gte":"now-4h",
"lt":"now"
}
}
},
"aggs": {
"ouyu-version-filter": {
"filter": {
"bool": {
"must_not": [
{
"terms": {
"ouyu-version.keyword": [
"230",
"105",
"100"
]
}
}
]
}
},
"aggs": {
"ouyu": {
"terms": {
"field" : "ouyu-number.keyword",
"size": 20
}
}
}
}
}
}
}
}
 
 
输出结果:
{
"took" : 15,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 10000,
"relation" : "gte"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"today" : {
"doc_count" : 36835,
"ouyu-version-filter" : {
"doc_count" : 881,
"ouyu" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "400135",
"doc_count" : 166
},
{
"key" : "400136",
"doc_count" : 163
},
{
"key" : "666621",
"doc_count" : 160
},
{
"key" : "441005",
"doc_count" : 140
},
{
"key" : "400138",
"doc_count" : 126
},
{
"key" : "618601",
"doc_count" : 62
}
]
}
}
}
}
}

要回复问题请先登录注册