SQL Translate APIedit

The SQL Translate API accepts SQL in a JSON document and translates it into native Elasticsearch queries. For example:

POST /_xpack/sql/translate
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 10
}

Which returns:

{
    "size" : 10,
    "docvalue_fields" : [
        "page_count",
        "release_date"
    ],
    "_source": {
        "includes": [
            "author",
            "name"
        ],
        "excludes": []
    },
    "sort" : [
        {
            "page_count" : {
                "order" : "desc"
            }
        }
    ]
}

Which is the request that SQL will run to provide the results. In this case, SQL will use the scroll API. If the result contained an aggregation then SQL would use the normal search API.

The request body accepts all of the fields that the SQL REST API accepts except cursor.

Note

If you are using Security you need to add a few permissions to users so they can run translate SQL. To translate SQL a user needs read and indices:admin/get. The following example configures a role that can run SQL against the test and bort indices:

rest_minimal:
  indices:
    - names: test
      privileges: [read, "indices:admin/get"]
    - names: bort
      privileges: [read, "indices:admin/get"]