如何在Elasticsearch 7.6.2中编写distinct查询

Question

我是ES的新手。我的要求是获取最近n个最近的时间戳和不同的trace_id以及它们的记录。就像sql查询"Select distinct trace_id，job_name from stpjoblogs where status="SUCCESS"“一样。请告诉我如何在Elasticsearch 7.6.2中实现同样的功能。

我的回答是：

_source" : {
          "port" : 57376,
          "job_name" : "stbl-executive-dashboard",
          "timestamp" : "2020-04-28T10:55:45.640267+00:00",
          "trace_id" : "180600fd27ef8108",
          "PCF_Space" : "Development",
          "PCF_Org" : "EPSOrg",
          "status" : "SUCCESS"
        }

我尝试了下面的查询：

GET /stpjoblogs/_search
{
  "query": {
    "bool": {
       "must": [
         {"match":{"status":"SUCCESS"}}
         ]
    }
  },
  "sort": [
    {
      "timestamp": {
        "order": "desc"
      }
    }
  ],
  "aggs": {
    "distinct_transactions": {
      "terms": { "field": "trace_id"}
    }
  }
}

但是我的错误不是很明显

{
  "error" : {
      "caused_by" : {
        "type" : "illegal_argument_exception",
        "reason" : "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [trace_id] in order to load field data by uninverting the inverted index. Note that this can use significant memory."
      }
  },
  "status" : 400
}

Answer 1

您需要添加trace_id.keyword。如果索引是通过自动映射创建的，则此字段将已经存在，或者您需要在映射中使用此字段

"trace_id":{
   "type":"text",
   "fields":{
      "keyword":{
           "type":"keyword"
       }
    }
}

GET /stpjoblogs/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "status": "SUCCESS"
          }
        }
      ]
    }
  },
  "sort": [
    {
      "timestamp": {
        "order": "desc"
      }
    }
  ],
  "aggs": {
    "distinct_transactions": {
      "terms": {
        "field": "trace_id.keyword"
      },
      "aggs": {
        "job_names": {
          "terms": {
            "field": "job_name.keyword",
            "size": 10
          },
          "aggs": {
            "docs": {
              "top_hits": {
                "size": 1,
                "sort": [{"timestamp":"desc"}]
              }
            }
          }
        }
      }
    }
  }
}

要获取分组的最新文档，您可以使用field collapsing，它的工作方式类似于trace_id by，并为每个字段返回前一个文档

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "status": "SUCCESS"
          }
        }
      ]
    }
  },
  "collapse": {
    "field": "trace_id.keyword",
    "inner_hits" : {
            "name": "space",
            "collapse" : {"field" : "job_name.keyword"},
            "size": 3
        }
  },
  "sort": [
    {
      "timestamp": {
        "order": "desc"
      }
    }
  ]
}