Filtering using Elasticsearch query DSL

edit

Filtering using Elasticsearch query DSL

edit

One can filter the results that SQL will run on using a standard Elasticsearch query DSL by specifying the query in the filter parameter.

POST /_sql?format=txt
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "filter": {
        "range": {
            "page_count": {
                "gte" : 100,
                "lte" : 200
            }
        }
    },
    "fetch_size": 5
}

Which returns:

    author     |                name                |  page_count   | release_date
---------------+------------------------------------+---------------+------------------------
Douglas Adams  |The Hitchhiker's Guide to the Galaxy|180            |1979-10-12T00:00:00.000Z

A useful and less obvious usage for standard query DSL filtering is to search documents by a specific routing key. Because Elasticsearch SQL does not support a routing parameter, one can specify a terms filter for the _routing field instead:

POST /_sql?format=txt
{
    "query": "SELECT * FROM library",
    "filter": {
        "terms": {
            "_routing": ["abc"]
        }
    }
}