Elasticsearch SQL retrieve more than 1000

Elastic Stack Elasticsearch
#1

Implementing SQL query in Elasticsearch, not able to extract records more than 1000 in spite of LIMIT > 1000. Tried "index.mapping.total_fields.limit": 5000 , but did not help.

POST _sql?format=txt
{
  "query": "SELECT column1 FROM table LIMIT 5000"
}
#2

Welcome!

This setting

index.mapping.total_fields.limit: 5000

Is not meant for the maximum number of documents you can retrieve but for the maximum number of fields you can have within your mapping.

See

If you want to extract more data, try this:

POST _sql?format=txt
{
  "query": "SELECT column1 FROM table",
  "fetch_size": 5000
}

More details about using cursors at: