Elasticsearch SQL aggregation query always return 1,000 results

Elastic Stack Elasticsearch
1

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?

2

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

3

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

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

The size is controlled by fetch_size parameter:

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.

5

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