# Doubt about having clause behaviour

**URL:** <https://discuss.elastic.co/t/doubt-about-having-clause-behaviour/65129>\
**Category:** Elasticsearch\
**Created:** [November 6, 2016, 10:03am UTC](https://discuss.elastic.co/t/doubt-about-having-clause-behaviour/65129 "2016-11-06T10:03:38Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![templair79](https://avatars.discourse-cdn.com/v4/letter/t/6f9a4e/32.png) [@templair79](https://discuss.elastic.co/u/templair79)\
**Post date:** [November 6, 2016, 10:03am UTC](https://discuss.elastic.co/t/doubt-about-having-clause-behaviour/65129/1 "2016-11-06T10:03:38Z")

</div>

Hi

I need some help to understand how having is used in aggregations.

I'm using ES 2.4.1 and I have some documents indexed like these:

{"author":"first writter", "book": "first book"},  
{"author":"first writter","book": "second book"},  
{"author":"second writter","book": "first book"}

So, my test dataset has more than 2 millions indexed documents as described above: I hope this three examples are enough to understand the structure. Note that all authors have at least 1 book, but it's not mandatory to have more than 1.

I want to retrieve two stats:

- Top 5 Authors with more than 1 book
- Bottom 5 Authors with more than 1 book

As I need to retrieve only authors with more than 1 book, my first aproach (as I come from an SQL environment) has been to use having clause in aggregation as following:

```
"aggregations" : {
	"author" : {
  		"terms" : {
    		"field" : "author",
    		"size" : 5,
    		"order" : {
      			"requests": "desc"
    		}
  		},
        "aggregations" : {
        	"requests" : {
            	"cardinality" : {
                	"field" : "book"
                }
            },
            "having" : {
            	"bucket_selector": {
                           "buckets_path": {
                               "total": "requests"
                    },
                    "script": "total > 1"
                }
            }
        }
	}
 }

```

With this approach I have succeeded in retrieving top indicator, but bottom indicator (changing order to desc) doesn't return any result.

I have changed my having clause from  
"script": "total \> 1"  
to  
"script": "total \> 0"

And then, bottom indicator returns 5 authors with only 1 book, but this is not the expected behaviour.

Am I missing something? Is there any other better approach to retrieve this information?

Thanks in advance

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [November 7, 2016, 8:41am UTC](https://discuss.elastic.co/t/doubt-about-having-clause-behaviour/65129/2 "2016-11-07T08:41:41Z")

</div>

Hey,

for the first approach, you just need a single `terms` aggregation with the `min_doc_count` field set to `1`. See the [terms aggs docs](https://www.elastic.co/guide/en/elasticsearch/reference/5.0/search-aggregations-bucket-terms-aggregation.html#_minimum_document_count_3)

I am not sure what a `bottom 5 author` is in this context, as you are referring to a `requests` field that was not mentioned before, but maybe this helps already.

--Alex

---

<div class="post-metadata">

**Author:** ![templair79](https://avatars.discourse-cdn.com/v4/letter/t/6f9a4e/32.png) [@templair79](https://discuss.elastic.co/u/templair79)\
**Post date:** [November 17, 2016, 6:00pm UTC](https://discuss.elastic.co/t/doubt-about-having-clause-behaviour/65129/3 "2016-11-17T18:00:07Z")

</div>

Hi

First of all I would like to thank you Alexander for your reply, but it doesn't help me to clarifying how to use having in elasticsearch.

As I explained, I come from an SQL environment, and I would retrieve bottom 5 Authors with more than 1 book with an SQL query like this one:

```
select count(books), author from authors group by author having count(books) > 2 order by count(books) asc limit 5

```

Is there a way to reproduce this behaviour in elasticsearch? Is there any other option to retrieve this kind of aggregation?

Thanks again and sorry for my late reply

---

<div class="post-metadata">

**Author:** ![templair79](https://avatars.discourse-cdn.com/v4/letter/t/6f9a4e/32.png) [@templair79](https://discuss.elastic.co/u/templair79)\
**Post date:** [December 1, 2016, 10:14pm UTC](https://discuss.elastic.co/t/doubt-about-having-clause-behaviour/65129/4 "2016-12-01T22:14:27Z")

</div>

Hi

Does elasticsearch provide described behaviour?

Should I take another aproach in order to retrieve this bottom aggregation?

Thanks again

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 29, 2016, 10:14pm UTC](https://discuss.elastic.co/t/doubt-about-having-clause-behaviour/65129/5 "2016-12-29T22:14:54Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.