# Get all results at once in Elasticsearch SQL syntax **URL:** \ **Category:** Elasticsearch\ **Created:** [October 25, 2018, 8:09am UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935 "2018-10-25T08:09:43Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![legendu](https://avatars.discourse-cdn.com/v4/letter/l/e480ec/32.png) [@legendu](https://discuss.elastic.co/u/legendu)\ **Post date:** [October 25, 2018, 8:09am UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/1 "2018-10-25T08:09:43Z")
I have a simple SQL query in Elasticsearch which I know returns less than 100 rows of results. How can I get all these results at once (i.e., without using scroll)? I tried the `limit n` clause but it works when `n` is less than or equal to 10 but doesn't work when `n` is great than 10. ---
**Author:** ![fkelbert](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fkelbert/32/35762_2.png) [@fkelbert](https://discuss.elastic.co/u/fkelbert)\ **Post date:** [October 25, 2018, 1:02pm UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/2 "2018-10-25T13:02:35Z")
According to the [documentation](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-syntax-select.html#sql-syntax-limit), you should be able to use `LIMIT ALL`. Does this work? ---
**Author:** ![legendu](https://avatars.discourse-cdn.com/v4/letter/l/e480ec/32.png) [@legendu](https://discuss.elastic.co/u/legendu)\ **Post date:** [October 25, 2018, 11:32pm UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/3 "2018-10-25T23:32:08Z")
I tried. It didn't work. It still returned 10 rows of result. Perhaps I should file a ticket on this? ---
**Author:** ![nik9000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nik9000/32/44947_2.png) [@nik9000](https://discuss.elastic.co/u/nik9000)\ **Post date:** [October 25, 2018, 11:46pm UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/4 "2018-10-25T23:46:35Z")
If you fo it in the SQL console you'll get everything. I'd you do it in jdbc you'll get everything. If you do it over the http API you need to use the cursor that it returns. It is documented on the SQL API page. I'm on mobile so no link, sorry. Jdbc and the SQL cli all use the cursor mechanism over the http API. ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [October 26, 2018, 8:20am UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/5 "2018-10-26T08:20:59Z")
@legendu how do you run the query and, also, please share the query command (the full JSON if it's the REST API, or java code if it's JDBC). Thanks. ---
**Author:** ![legendu](https://avatars.discourse-cdn.com/v4/letter/l/e480ec/32.png) [@legendu](https://discuss.elastic.co/u/legendu)\ **Post date:** [October 28, 2018, 9:08am UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/6 "2018-10-28T09:08:38Z")
@nik9000 Thank you very much for the explanation! I used the http API. Now everything makes sense to me. But how can I use the scroll API to get all results from a SQL query using http API? The scroll API returns scroll\_ids while the SQL query returns a cursor ID. I tried to feed the cursor ID returned by a SQL query into a scroll API but it failed to work. ---
**Author:** ![legendu](https://avatars.discourse-cdn.com/v4/letter/l/e480ec/32.png) [@legendu](https://discuss.elastic.co/u/legendu)\ **Post date:** [October 28, 2018, 9:12am UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/7 "2018-10-28T09:12:10Z")
@Andrei_Stefan Please see my Python code for querying ES using `requests` below. ```auto import requests import json url = 'http://10.204.61.127:9200/_xpack/sql' headers = { 'Content-Type': 'application/json', } query = { 'query': ''' select date_start, sum(spend) as spend from some_index where campaign_id = 790 or campaign_id = 490 group by date_start ''' } response = requests.post(url, headers=headers, data=json.dumps(query)) ``` ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [October 29, 2018, 8:09am UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/8 "2018-10-29T08:09:00Z")
@legendu the cursor ID you get from the SQL API needs to be feed back in the same SQL API to get the next batch of results. Snippet from [our documentation](https://www.elastic.co/guide/en/elasticsearch/reference/6.x/sql-rest.html): > You can continue to the next page by sending back the `cursor` field. In case of text format the cursor is returned as `Cursor` http header. > > `POST /_xpack/sql?format=json { "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWYUpOYklQMHhRUEtld3RsNnFtYU1hQQ==:BAFmBGRhdGUBZgVsaWtlcwFzB21lc3NhZ2UBZgR1c2Vy9f///w8="` } ---
**Author:** ![legendu](https://avatars.discourse-cdn.com/v4/letter/l/e480ec/32.png) [@legendu](https://discuss.elastic.co/u/legendu)\ **Post date:** [October 29, 2018, 2:03pm UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/9 "2018-10-29T14:03:42Z")
@Andrei_Stefan Thank you, Andrei! I had a try but it still gave me at most 10 rows of results. The second call on the cursor returns no result at all. I'm sure that there are more than 10 rows of results in the index. Any idea what went wrong? And BTW, do you know the equivalent native syntax of the SQL query above? I'm so to ask for this but I'm very new to ES (and that's the reason I tried the SQL API). I'd like to verify that the equivalent native query returns the right answer. ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [October 30, 2018, 7:09am UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/10 "2018-10-30T07:09:24Z")
> [@legendu](#): > > select date\_start, sum(spend) as spend from some\_index where campaign\_id = 790 or campaign\_id = 490 group by date\_start Take the query above and send it to `translate` API: [SQL Translate API | Elasticsearch Guide [master] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/master/sql-translate.html). It will give the native ES query that you can use to test. ---
**Author:** ![legendu](https://avatars.discourse-cdn.com/v4/letter/l/e480ec/32.png) [@legendu](https://discuss.elastic.co/u/legendu)\ **Post date:** [November 4, 2018, 11:40am UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/11 "2018-11-04T11:40:57Z")
I tried the native query returned by the SQL translate API on the SQL query with `limit all`. However, it still returns only 10 rows of results. How can I make it return all results (less than 100 rows in total)? ```auto import requests import json url = 'http://10.204.61.127:9200/some_index/some_doc/_search' headers = { 'Content-Type': 'application/json', } query = { "size": 0, "query": { "bool": { "should": [ { "term": { "campaign_id.keyword": { "value": 790, "boost": 1.0 } } }, { "term": { "campaign_id.keyword": { "value": 490, "boost": 1.0 } } } ], "adjust_pure_negative": True, "boost": 1.0 } }, "_source": False, "stored_fields": "_none_", "aggregations": { "groupby": { "composite": { "size": 1000, "sources": [ { "2735": { "terms": { "field": "date_start", "missing_bucket": False, "order": "asc" } } } ] }, "aggregations": { "2768": { "sum": { "field": "spend" } } } } } } response = requests.post(url, headers=headers, data=json.dumps(query)).json() ``` ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [November 5, 2018, 1:56pm UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/12 "2018-11-05T13:56:14Z")
If you used that ES DSL and you got back 10 records... maybe that is what you have in the index that matches that query. Please, note that the query uses `"size": 10` which means the results will contain the aggregations results only, which is expected. Also, the `composite` aggregation there has `"size": 1000` which means the result will contain first 1000 buckets. So, if you say there are more than 10 rows you need to get back, this query (and its aggregation) should get you back all of it. To me, this means you have 10 buckets to be returned. Or maybe you mixed up number of documents with number of buckets? (you are using a `group by` in there, that's why you have `"size": 0` and a `composite` aggregation in the query) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 3, 2018, 2:04pm UTC](https://discuss.elastic.co/t/get-all-results-at-once-in-elasticsearch-sql-syntax/153935/13 "2018-12-03T14:04:44Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.