Endpoint

    To send query request to SQL plugin, you can either use a request parameter in HTTP GET or request body by HTTP POST request. POST request is recommended because it doesn’t have length limitation and allows for other parameters passed to plugin for other functionality such as prepared statement. And also the explain endpoint is used very often for query translation and troubleshooting.

    You can send HTTP GET request with your query embedded in URL parameter.

    Example

    SQL query:

    You can also send HTTP POST request with your query in request body.

    Example

    2.   "query" : "SELECT * FROM accounts"
    3. }'

    To translate your query, send it to explain endpoint. The explain output is OpenSearch domain specific language (DSL) in JSON format. You can just copy and paste it to your console to run it against OpenSearch directly.

    Example

    Explain query:

    1. >> curl -H 'Content-Type: application/json' -X POST localhost:9200/_plugins/_sql/_explain -d '{
    2.   "query" : "SELECT firstname, lastname FROM accounts WHERE age > 20"
    3. }'

    Explain:

    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 fallback to a non-paginated response.

    Example

    SQL query:

    1. >> curl -H 'Content-Type: application/json' -X POST localhost:9200/_plugins/_sql -d '{
    2.   "fetch_size" : 5,
    3.   "query" : "SELECT firstname, lastname FROM accounts WHERE age > 20 ORDER BY state ASC"
    4. }'

    Result set:

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

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

    The result only has the fetch_size number of datarows and cursor. The last page has only datarows and no cursor. The datarows can have more than the number of records in case the nested fields are flattened.

    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. }
    1. >> curl -H 'Content-Type: application/json' -X POST localhost:9200/_plugins/_sql/close -d '{
    3. }'

    Sample response