SQL and PPL API

Use the SQL and PPL API to send queries to the SQL plugin. Use the _sql endpoint to send queries in SQL, and the _ppl endpoint to send queries in PPL. For both of these, you can also use the _explain endpoint to translate your query into OpenSearch domain-specific language (DSL) or to troubleshoot errors.

Query API

Sends an SQL/PPL query to the SQL plugin. You can pass the format for the response as a query parameter.

Query parameters

ParameterData TypeDescription
formatStringThe format for the response. The _sql endpoint supports jdbc, csv, raw, and json formats. The _ppl endpoint supports jdbc, csv, and raw formats. Default is jdbc.
sanitizeBooleanSpecifies whether to escape special characters in the results. See Response formats for more information. Default is true.

Request fields

FieldData TypeDescription
queryStringThe query to be executed. Required.
filterJSON objectThe filter for the results. Optional.
fetch_sizeintegerThe number of results to return in one response. Used for paginating results. Default is 1,000. Optional. Only supported for the jdbc response format.

Example request

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

Example response

The response contains the schema and the results:

  1. {
  2.   "schema": [
  3.     {
  4.       "name": "account_number",
  5.       "type": "long"
  6.     },
  7.     {
  8.       "name": "firstname",
  9.       "type": "text"
  10.     },
  11.     {
  12.       "name": "address",
  13.       "type": "text"
  14.     },
  15.     {
  16.       "name": "balance",
  17.       "type": "long"
  18.     },
  19.     {
  20.       "name": "gender",
  21.       "type": "text"
  22.     },
  23.     {
  24.       "name": "city",
  25.       "type": "text"
  26.     },
  27.     {
  28.       "name": "employer",
  29.       "type": "text"
  30.     },
  31.     {
  32.       "name": "state",
  33.       "type": "text"
  34.     },
  35.     {
  36.       "name": "age",
  37.       "type": "long"
  38.     },
  39.     {
  40.       "name": "email",
  41.       "type": "text"
  42.     },
  43.     {
  44.       "name": "lastname",
  45.       "type": "text"
  46.     }
  47.   ],
  48.   "datarows": [
  49.     [
  50.       1,
  51.       "Amber",
  52.       "880 Holmes Lane",
  53.       39225,
  54.       "M",
  55.       "Brogan",
  56.       "Pyrami",
  57.       "IL",
  58.       32,
  59.       "amberduke@pyrami.com",
  60.       "Duke"
  61.     ],
  62.     [
  63.       6,
  64.       "Hattie",
  65.       "671 Bristol Street",
  66.       5686,
  67.       "M",
  68.       "Dante",
  69.       "Netagy",
  70.       "TN",
  71.       36,
  72.       "hattiebond@netagy.com",
  73.       "Bond"
  74.     ],
  75.     [
  76.       13,
  77.       "Nanette",
  78.       "789 Madison Street",
  79.       32838,
  80.       "F",
  81.       "Nogal",
  82.       "Quility",
  83.       "VA",
  84.       28,
  85.       "nanettebates@quility.com",
  86.       "Bates"
  87.     ],
  88.     [
  89.       18,
  90.       "Dale",
  91.       "467 Hutchinson Court",
  92.       4180,
  93.       "M",
  94.       "Orick",
  95.       null,
  96.       "MD",
  97.       33,
  98.       "daleadams@boink.com",
  99.       "Adams"
  100.     ]
  101.   ],
  102.   "total": 4,
  103.   "size": 4,
  104.   "status": 200
  105. }

Response fields

FieldData TypeDescription
schemaArraySpecifies the field names and types for all fields.
data_rows2D arrayAn array of results. Each result represents one matching row (document).
totalIntegerThe total number of rows (documents) in the index.
sizeIntegerThe number of results to return in one response.
statusStringThe HTTP response status OpenSearch returns after running the query.

Explain API

The SQL plugin has an explain feature that shows how a query is executed against OpenSearch, which is useful for debugging and development. A POST request to the _plugins/_sql/_explain or _plugins/_ppl/_explain endpoint returns OpenSearch domain-specific language (DSL) in JSON format, explaining the query. You can execute the explain API operation either in command line using curl or in the Dashboards console, like in the example below.

Sample explain request for an SQL query

  1. POST _plugins/_sql/_explain
  2. {
  3.   "query": "SELECT firstname, lastname FROM accounts WHERE age > 20"
  4. }

Sample SQL query explain response

  1. {
  2.   "root": {
  3.     "name": "ProjectOperator",
  4.     "description": {
  5.       "fields": "[firstname, lastname]"
  6.     },
  7.     "children": [
  8.       {
  9.         "name": "OpenSearchIndexScan",
  10.         "description": {
  11.           "request": """OpenSearchQueryRequest(indexName=accounts, sourceBuilder={"from":0,"size":200,"timeout":"1m","query":{"range":{"age":{"from":20,"to":null,"include_lower":false,"include_upper":true,"boost":1.0}}},"_source":{"includes":["firstname","lastname"],"excludes":[]},"sort":[{"_doc":{"order":"asc"}}]}, searchDone=false)"""
  12.         },
  13.         "children": []
  14.       }
  15.     ]
  16.   }
  17. }

