What to do to show aggregations doc_count 0, for graphics, when result dont exists

Elastic Stack Elasticsearch
1

hello, I have this sample search where the result I want to use in a highchart chart.

 `GET _search
{
    "query": {
        "filtered": {
            "query": {
                "bool": {
                    "must": [
                    {"terms": {"sexo": ["M","F"]}},
                     {"terms": {"doenca":
                     ["Dengue","Intox Alcoolica","DST","Intox Alimentar","Animal Peconhento"
                     ]}}]
                }
            }
        }
    },  
    "size": 0,
        "aggs" : {
            "municipios" : {
                "terms" : { 
                    "field" : "bairro",
                    "size":15
                },
                
                "aggs": {
    "doencas": {
      "terms": { 
        "field": "doenca", "size" : 5,"order" : { "_term" : "asc" }
      }
    }}
        }
        }
  }`

As you can see, before the aggregations, it goes through some queries and terms.
The problem is that when it does not find the result to the Aggregation, he's not returning any data, and I need it to return 0 in the case.

a return Example:

{
   "took": 69,
    "timed_out": false,
    "_shards": {
       "total": 5,
  "successful": 5,
   "failed": 0
 },
 "hits": {
   "total": 5260,
   "max_score": 0,
   "hits": []
},
"aggregations": {
  "municipios": {
     "doc_count_error_upper_bound": 0,
     "sum_other_doc_count": 2234,
     "buckets": [
        { {
           "key": "Ouro Verde",
           "doc_count": 248,
           "doencas": {
              "doc_count_error_upper_bound": 0,
              "sum_other_doc_count": 0,
              "buckets": [
                 {
                    "key": "Animal Peconhento",
                    "doc_count": 38
                 },
                 {
                    "key": "DST",
                    "doc_count": 68
                 },
                 {
                    "key": "Dengue",
                    "doc_count": 1
                 },
                 {
                    "key": "Intox Alcoolica",
                    "doc_count": 101
                 },
                 {
                    "key": "Intox Alimentar",
                    "doc_count": 40
                 }
              ]
           }
        },
        {
           "key": "Municipal",
           "doc_count": 244,
           "doencas": {
              "doc_count_error_upper_bound": 0,
              "sum_other_doc_count": 0,
              "buckets": [
                 {
                    "key": "Animal Peconhento",
                    "doc_count": 34
                 },
                 {
                    "key": "DST",
                    "doc_count": 40
                 },
                 {
                    "key": "Intox Alcoolica",
                    "doc_count": 145
                 },
                 {
                    "key": "Intox Alimentar",
                    "doc_count": 25
                 }
              ]
           }
        },

The second bucket, did not return the key "Dengue", because in this case there was no record in the database, but I need to return the result, even if it is 0. For example:
{
"Key": "Dengue"
"Doc_count": 0
},

How can I make it returns 0 when there are no cases?

Note: I tried using min_doc_count, like this:

  "aggs": {
    "doencas": {
      "terms": { 
        "field": "doenca","min_doc_count": 0, "size" : 5,"order" : { "_term" : "asc" }
      }
    }}

but using this way, it violates the terms of the query, and brings totally erroneous results.
thank you for attention

2

You can set the min_doc_count option on the Terms Aggregation. If you set it to zero (min_doc_count: 0), it'll return all buckets, even if they have zero documents

Here are the docs for it: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#_minimum_document_count_3

Note: this could result in very large / expensive terms aggregations and responses.

3

yes it returns all but I needed him to return only those that added in the query

{"terms": {"doenca":  ["Dengue","Intox Alcoolica","DST","Intox Alimentar","Animal Peconhento"

instead it returns all, restricted to 5 for the site, but not the field I need.
Thank you so much

4

Oops, sorry, I totally skipped over the last part of your message where you mentioned that you tried min_doc_count. :open_mouth: Sorry!

There are two ways you could tackle this, neither are the most pleasant. The root problem is that the query just finds documents that match the query, while the aggregation just summarizes details about the documents that are found. They aren't very closely coupled.

Option A

The first option is to use min_doc_count: 0 as suggested, and also filter the terms that are aggregated. So your agg would look something like this:

"aggs": {
    "doencas": {
      "terms": { 
        "field": "doenca",
        "min_doc_count": 0,
        "include": ["Dengue","Intox Alcoolica","DST","Intox Alimentar","Animal Peconhento"]
      }
    }
}

This will only include those explicit terms in the aggregation.

Option B

The second option is to construct a set of filter aggregations for each term you're interested in:

"aggs": {
  "dengue": {
    "filter": {
      "term": "Dengue"
    }
  },
  "intox": {
    "filter": {
      "term": "Intox Alcoolica"
    }
  },
  ... etc ...
}

Which will give you a bucket-per-filter that you construct, regardless of if it matches documents or not.

Option A will likely give better performance, but B may be more flexible depending on what you want to do.

1 Like
5

oh, option A, it worked perfectly.
How I will use this return to mount a chart, it must always return the same order and without missing fields, because manage the return without the fields is hell.

Thank you very very much , I hope that this answer can help others.

6

If you add back in the sort order that you had in your original post ("order" : { "_term" : "asc" }) you should be able to get a consistent order each time.

Goodluck!