Response formats

By default, the SQL plugin returns the response in the standard JDBC format. This format is provided for the JDBC driver and clients that need both the schema and the result set to be well formatted.

Sample request

The following query does not specify the response format, so the format is set to jdbc:

Sample response

In the response, the schema contains the field names and types, and the datarows field contains the result set:

  1. {
  2.   "schema": [{
  3.       "name": "firstname",
  4.       "type": "text"
  5.     },
  6.     {
  7.       "name": "lastname",
  8.       "type": "text"
  9.     },
  10.     {
  11.       "name": "age",
  12.       "type": "long"
  13.     }
  14.   ],
  15.   "total": 4,
  16.   "datarows": [
  17.     [
  18.       "Nanette",
  19.       "Bates",
  20.       28
  21.     ],
  22.     [
  23.       "Amber",
  24.       "Duke",
  25.       32
  26.     ]
  27.   ],
  28.   "size": 2,
  29.   "status": 200
  30. }

If an error of any type occurs, OpenSearch returns the error message.

The following query searches for a non-existent field unknown:

  1. POST /_plugins/_sql
  2. {
  3.   "query" : "SELECT unknown FROM accounts"
  4. }

The response contains the error message and the cause of the error:

  1.   "error": {
  2.     "reason": "Invalid SQL query",
  3.     "details": "Field [unknown] cannot be found or used here.",
  4.     "type": "SemanticAnalysisException"
  5.   },
  6.   "status": 400
  7. }

Sample request

The following query sets the response format to :

  1. POST _plugins/_sql?format=json
  2. {
  3.   "query" : "SELECT firstname, lastname, age FROM accounts ORDER BY age LIMIT 2"
  4. }

Sample response

The response is the original response from OpenSearch:

  1. {
  2.   "_shards": {
  3.     "total": 5,
  4.     "failed": 0,
  5.     "successful": 5,
  6.     "skipped": 0
  7.   },
  8.   "hits": {
  9.     "hits": [{
  10.         "_index": "accounts",
  11.         "_type": "account",
  12.         "_source": {
  13.           "firstname": "Nanette",
  14.           "age": 28,
  15.           "lastname": "Bates"
  16.         },
  17.         "_id": "13",
  18.         "sort": [
  19.           28
  20.         ],
  21.         "_score": null
  22.       },
  23.       {
  24.         "_index": "accounts",
  25.         "_type": "account",
  26.         "_source": {
  27.           "firstname": "Amber",
  28.           "age": 32,
  29.           "lastname": "Duke"
  30.         },
  31.         "_id": "1",
  32.         "sort": [
  33.           32
  34.         ],
  35.         "_score": null
  36.       }
  37.     ],
  38.     "total": {
  39.       "relation": "eq"
  40.     },
  41.     "max_score": null
  42.   },
  43.   "took": 100,
  44.   "timed_out": false
  45. }

You can also specify to return results in CSV format.

Sample request

Sample response

  1. firstname,lastname,age
  3. Amber,Duke,32
  4. Dale,Adams,33
  5. Hattie,Bond,36

By default, OpenSearch sanitizes header cells (field names) and data cells (field contents) according to the following rules:

  • If a cell starts with +, -, = , or @, the sanitizer inserts a single quote (') at the start of the cell.
  • If a cell contains one or more commas (,), the sanitizer surrounds the cell with double quotes (").

The following query indexes a document with cells that either start with special characters or contain commas:

  1. PUT /userdata/_doc/1?refresh=true
  2. {
  3.   "+firstname": "-Hattie",
  4.   "=lastname": "@Bond",
  5.   "address": "671 Bristol Street, Dente, TN"
  6. }

You can use the query below to request results in CSV format:

  1. POST /_plugins/_sql?format=csv
  2. {
  3.   "query" : "SELECT * FROM userdata"
  4. }
  1. '+firstname,'=lastname,address
  2. 'Hattie,'@Bond,"671 Bristol Street, Dente, TN"

To skip sanitizing, set the sanitize query parameter to false:

  1. POST /_plugins/_sql?format=csvandsanitize=false
  2. {
  3.   "query" : "SELECT * FROM userdata"
  4. }

The response contains the results in the original CSV format:

You can use the raw format to pipe the results to other command line tools for post-processing.

Sample request

  1. POST /_plugins/_sql?format=raw
  2. {
  3.   "query" : "SELECT firstname, lastname, age FROM accounts ORDER BY age"
  4. }

Sample response

  1. Nanette|Bates|28
  2. Amber|Duke|32
  3. Dale|Adams|33
  4. Hattie|Bond|36

By default, OpenSearch sanitizes results in raw format according to the following rule:

  • If a data cell contains one or more pipe characters (|), the sanitizer surrounds the cell with double quotes.

The following query indexes a document with pipe characters (|) in its fields:

  1. PUT /userdata/_doc/1?refresh=true
  2. {
  3.   "+firstname": "|Hattie",
  4.   "=lastname": "Bond|",
  5.   "|address": "671 Bristol Street| Dente| TN"
  6. }

You can use the query below to request results in raw format:

  1. POST /_plugins/_sql?format=raw
  2. {
  3.   "query" : "SELECT * FROM userdata"
  4. }
  1. "671 Bristol Street| Dente| TN"|"Bond|"|"|Hattie"