Sample explain request for a PPL query

  1. POST _plugins/_ppl/_explain
  2. {
  3.   "query" : "source=accounts | fields firstname, lastname"
  4. }

Sample PPL query explain response

  1. {
  2.   "root": {
  3.     "name": "ProjectOperator",
  4.     "description": {
  5.       "fields": "[firstname, lastname]"
  6.     },
  7.     "children": [
  8.       {
  9.         "name": "OpenSearchIndexScan",
  10.         "description": {
  11.           "request": """OpenSearchQueryRequest(indexName=accounts, sourceBuilder={"from":0,"size":200,"timeout":"1m","_source":{"includes":["firstname","lastname"],"excludes":[]}}, searchDone=false)"""
  12.         },
  13.         "children": []
  14.       }
  15.     ]
  16.   }
  17. }

For queries that require post-processing, the explain response includes a query plan in addition to the OpenSearch DSL. For those queries that don’t require post processing, you can see a complete DSL.

Paginating results

To get back a paginated response, use the fetch_size parameter. The value of fetch_size should be greater than 0. The default value is 1,000. A value of 0 will fall back to a non-paginated response.

The fetch_size parameter is only supported for the jdbc response format.

Example

The following request contains an SQL query and specifies to return five results at a time:

  1. POST _plugins/_sql/
  2. {
  3.   "fetch_size" : 5,
  4.   "query" : "SELECT firstname, lastname FROM accounts WHERE age > 20 ORDER BY state ASC"
  5. }

The response contains all the fields that a query without fetch_size would contain, and a cursor field that is used to retrieve subsequent pages of results:

  1. {
  2.   "schema": [
  3.     {
  4.       "name": "firstname",
  5.       "type": "text"
  6.     },
  7.     {
  8.       "name": "lastname",
  9.       "type": "text"
  10.     }
  11.   ],
  12.   "cursor": "d:eyJhIjp7fSwicyI6IkRYRjFaWEo1UVc1a1JtVjBZMmdCQUFBQUFBQUFBQU1XZWpkdFRFRkZUMlpTZEZkeFdsWnJkRlZoYnpaeVVRPT0iLCJjIjpbeyJuYW1lIjoiZmlyc3RuYW1lIiwidHlwZSI6InRleHQifSx7Im5hbWUiOiJsYXN0bmFtZSIsInR5cGUiOiJ0ZXh0In1dLCJmIjo1LCJpIjoiYWNjb3VudHMiLCJsIjo5NTF9",
  13.   "total": 956,
  14.   "datarows": [
  15.     [
  16.       "Cherry",
  17.       "Carey"
  18.     ],
  19.     [
  20.       "Lindsey",
  21.       "Hawkins"
  22.     ],
  23.     [
  24.       "Sargent",
  25.       "Powers"
  26.     ],
  27.     [
  28.       "Campos",
  29.       "Olsen"
  30.     ],
  31.     [
  32.       "Savannah",
  33.       "Kirby"
  34.     ]
  35.   ],
  36.   "size": 5,
  37.   "status": 200
  38. }

To fetch subsequent pages, use the cursor from the previous response:

  1. POST /_plugins/_sql 
  2. {
  3.    "cursor": "d:eyJhIjp7fSwicyI6IkRYRjFaWEo1UVc1a1JtVjBZMmdCQUFBQUFBQUFBQU1XZWpkdFRFRkZUMlpTZEZkeFdsWnJkRlZoYnpaeVVRPT0iLCJjIjpbeyJuYW1lIjoiZmlyc3RuYW1lIiwidHlwZSI6InRleHQifSx7Im5hbWUiOiJsYXN0bmFtZSIsInR5cGUiOiJ0ZXh0In1dLCJmIjo1LCJpIjoiYWNjb3VudHMiLCJsIjo5NTF9"
  4. }

The next response contains only the datarows of the results and a new cursor.

  1. {
  2.   "cursor": "d:eyJhIjp7fSwicyI6IkRYRjFaWEo1UVc1a1JtVjBZMmdCQUFBQUFBQUFBQU1XZWpkdFRFRkZUMlpTZEZkeFdsWnJkRlZoYnpaeVVRPT0iLCJjIjpbeyJuYW1lIjoiZmlyc3RuYW1lIiwidHlwZSI6InRleHQifSx7Im5hbWUiOiJsYXN0bmFtZSIsInR5cGUiOiJ0ZXh0In1dLCJmIjo1LCJpIjoiYWNjb3VudHMabcde12345",
  3.   "datarows": [
  4.     [
  5.       "Abbey",
  6.       "Karen"
  7.     ],
  8.     [
  9.       "Chen",
  10.       "Ken"
  11.     ],
  12.     [
  13.       "Ani",
  14.       "Jade"
  15.     ],
  16.     [
  17.       "Peng",
  18.       "Hu"
  19.     ],
  20.     [
  21.       "John",
  22.       "Doe"
  23.     ]
  24.   ]
  25. }

