Search code examples
elasticsearchelasticsearch-dsl

How to achive this sql query result in Elastic Search

I have a error index containing all the errors logged by the php application. Now I want a DSL query whitch returns the distinct errors by message and also the count.

Similar to mysql query:

SELECT *, COUNT(*) AS total FROM errorsGROUP BYmessage;

Mapping for my index:

"mappings": {
  "errors": {
    "properties": {
      "message": {
        "type": "keyword"
      },
      "trace": {
        "type": "keyword"
      },
      "file": {
        "type": "keyword"
      }
    }
  }
}

Expected result:

Message | File | Count

Undefined variable $param at line 20 in index.php | project/index.php | 10

Undefined variable $opt at line 15 in helper.php | project/helper.php | 4

..........

I am using elastic search 5.6. Thanks in advance.

Solution

  • top hits aggregation could get you there, I think.

    GET errors/_search?size=0
{
  "aggs": {
    "error-counts": {
      "terms": {
        "field": "message"
      },
      "aggs": {
        "messages": {
          "top_hits": {
            "size": 100
          }
        }
      }
    }
  }
}

    This will create buckets for each message, with a total count in each bucket, and with a list of 100 records within each bucket.