# Elasticsearch SQL aggregation query always return 1,000 results

**URL:** <https://discuss.elastic.co/t/elasticsearch-sql-aggregation-query-always-return-1-000-results/231964>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [May 11, 2020, 9:08am UTC](https://discuss.elastic.co/t/elasticsearch-sql-aggregation-query-always-return-1-000-results/231964 "2020-05-11T09:08:34Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![EREZH31](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/erezh31/32/68051_2.png) [@EREZH31](https://discuss.elastic.co/u/EREZH31)\
**Post date:** [May 11, 2020, 9:08am UTC](https://discuss.elastic.co/t/elasticsearch-sql-aggregation-query-always-return-1-000-results/231964/1 "2020-05-11T09:08:34Z")

</div>

I'm using elasticsearch built-in SQL for aggregation query, and the maximum number of results I get is always 1,000 - even when I set LIMIT. When I use the translate API, I understand the 1,000 is because the composite aggregation size is 1,000.

There is anyway to change increase that default?

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [May 11, 2020, 4:16pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-aggregation-query-always-return-1-000-results/231964/2 "2020-05-11T16:16:54Z")

</div>

Could you provide some more info, regarding your ES version and the SQL query you're running?

---

<div class="post-metadata">

**Author:** ![EREZH31](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/erezh31/32/68051_2.png) [@EREZH31](https://discuss.elastic.co/u/EREZH31)\
**Post date:** [May 11, 2020, 6:08pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-aggregation-query-always-return-1-000-results/231964/3 "2020-05-11T18:08:11Z")

</div>

I'm using 7.5.2.  
All the aggregation query, for example:  
`SELECT productName, count(*) as cnt FROM "INDEX_NAME" GROUP BY productName LIMIT 5000`

The translation for this SQL to ES query, reveal that the composite size is 1,000 although the LIMIT is 5,000

```auto
{
  "size" : 0,
  "_source" : false,
  "stored_fields" : "_none_",
  "aggregations" : {
    "groupby" : {
      "composite" : {
        "size" : 1000,
        "sources" : [
          {
            "46601" : {
              "terms" : {
                "field" : "productName.keyword",
                "missing_bucket" : true,
                "order" : "asc"
              }
            }
          }
        ]
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [May 12, 2020, 4:13pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-aggregation-query-always-return-1-000-results/231964/4 "2020-05-12T16:13:24Z")

</div>

The size is controlled by [`fetch_size`](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-rest-fields.html) parameter:

```auto
POST localhost:9200/_sql/translate
{
    "query" : "SELECT productName, count(*) as cnt FROM \"INDEX_NAME\" GROUP BY productName LIMIT 5000",
    "fetch_size": 50
}

```

and doesn't have to do with the LIMIT set.  
With limit 5000 and fetch\_size 50 you will still get 5000 rows (if exist) but in 100 pages of 50 rows each. You can find more information about pagination [here](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-pagination.html).

---

<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:** [June 9, 2020, 4:13pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-aggregation-query-always-return-1-000-results/231964/5 "2020-06-09T16:13:32Z")

</div>

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