The datarows can have more than the fetch_size number of records in case nested fields are flattened.

The last page of results has only datarows and no cursor. The cursor context is automatically cleared on the last page.

To explicitly clear the cursor context, use the _plugins/_sql/close endpoint operation:

  1. POST /_plugins/_sql/close 
  2. {
  3.    "cursor": "d:eyJhIjp7fSwicyI6IkRYRjFaWEo1UVc1a1JtVjBZMmdCQUFBQUFBQUFBQU1XZWpkdFRFRkZUMlpTZEZkeFdsWnJkRlZoYnpaeVVRPT0iLCJjIjpbeyJuYW1lIjoiZmlyc3RuYW1lIiwidHlwZSI6InRleHQifSx7Im5hbWUiOiJsYXN0bmFtZSIsInR5cGUiOiJ0ZXh0In1dLCJmIjo1LCJpIjoiYWNjb3VudHMiLCJsIjo5NTF9"
  4. }'

The response is an acknowledgement from OpenSearch:

  1. {"succeeded":true}

Filtering results

You can use the filter parameter to add more conditions to the OpenSearch DSL directly.

The following SQL query returns the names and account balances of all customers. The results are then filtered to contain only those customers with less than $10,000 balance.

  1. POST /_plugins/_sql/ 
  2. {
  3.   "query" : "SELECT firstname, lastname, balance FROM accounts",
  4.   "filter" : {
  5.     "range" : {
  6.       "balance" : {
  7.         "lt" : 10000
  8.       }
  9.     }
  10.   }
  11. }

The response contains the matching results:

  1. {
  2.   "schema": [
  3.     {
  4.       "name": "firstname",
  5.       "type": "text"
  6.     },
  7.     {
  8.       "name": "lastname",
  9.       "type": "text"
  10.     },
  11.     {
  12.       "name": "balance",
  13.       "type": "long"
  14.     }
  15.   ],
  16.   "total": 2,
  17.   "datarows": [
  18.     [
  19.       "Hattie",
  20.       "Bond",
  21.       5686
  22.     ],
  23.     [
  24.       "Dale",
  25.       "Adams",
  26.       4180
  27.     ]
  28.   ],
  29.   "size": 2,
  30.   "status": 200
  31. }

You can use the Explain API to see how this query is executed against OpenSearch:

  1. POST /_plugins/_sql/_explain 
  2. {
  3.   "query" : "SELECT firstname, lastname, balance FROM accounts",
  4.   "filter" : {
  5.     "range" : {
  6.       "balance" : {
  7.         "lt" : 10000
  8.       }
  9.     }
  10.   }
  11. }'

The response contains the Boolean query in OpenSearch DSL that corresponds to the query above:

  1. {
  2.   "from": 0,
  3.   "size": 200,
  4.   "query": {
  5.     "bool": {
  6.       "filter": [{
  7.         "bool": {
  8.           "filter": [{
  9.             "range": {
  10.               "balance": {
  11.                 "from": null,
  12.                 "to": 10000,
  13.                 "include_lower": true,
  14.                 "include_upper": false,
  15.                 "boost": 1.0
  16.               }
  17.             }
  18.           }],
  19.           "adjust_pure_negative": true,
  20.           "boost": 1.0
  21.         }
  22.       }],
  23.       "adjust_pure_negative": true,
  24.       "boost": 1.0
  25.     }
  26.   },
  27.   "_source": {
  28.     "includes": [
  29.       "firstname",
  30.       "lastname",
  31.       "balance"
  32.     ],
  33.     "excludes": []
  34.   }
  35. }

Using parameters

You can use the parameters field to pass parameter values to a prepared SQL query.

The following explain operation uses an SQL query with an age parameter:

  1. POST /_plugins/_sql/_explain 
  2. {
  3.   "query": "SELECT * FROM accounts WHERE age = ?",
  4.   "parameters": [{
  5.     "type": "integer",
  6.     "value": 30
  7.   }]
  8. }

The response contains the Boolean query in OpenSearch DSL that corresponds to the SQL query above:

  1. {
  2.   "from": 0,
  3.   "size": 200,
  4.   "query": {
  5.     "bool": {
  6.       "filter": [{
  7.         "bool": {
  8.           "must": [{
  9.             "term": {
  10.               "age": {
  11.                 "value": 30,
  12.                 "boost": 1.0
  13.               }
  14.             }
  15.           }],
  16.           "adjust_pure_negative": true,
  17.           "boost": 1.0
  18.         }
  19.       }],
  20.       "adjust_pure_negative": true,
  21.       "boost": 1.0
  22.     }
  23.   }
  24. }