[[esql-rest]] === {esql} REST API ++++ REST API ++++ [discrete] [[esql-rest-overview]] === Overview The <> accepts an {esql} query string in the `query` parameter, runs it, and returns the results. For example: [source,console] ---- POST /_query?format=txt { "query": "FROM library | KEEP author, name, page_count, release_date | SORT page_count DESC | LIMIT 5" } ---- // TEST[setup:library] Which returns: [source,text] ---- author | name | page_count | release_date -----------------+--------------------+---------------+------------------------ Peter F. Hamilton|Pandora's Star |768 |2004-03-02T00:00:00.000Z Vernor Vinge |A Fire Upon the Deep|613 |1992-06-01T00:00:00.000Z Frank Herbert |Dune |604 |1965-06-01T00:00:00.000Z Alastair Reynolds|Revelation Space |585 |2000-03-15T00:00:00.000Z James S.A. Corey |Leviathan Wakes |561 |2011-06-02T00:00:00.000Z ---- // TESTRESPONSE[s/\|/\\|/ s/\+/\\+/] // TESTRESPONSE[non_json] [discrete] [[esql-kibana-console]] ==== Kibana Console If you are using {kibana-ref}/console-kibana.html[Kibana Console] (which is highly recommended), take advantage of the triple quotes `"""` when creating the query. This not only automatically escapes double quotes (`"`) inside the query string but also supports multi-line requests: // tag::esql-query-api[] [source,console] ---- POST /_query?format=txt { "query": """ FROM library | KEEP author, name, page_count, release_date | SORT page_count DESC | LIMIT 5 """ } ---- // TEST[setup:library] [discrete] [[esql-rest-format]] ==== Response formats {esql} can return the data in the following human readable and binary formats. You can set the format by specifying the `format` parameter in the URL or by setting the `Accept` or `Content-Type` HTTP header. NOTE: The URL parameter takes precedence over the HTTP headers. If neither is specified then the response is returned in the same format as the request. [cols="m,4m,8"] |=== s|`format` s|HTTP header s|Description 3+h| Human readable |csv |text/csv |{wikipedia}/Comma-separated_values[Comma-separated values] |json |application/json |https://www.json.org/[JSON] (JavaScript Object Notation) human-readable format |tsv |text/tab-separated-values |{wikipedia}/Tab-separated_values[Tab-separated values] |txt |text/plain |CLI-like representation |yaml |application/yaml |{wikipedia}/YAML[YAML] (YAML Ain't Markup Language) human-readable format 3+h| Binary |cbor |application/cbor |https://cbor.io/[Concise Binary Object Representation] |smile |application/smile |{wikipedia}/Smile_(data_interchange_format)[Smile] binary data format similar to CBOR |=== The `csv` format accepts a formatting URL query attribute, `delimiter`, which indicates which character should be used to separate the CSV values. It defaults to comma (`,`) and cannot take any of the following values: double quote (`"`), carriage-return (`\r`) and new-line (`\n`). The tab (`\t`) can also not be used. Use the `tsv` format instead. [discrete] [[esql-rest-filtering]] ==== Filtering using {es} Query DSL Specify a Query DSL query in the `filter` parameter to filter the set of documents that an {esql} query runs on. [source,console] ---- POST /_query?format=txt { "query": """ FROM library | KEEP author, name, page_count, release_date | SORT page_count DESC | LIMIT 5 """, "filter": { "range": { "page_count": { "gte": 100, "lte": 200 } } } } ---- // TEST[setup:library] Which returns: [source,text] -------------------------------------------------- author | name | page_count | release_date ---------------+------------------------------------+---------------+------------------------ Douglas Adams |The Hitchhiker's Guide to the Galaxy|180 |1979-10-12T00:00:00.000Z -------------------------------------------------- // TESTRESPONSE[s/\|/\\|/ s/\+/\\+/] // TESTRESPONSE[non_json] [discrete] [[esql-rest-columnar]] ==== Columnar results By default, {esql} returns results as rows. For example, `FROM` returns each individual document as one row. For the `json`, `yaml`, `cbor` and `smile` <>, {esql} can return the results in a columnar fashion where one row represents all the values of a certain column in the results. [source,console] ---- POST /_query?format=json { "query": """ FROM library | KEEP author, name, page_count, release_date | SORT page_count DESC | LIMIT 5 """, "columnar": true } ---- // TEST[setup:library] Which returns: [source,console-result] ---- { "columns": [ {"name": "author", "type": "text"}, {"name": "name", "type": "text"}, {"name": "page_count", "type": "integer"}, {"name": "release_date", "type": "date"} ], "values": [ ["Peter F. Hamilton", "Vernor Vinge", "Frank Herbert", "Alastair Reynolds", "James S.A. Corey"], ["Pandora's Star", "A Fire Upon the Deep", "Dune", "Revelation Space", "Leviathan Wakes"], [768, 613, 604, 585, 561], ["2004-03-02T00:00:00.000Z", "1992-06-01T00:00:00.000Z", "1965-06-01T00:00:00.000Z", "2000-03-15T00:00:00.000Z", "2011-06-02T00:00:00.000Z"] ] } ---- [discrete] [[esql-rest-params]] ==== Passing parameters to a query Values, for example for a condition, can be passed to a query "inline", by integrating the value in the query string itself: [source,console] ---- POST /_query { "query": """ FROM library | EVAL year = DATE_EXTRACT("year", release_date) | WHERE page_count > 300 AND author == "Frank Herbert" | STATS count = COUNT(*) by year | WHERE count > 0 | LIMIT 5 """ } ---- // TEST[setup:library] To avoid any attempts of hacking or code injection, extract the values in a separate list of parameters. Use question mark placeholders (`?`) in the query string for each of the parameters: [source,console] ---- POST /_query { "query": """ FROM library | EVAL year = DATE_EXTRACT("year", release_date) | WHERE page_count > ? AND author == ? | STATS count = COUNT(*) by year | WHERE count > ? | LIMIT 5 """, "params": [300, "Frank Herbert", 0] } ---- // TEST[setup:library]